Lookup, find, match and list text from multiple cells

Is there always 2 corresponding entries per letter or is the number variable?

Originally Posted by JSTR250

The number of corresponding letters is variable.

Ok, the best option is to use a VBA function. Are you open to that?

Originally Posted by JSTR250

Yes but I am a novice.

That's OK. It's pretty easy once you see how it works.

With your file open...

Press the key combination of Alt+F11 to open the Visual Basic Editor

Goto the menu Insert > Module

Copy the VBA code at the below link and paste it into the window that opens on the right side of the screen

https://www.excelforum.com/showthread.php?p=3096647

Press the key combination of ALT+Q to close the Visual Basic Editor and return to Excel.

Then...

Data Range

	A	B	C	D	E
1	------	------	------	------	------
2	MK1-	A		A	MK1-MK2-MK7-
3	MK2-	A		B	MK3-MK5-
4	MK3-	B		C	MK4-MK6-MK9-
5	MK4-	C		D	MK8-
6	MK5-	B
7	MK6-	C
8	MK7-	A
9	MK8-	D
10	MK9-	C

Enter this array formula** in E2:

=concatall(IF($B$2:$B$10=$D2,$A$2:$A$10,""))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down as needed.

You can use advanced filter to create the list of unique letters in column D:

http://contextures.com/xladvfilter01.html#FilterUR

Please find the attached sheet to see if this works as per your requirement.

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.