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

Board Regular
Joined
Mar 12, 2014
Messages
175
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
=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.!
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Last edited:
Upvote 0
=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),""))
 
Upvote 0
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
 
Upvote 0
Thanks for your Invaluable Pointer & Responses !! (Thanks Rick Rothstein) ... You got me thinking it about it logically with Focus.
~!OM~!
Bless you all!!
 
Upvote 0
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]
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top