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

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.

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?

See attached.

See attached.

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