is there a way to automate a cell to count the number of cells with content and group them all separated by a | symbol?
for example, in the attachment i want the formula in cell AA. i already put the end result i want to see in Cell. i have done this by manually concatenateing all the cells manually but sometimes i get a result like 1|2|3|4|5|6|7||||||||||||||||||||||||||||. my meager result has all the extra |.
this is one (ugly) way.
It assumes you don't have values going past column P.PHP Code:=IF(AND($B2<>"",$C2=""),B2,IF(AND($B2<>"",$C2<>"",$D2=""),B2&"|"&C2,IF(AND($B2<>"",$C2<>"",$D2<>"",E2=""),B2&"|"&C2&"|"&D2,IF(AND($B2<>"",$C2<>"",$D2<>"",E2<>"",F2=""),B2&"|"&C2&"|"&D2&"|"&E2,IF(AND($B2<>"",$C2<>"",$D2<>"",E2<>"",F2<>"",G2=""),B2&"|"&C2&"|"&D2&"|"&E2&"|"&F2,IF(AND($B2<>"",$C2<>"",$D2<>"",E2<>"",F2<>"",G2<>"",H2=""),B2&"|"&C2&"|"&D2&"|"&E2&"|"&F2&"|"&G2,IF(AND($B2<>"",$C2<>"",$D2<>"",E2<>"",F2<>"",G2<>"",H2<>"",I2=""),B2&"|"&C2&"|"&D2&"|"&E2&"|"&F2&"|"&G2&"|"&H2,IF(AND($B2<>"",$C2<>"",$D2<>"",E2<>"",F2<>"",G2<>"",H2<>"",I2<>"",J2=""),B2&"|"&C2&"|"&D2&"|"&E2&"|"&F2&"|"&G2&"|"&H2&"|"&I2,IF(AND($B2<>"",$C2<>"",$D2<>"",E2<>"",F2<>"",G2<>"",H2<>"",I2<>"",J2<>"",K2=""),B2&"|"&C2&"|"&D2&"|"&E2&"|"&F2&"|"&G2&"|"&H2&"|"&I2&"|"&J2,IF(AND($B2<>"",$C2<>"",$D2<>"",E2<>"",F2<>"",G2<>"",H2<>"",I2<>"",J2<>"",K2<>"",L2=""),B2&"|"&C2&"|"&D2&"|"&E2&"|"&F2&"|"&G2&"|"&H2&"|"&I2&"|"&J2&"|"&K2,IF(AND($B2<>"",$C2<>"",$D2<>"",E2<>"",F2<>"",G2<>"",H2<>"",I2<>"",J2<>"",K2<>"",L2<>"",M2=""),B2&"|"&C2&"|"&D2&"|"&E2&"|"&F2&"|"&G2&"|"&H2&"|"&I2&"|"&J2&"|"&K2&"|"&L2,IF(AND($B2<>"",$C2<>"",$D2<>"",E2<>"",F2<>"",G2<>"",H2<>"",I2<>"",J2<>"",K2<>"",L2<>"",M2<>"",N2=""),B2&"|"&C2&"|"&D2&"|"&E2&"|"&F2&"|"&G2&"|"&H2&"|"&I2&"|"&J2&"|"&K2&"|"&L2&"|"&M2,IF(AND($B2<>"",$C2<>"",$D2<>"",E2<>"",F2<>"",G2<>"",H2<>"",I2<>"",J2<>"",K2<>"",L2<>"",M2<>"",N2<>"",O2=""),B2&"|"&C2&"|"&D2&"|"&E2&"|"&F2&"|"&G2&"|"&H2&"|"&I2&"|"&J2&"|"&K2&"|"&L2&"|"&M2&"|"&N2,IF(AND($B2<>"",$C2<>"",$D2<>"",E2<>"",F2<>"",G2<>"",H2<>"",I2<>"",J2<>"",K2<>"",L2<>"",M2<>"",N2<>"",O2<>"",P2=""),B2&"|"&C2&"|"&D2&"|"&E2&"|"&F2&"|"&G2&"|"&H2&"|"&I2&"|"&J2&"|"&K2&"|"&L2&"|"&M2&"|"&N2&"|"&O2,IF(AND($B2<>"",$C2<>"",$D2<>"",E2<>"",F2<>"",G2<>"",H2<>"",I2<>"",J2<>"",K2<>"",L2<>"",M2<>"",N2<>"",O2<>"",P2<>""),B2&"|"&C2&"|"&D2&"|"&E2&"|"&F2&"|"&G2&"|"&H2&"|"&I2&"|"&J2&"|"&K2&"|"&L2&"|"&M2&"|"&N2&"|"&O2&"|"&P2,"")))))))))))))))
(trying to paste it differently so it shows better.)
HTML Code:
Last edited by Sam Capricci; 07-05-2019 at 04:19 PM.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
I suggest you use helper columns then take the last result. Using your sample file, do this:
1. In AB2, put this:
Formula:
Please Login or Register to view this content.
2. In AC2, put this:
Formula:Please Login or Register to view this content.
3. Drag that formula along until you get to AZ2. There, it will look like this:
Formula:Please Login or Register to view this content.
4. Go back to column AA and enter this in AA2:
Formula:Please Login or Register to view this content.
5. Drag the formula in AA2:AZ2 down as many rows as you need (to row 4 in your sample file).
What this does is look at each cell in turn (of your original data) and, if there is something in the cell, concatenate that with a | separator before it. The second IF in the formula is there in case your data starts with some blank cells. If the first cell (B2) will never be blank, then you can replace the second IF statement with just " | ", giving you this for in AC2:
Formula:Please Login or Register to view this content.
You can then hide the helper columns. I've attached your file with this working, but the columns not yet hidden.
Hope that helps.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Try this if you have TEXTJOIN function
Enter as array formula in cell AA2 and copy down (must be entered with ctrl+shift+enter key combination.
Formula:Please Login or Register to view this content.
Last edited by AlKey; 07-05-2019 at 04:30 PM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Without textjoin,
=SUBSTITUTE(TRIM(B2&" "&C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2&" "&K2&" "&L2&" "&M2&" "&N2&" "&O2&" "&P2&" ")," ","|")
OrOriginally Posted by AlKey
Try this if you have TEXTJOIN function
Enter as array formula in cell AA2 and copy down (must be entered with ctrl+shift+enter key combination.
Formula:Please Login or Register to view this content.without array confirmation.Formula:Please Login or Register to view this content.
Without TEXTJOIN, a simple UDF will be easier to work with than a long formula that refers to each cell individually. Note that this, as with Bo_Ry's suggestion above will fail if there are spaces in the original data.Then use the formula to call the UDF
Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Another UDF possibility, from this thread: https://www.excelforum.com/excel-gen...ank-cells.html
Please Login or Register to view this content.
thank you all for the help. I am going to try all of these as they are all quite different!!! would like to learn how they all work.
Cheers!
That's one of the great things about Excel - there are often a few different ways of achieving what you want.
If you need more information about one or more of the above, just let us know.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks