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]

Why is my post not viewable?

 

[Joe4]

Why is my post not viewable?

Not sure what you mean...
If it is not viewable, how were you able to post to it?

If you mean that you are trying to post some sort of image that isn't coming through, note that there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html. Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[Rick Rothstein]

=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

This is untested, but what if you change your formula to this, does it work then?

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

 

[Spyros13]

OMG !!!!! IT WORKS !!!!!!!! OMG!!!!! Hallilluyyaa !!! & I ACTUALLY DID THIS !! MYSELF!!!#

ENTERED AS ARRAY FORMULA !!!

={StringConcat("|",IF(Sheet2!$E$7:$E$51=V69,IF(ISERROR((Sheet2!$E$7:$E$51=V69)/(Sheet2!$E$7:$E$51=V69)*(ROW(Sheet2!$E$7:$E$51)-ROW(Sheet2!E$6))),"",(Sheet2!$E$7:$E$51=V69)/(Sheet2!$E$7:$E$51=V69)*(ROW(Sheet2!$E$7:$E$51)-ROW(Sheet2!E$6))),""),"")}

how I feel !! ___ --> https://www.youtube.com/watch?v=IPKAwJKGSDc

 

[Spyros13]

Thanks for your Invaluable Pointer & Responses !! (Thanks Rick Rothstein) ... You got me thinking it about it logically with Focus.
~!OM~!
Bless you all!!

 

[Spyros13]

Im actually looking for the clasped hands prayer symbol . Thank You Chip.

��

Actually cant say enough. Properly or sincerely on a forum. But omg thank you.

[FONT=&quot]om �� again

--- postcript to anyone needing this [solution found above][/FONT]

 

[Spyros13]

Forgive me , context required. Im near Marian siwek & my mum all the time. She told me (her voice told me) it works (as I was about to type it). (*before i did it). Om. Thank you { that was playing in background of the store I did this from }. Om. Okkay, next step is to see if & how I can adapt/expand this for a table (n by m table). OK . see you all later, its been exausting (& ive pushed myself). But cant believe & so extactic & happy Over the moon i did this ! good night.

 

[Spyros13]

I REALLY MEAN IT. PRAYING HANDS, CLASPED HANDS AND HEARTS ALL AROUND. THIS IS REALLY EMOTIONAL . THANK YOU CHIP, MR EXCEL, MUM, DAD, AGATA, ELY, EVERONE. FLYING BIRDS AND HEARTS . TEARS. THANK YOU

& TO HELL WITH ANY EMBARSEMENT!!! THANK YOU.

FORMULA CSE ENTERED:

={StringConcat("|",IF(Sheet2!$E$7:$E$51=V69,IF(ISERROR((Sheet2!$E$7:$E$51=V69)/(Sheet2!$E$7:$E$51=V69)*(ROW(Sheet2!$E$7:$E$51)-ROW(Sheet2!E$6))),"",(Sheet2!$E$7:$E$51=V69)/(Sheet2!$E$7:$E$51=V69)*(ROW(Sheet2!$E$7:$E$51)-ROW(Sheet2!E$6))),""),"")}

RESULT:

[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]11|33
[/TD]
[/TR]
</tbody>[/TABLE]

EXACTLY WHAT I WANT.

 

[Rick Rothstein]

I REALLY MEAN IT. PRAYING HANDS, CLASPED HANDS AND HEARTS ALL AROUND. THIS IS REALLY EMOTIONAL . THANK YOU CHIP, MR EXCEL, MUM, DAD, AGATA, ELY, EVERONE. FLYING BIRDS AND HEARTS . TEARS. THANK YOU

& TO HELL WITH ANY EMBARSEMENT!!! THANK YOU.

FORMULA CSE ENTERED:

={StringConcat("|",IF(Sheet2!$E$7:$E$51=V69,IF(ISERROR((Sheet2!$E$7:$E$51=V69)/(Sheet2!$E$7:$E$51=V69)*(ROW(Sheet2!$E$7:$E$51)-ROW(Sheet2!E$6))),"",(Sheet2!$E$7:$E$51=V69)/(Sheet2!$E$7:$E$51=V69)*(ROW(Sheet2!$E$7:$E$51)-ROW(Sheet2!E$6))),""),"")}

RESULT:

[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]11|33
[/TD]
[/TR]
</tbody>[/TABLE]

EXACTLY WHAT I WANT.

Alright, I downloaded Chips UDF and tested the idea I posted in Message #4 ... and it works if you array-enter** the formula.

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

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

The above formula adds the "Row#" text to the front of the row number as you seemed to indicate you wanted in your original post. If, however, you do not want that text added to each row number (which you seemed to indicate in Message #9 ), then use this version of my formula...

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

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.