Matching of Lists

[y0shi]

Dear All,

Does anyone know how to do the following in Excel?

1. I have two lists: List 1 contains unique (Elephant) and repetitive (Bird, Cat, Dog) items. List 2 contains only unique items and it is a subset of List 1.

2. I wish to match List 2 to List 1, so that in the output I would have to two lists side by side, with the items in List 2 repeated the same number of times as the corresponding items in List 1. As for the items in List 1 but not in List 2, the corresponding cells in List 2 can be empty or filled with any identifier.

Please refer to the attached image.

The format in the output may vary according to the functionality of Excel. In the end, I am only interested to know that Bird in List 2 is found in Rows 2 and 3 in List 1, and Dog in rows 7, 8, and 9.

Matching.jpg

Thanks in advance!

[TMS]

Cell B2:

Please Login or Register  to view this content.

and copy down.

Regards, TMS

[adhawan06]

You can try following formula in F2 assuming you wanted to have result in column F

[TMS]

Or B2:

Please Login or Register  to view this content.

and copy down.

[adhawan06]

@Yoshi - Now you have 3 ways to get the same thing. This is what special here.

[y0shi]

Hi All,

Thanks for the prompt reply and suggestions! But I still can't get it to work. I tried all formulae suggested and got a message saying there's an error in my formula. Do you guys know what's wrong? Sorry, I am not good in Excel. Simple things may not be that obvious to me.

error.jpg

[adhawan06]

Hi Y0shi,

The formula is seems to be fine and should work don't know whats the problem. Till the time, you can try below one.

Or B2:

Please Login or Register  to view this content.

and copy down.

Or

You can try following formula in F2 assuming you wanted to have result in column F

[TMS]

Try changing the commas (,) to semi-colons ( ; ).

Probably Regional Settings issue.


Regards, TMS

[adhawan06]

Btw Y0shi which version of excel you are using currently. Are you on Mac? or window? If you are on Mac try replacing (,) with ( ; ).

[TMS]

@Any: I don't think it's because he's on a Mac, I think it's because he's in Switzerland.

[adhawan06]

@Any: I don't think it's because he' son a Mac, I think it's because he's in Switzerland.

@TMC but if he's in Switzerland then how it matter not getting the result

[y0shi]

You guys are awesome!

Semicolon worked beautifully!! So it's really a regional setting issue. Thanks peeps!!

[adhawan06]

y0shi,

Glad it worked.



Please take few seconds to mark this thread as SOLVED and say thanks to the person who helped you by adding *Add Reputation.


Cheers!!!


Anil

[TMS]

You're welcome.

Some countries use a comma as a separator, others use a semi-colon. This is usually set up when you install Windows and tell it where you are (if it hasn't worked it out for itself). I guessed from the location in your profile that this might be the case.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[TMS]

@Anil:

then how it matter not getting the result

I don't understand your question. Maybe I've answered it in my last post?