Thank you Chp Pearson - You were the Best. =StringConcat("|",IF(Sheet2!$E$7:$E$51=V41,Sheet2!$E$7:$E$51,"")) works. but how to get it to return & Joi

[Spyros13]

=StringConcat("|",IF(Sheet2!$E$7:$E$51=V41,Sheet2!$E$7:$E$51,""))

Chip Pearson was the best. Thats his UDF - which I found (here: http://www.cpearson.com/excel/stringconcatenation.aspx/) - that he made to handle Concatenation properly, even the concatenation of arrays (Holy Grail!!!)

My Only problem is it gives me the values, not the row numbers found in. So instead of row#1|row#2 , in that cell it returns Moore|Moore

which is useless for me.

Even if I replace it with or try =StringConcat("|",IF(Sheet2!$E$7:$E$51=V41,ROWS(Sheet2!$E$7:$E$51),"")) or ROWS. I just get an empty cell returned.

Any help on how I can adapt the 2nd argument to get it to validly reference corresponding rows instead of the values?

This is all to do with my side work from my job in the last 2 days of trying to learn VBA/Excel Better. Starting from Array Parsing, Parsing Formula into UDF's, to Join Function, and all such things,which I still havent learned, but Chip's formula does the job 99% .... so I'm trying to use that (get the job done) and learn again when its done. Perhaps weekend when I can.

Or with the way things are going, maybe again tonight.!

 

[Spyros13]

Your right. It does work. Its much cleaner, so Ill use your formula going forward.

Either I didnt try it as CSE when I did try, or I was all over the place mentally. Thank you.