Need help with a formula to find a given string in a column

[kelly mort]

Hello,
I have two sheets of data with the almost the same list of items. Just that one contains the few more list than the other, however the full list lacks some data fields I need to get added. I could sort if they were all full lists.

Is there a way to get both lists compared and see if part of names of items match on both sheet?

Formula or vba is cool. Thanks

 

[JackDanIce]

Stack the two lists on top of each other
Remove duplicates
Use VLOOKUP on two individual lists against unique list to see what matches are found in what list.

E.g. List 1: A, B, C, D
List 2: B, C, D, E, F
Unique List, A, B, C, D, E, F

Vlookup(List1, Unique List): A, B, C, D, (#no match E), (#no match F)
Vlookup(List2, Unique List): (#no match A), B, C, D, E, F

 

[kelly mort]

Stack the two lists on top of each other
Remove duplicates
Use VLOOKUP on two individual lists against unique list to see what matches are found in what list.

E.g. List 1: A, B, C, D
List 2: B, C, D, E, F
Unique List, A, B, C, D, E, F

Vlookup(List1, Unique List): A, B, C, D, (#no match E), (#no match F)
Vlookup(List2, Unique List): (#no match A), B, C, D, E, F

Okay thanks. But I don't seem to understand your analysis. Can you explain further. Maybe actual formula will do.

 

[JackDanIce]

Copy list 1 to a new empty column
Copy list 2 underneath above
This creates a single list made up of list1 and list2
Select this single list
Use Excel's Remove Duplicate's feature to remove duplicates in this single list.
Use VLOOKUP against the original lists to match to this new list.

 

[kelly mort]

Oh okay I get it now. But I am not sure there are even duplicates.

For example in list 1, "Apple Juice" in list 2, "Apple Jui" something like that. So virtually the list are different but I was thinking if I could compare few characters from left of each list since those seem to match.

 

[jtakw]

Hi,

Can you post a small sample of both lists (may be about 10 to 20 items) to clarify your requirements?