+ Reply to Thread
Results 1 to 7 of 7

Creating List of matched data

  1. #1
    Registered User
    Join Date
    02-28-2011
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Creating List of matched data

    Essentially, I have two lists of Hospital systems that I need to consolidate into one comprehensive list. so far I have created a sheet using an =IF(ISNA(MATCH... sequence that gave me a match or no match read out. From here I'm looking to find the discrepancies between the two (i made a cell that reads out 204). Unfortunately, it's not simply a matter of using the longer list because the smaller list may have systems that are not included in the larger. I was thinking if i could have excel sort of separate the matched and extraneous systems into two columns I could work from there? help? attached is what I have so far. All help would be greatly appreciated! thank you
    Attached Files Attached Files
    Last edited by Vik1ng; 02-28-2011 at 05:30 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Creating List of matched data

    You can add another helper column to check the reverse, try in D2:

    Please Login or Register  to view this content.
    copied down.

    Then in F2:

    Please Login or Register  to view this content.
    copied down.

    This gets all the unique consolidated items.

    You can use Data|Filter|Autofilter to filter for Non-Blanks and copy/paste the results elsewhere to get the list with no blanks.

    or to do it with a formula, change formula in E2 to:
    Please Login or Register  to view this content.
    then in a new column:

    Please Login or Register  to view this content.
    confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    02-28-2011
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Creating List of matched data

    EDIT:

    figured it out but am unable to do what you did with the =IF(ROWS($A$2:$A2)>$E$3,"",INDEX($F$3:$F$597,SMALL(IF($F$3:$I$597<>"",ROW($F$3:$F$597)-ROW($F$3)+1),ROWS($A$2:$A2))))

    line when referring to the data in the I column (tried replacing all F's with I's- not including the ones in the IF's of course-, didnt work.) any ideas?
    Last edited by Vik1ng; 02-28-2011 at 04:41 PM. Reason: sorta figured it out,still working on it though

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Creating List of matched data

    See attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-28-2011
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Creating List of matched data

    right, I have all of that, I'm stuck here:
    Attached Files Attached Files

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Creating List of matched data

    See attached.

    I added column G and a new countif in E4, the result in column I
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-28-2011
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Creating List of matched data

    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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