Formula to find and match multiple values

[SmallJack]

Hi Gurus,

Would need your kind expertise to help on this request.

Assuming I’ve the following sample data as per attached and wishing if the expected output can be generated using some kind of excel/vba formulas.

The requirement is to find and match the specific cell which contains multiple values and return them in horizontal layout.

The sample data could be long and a lot, so it's best if the formula can be made flexible to cater for this situation.

Appreciate for any of your help and advice.

Thank you a lot in advance.

[Pete_UK]

Insert a new column C, then you can use these formulae in the cells stated:

C2: =IF(COUNTIF(A$2:A2,A2)=1,MAX(C$1:C1)+1,"-")

D2: =IFERROR(VLOOKUP(A2,A:C,3,0)&"_"&COUNTIF(A$2:A2,A2),"")

Copy these down as far as you need.

Then with the headers for your secondary table in E1, F1, G1 etc., You can use this formula in E2 to get the unique list of names:

=IFERROR(INDEX($A:$A,MATCH(ROWS($1:1),$C:$C,0)),"")

Copy down as far as you need to (until you start to get blanks), then put this formula in F2:

=IFERROR(INDEX($B:$B,MATCH(ROWS($1:1)&"_"&COLUMNS($F:F),$D:$D,0)),"")

This can be copied across and down as required.

Hope this helps.

Pete

[XLent]

another alternative, using your sample file:

Please Login or Register  to view this content.

then

Please Login or Register  to view this content.

[SmallJack]

Hi Gurus,

Thanks so much for your help.

It worked as expected.

[Pete_UK]

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[SmallJack]

Hi Gurus,

Have some additional requests.

Will it be possible to have the script/formula to check and merge any of multiple entries and produce the following output, see attached file?

Thanks again.

[SmallJack]

Hi Pete,

Noted, I'll close it once it's finalized.

FYI, I've already done so to thanks those Gurus who have helped me

Thanks.

[XLent]

Probably warranted a new thread (for future ref.) but using your sample file:

Please Login or Register  to view this content.

pending size of your real life datasets the above isn't going to be particularly efficient, however.

[SmallJack]

Hi XLen,

Great, thanks for your help.

Also, noted on your feedback, will create a new thread next time.

Have a nice day.