LookUp Value and Concatenate All Found Results

[Rick Rothstein]

1+2. I will set up the instructions for other users so that the data will always be in column B/AU.
3. The macro should simply look for the last row with data in the lookup column.
4. I will set up the instructions so the output will always be column D.

Okay, I started to set things up and I need clarification on one point please. In your earlier message, you said this...

[I'm sure advanced users would do this anyway, but I just wanted to add that I had to lock the ranges when applying the lookup all down the column, or it would not look for the previously searched on values, which in my case I need it to do. This means my formula comes out =LookUpConcat(B3,$B$3:$B$6941,$AU$3:$AU$6941) rather than =LookUpConcat(B3,B3:B6941,AU3:AU6941) which is where I was originally going wrong.

I am gathering from your last message that you put the the above formula in D3 and then copied it down to Row 6941 (the last row of data), is that correct? If so, that means column D will have multiple copies of the same concatenated text; that is, everytime B3 repeats itself in Column B (and there should be repeats or why else would you be using my LookUpConcat function), the concatenated list will be displayed "next to it" in Column D. Did you really want all those repeats? Or would putting the concatenated list next to (in Column D) the first occurrence of the lookup word be enough (leaving Column D's row blank for when the word is repeated later on)? Seems like the latter would be less cluttered and would allow you to find each word's concatentation easier.