+ Reply to Thread
Results 1 to 4 of 4

Looking up data from mixed format list

  1. #1
    Forum Contributor
    Join Date
    02-21-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    122

    Looking up data from mixed format list

    I am trying to do a formula to look up the zip code on the "Tanika All" sheet against the list of zip codes in each the "Tanika Medical" & "Tanika Dental" worksheet. At first I wanted it to return the text "Medical" if it was found in the medical sheet or "dental" if it was found on the dental sheet. Or "Both" if it was found on both sheets.

    There is mixed formatting and it's a huge list of data. So I have tried vlookups & index match, if functions, sumproduct functions trying to get even just two lists that lookup if the zip code matches to the other two spreadsheets. I used the formula below and made it an array formula and was able to get it for the most part to match everything but the ones that start with 0's.

    Someone please help me.

    =IFERROR(VLOOKUP(""&F2&"",TEXT('[Tanika Medical.xlsx]Sheet1'!$A:$A,"0"),1,FALSE),"")


    *****I had to remove half of the lines on the medical file to get it to upload to this post. There is usually almost 47,000 lines*****
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,261

    Re: Looking up data from mixed format list

    Is there any reason why you have the Dental and Medical tables as separate files, rather than separate sheets in one main workbook?

    It would help if you had the same format for both these files - in Dental you have fields for State, City and Zip, and in Medical you have Zip, State, County and City. In your All file you have City State and Zip, so it would be better if they all followed the same layout.

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    02-21-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: Looking up data from mixed format list

    Quote Originally Posted by Pete_UK View Post
    Is there any reason why you have the Dental and Medical tables as separate files, rather than separate sheets in one main workbook?

    It would help if you had the same format for both these files - in Dental you have fields for State, City and Zip, and in Medical you have Zip, State, County and City. In your All file you have City State and Zip, so it would be better if they all followed the same layout.

    Hope this helps.

    Pete
    These reports are generated by the system and this is exactly how they come out. This is the raw data. The medical and dental sheets are not in the same workbook because once the lines on the All workbook that have the zip codes that show up on either medical or dental or both the medical and dental workbooks are no longer needed. They are simply for reference and the all workbook is used in reporting. I have tried moving around the columns manually and it didn't make a difference I still can't get it to work properly.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,261

    Re: Looking up data from mixed format list

    Okay then, open all 3 files in the same instance of Excel, and then in D2 of the Tanika All file you can have this formula:

    =IF(ISNA(MATCH(C2,'[Tanika Medical.xlsx]Sheet1'!$A:$A,0)),"","Yes")

    Copy this down to the bottom of your data by double-clicking the fill handle on D2 (the small black square in the bottom right corner of the cursor). Then in E2 you can have this formula:

    =IF(ISNA(MATCH(C2*1,'[Tanika Dental.xlsx]Sheet1'!$C:$C,0)),"","Yes")

    and copy this down in the same way.

    Then you can close the Medical and Dental files, and Excel will automatically expand the file references to include the full path to those files, and in future you will not need to open the files.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sum of each item in a mixed list
    By musik7 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-18-2015, 07:10 PM
  2. Replies: 17
    Last Post: 03-12-2015, 06:55 PM
  3. [SOLVED] mixed date formats in s/s, how to format
    By cal_chica in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-16-2014, 06:22 PM
  4. [SOLVED] take a cell value of nixed format and multiply another cell of mixed format
    By AllenF in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-09-2014, 01:36 AM
  5. Mixed dates that need to all be the same format
    By aigo in forum Excel General
    Replies: 2
    Last Post: 12-06-2013, 06:48 AM
  6. [SOLVED] macro to trim only text in mixed format spreadsheet
    By burnettec in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-23-2012, 08:46 PM
  7. Replies: 6
    Last Post: 06-08-2006, 08:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1