how do i merge data from several cells to one, adding a comma and ignoring empty cells?

[kevinu]

I'm trying to put all the data from several cells into one cell, adding a comma between values and ignoring empty cells. The cells being copied have a formula that recalls a value or " " if the data isnt used so its not exactly empty. Attached is an example workbook.

All the data being copied is from AY1 to EH1.

[mrshl9898]

=IF(LEFT(SUBSTITUTE(AY1&", "&AZ1&", "&BA1&", "&BB1&", "&BC1&", "&BD1&", "&BE1&", "&BF1&", "&BG1&", "&BH1&", "&BI1&", "&BJ1&", "&BK1&", "&BL1&", "&BM1&", "&BN1&", "&BO1&", "&BP1&", "&BQ1&", "&BR1&", "&BS1&", "&BT1&", "&BU1&", "&BV1&", "&BW1&", "&BX1&", "&BY1&", "&BZ1&", "&CA1&", "&CB1&", "&CC1&", "&CD1&", "&CE1&", "&CF1&", "&CG1&", "&CH1&", "&CI1&", "&CJ1&", "&CK1&", "&CL1&", "&CM1&", "&CN1&", "&CO1&", "&CP1&", "&CQ1&", "&CR1&", "&CS1&", "&CT1&", "&CU1&", "&CV1&", "&CW1&", "&CX1," ,",""),2)=", ",MID(SUBSTITUTE(AY1&", "&AZ1&", "&BA1&", "&BB1&", "&BC1&", "&BD1&", "&BE1&", "&BF1&", "&BG1&", "&BH1&", "&BI1&", "&BJ1&", "&BK1&", "&BL1&", "&BM1&", "&BN1&", "&BO1&", "&BP1&", "&BQ1&", "&BR1&", "&BS1&", "&BT1&", "&BU1&", "&BV1&", "&BW1&", "&BX1&", "&BY1&", "&BZ1&", "&CA1&", "&CB1&", "&CC1&", "&CD1&", "&CE1&", "&CF1&", "&CG1&", "&CH1&", "&CI1&", "&CJ1&", "&CK1&", "&CL1&", "&CM1&", "&CN1&", "&CO1&", "&CP1&", "&CQ1&", "&CR1&", "&CS1&", "&CT1&", "&CU1&", "&CV1&", "&CW1&", "&CX1," ,",""),3,100),SUBSTITUTE(AY1&", "&AZ1&", "&BA1&", "&BB1&", "&BC1&", "&BD1&", "&BE1&", "&BF1&", "&BG1&", "&BH1&", "&BI1&", "&BJ1&", "&BK1&", "&BL1&", "&BM1&", "&BN1&", "&BO1&", "&BP1&", "&BQ1&", "&BR1&", "&BS1&", "&BT1&", "&BU1&", "&BV1&", "&BW1&", "&BX1&", "&BY1&", "&BZ1&", "&CA1&", "&CB1&", "&CC1&", "&CD1&", "&CE1&", "&CF1&", "&CG1&", "&CH1&", "&CI1&", "&CJ1&", "&CK1&", "&CL1&", "&CM1&", "&CN1&", "&CO1&", "&CP1&", "&CQ1&", "&CR1&", "&CS1&", "&CT1&", "&CU1&", "&CV1&", "&CW1&", "&CX1," ,",""))

If you replace your " " return values with "".

Probably similiar length to that IF statement

[kevinu]

I found a LONG solution.. hope someone can slim this down

=AZ1&IF(BA1=" ","",BA1&", ")&IF(BB1=" ","",BB1&", ")&IF(BC1=" ","",BC1&", ")&IF(BD1=" ","",BD1&", ")&IF(BE1=" ","",BE1&", ")&IF(BF1=" ","",BF1&", ")&IF(BG1=" ","",BG1&", ")&IF(BH1=" ","",BH1&", ")

[FlameRetired]

Withdrawn by FR.

[FlameRetired]

Are you wanting to return the string

4L, 4R, 5L, 5R, 6L, 6R, 7L, 7R, 13L, 18L, 18R, 19L, 19R, 20L, 21L, 22L, 23L, 23R, 24L, 24R, 27L, 28L, 29L, 29R, 30L, 30R, 31L, 31R

from G1 or instead of G1 from something else?

[kevinu]

instead of G1. G1 was the best I could do but I've figured out how to brute force it but I'd love to see a more elegant solution.

Currently I have:

="BA Matches: "&IF(BA1=" ","",BA1&", ")&IF(BB1=" ","",BB1&", ")&IF(BC1=" ","",BC1&", ")&IF(BD1=" ","",BD1&", ")&IF(BE1=" ","",BE1&", ")&IF(BF1=" ","",BF1&", ")&IF(BG1=" ","",BG1&", ")&IF(BH1=" ","",BH1&", ")&IF(BI1=" ","",BI1&", ")&IF(BJ1=" ","",BJ1&", ")&IF(BK1=" ","",BK1&", ")&IF(BL1=" ","",BL1&", ")&IF(BM1=" ","",BM1&", ")&IF(BN1=" ","",BN1&", ")&IF(BO1=" ","",BO1&", ")&IF(BP1=" ","",BQ1&", ")&IF(BR1=" ","",BR1&", ")&IF(BS1=" ","",BS1&", ")&IF(BT1=" ","",BT1&", ")&IF(BU1=" ","",BVS1&", ")&IF(BV1=" ","",BV1&", ")&IF(BW1=" ","",BW1&", ")&IF(BX1=" ","",BYS1&", ")&IF(BY1=" ","",BY1&", ")&IF(BZ1=" ","",BZ1&", ")&IF(CA1=" ","",CA1&", ")&IF(CB1=" ","",CB1&", ")&IF(CC1=" ","",CC1&", ")&IF(CD1=" ","",CD1&", ")&IF(CE1=" ","",CE1&", ")&IF(CF1=" ","",CF1&", ")&IF(CG1=" ","",CG1&", ")&IF(CH1=" ","",CH1&", ")&IF(CI1=" ","",CI1&", ")&IF(CJ1=" ","",CJ1&", ")&IF(CK1=" ","",CK1&", ")&IF(CL1=" ","",CL1&", ")&IF(CM1=" ","",CM1&", ")&IF(CN1=" ","",CN1&", ")&IF(CO1=" ","",CO1&", ")&IF(CP1=" ","",CP1&", ")&IF(CQ1=" ","",CQ1&", ")&IF(CR1=" ","",CR1&", ")&IF(CS1=" ","",CS1&", ")&IF(CT1=" ","",CT1&", ")&IF(CU1=" ","",CU1&", ")&IF(CV1=" ","",CV1&", ")&IF(CW1=" ","",CW1&", ")&IF(CX1=" ","",CX1&", ")&IF(CY1=" ","",CY1&", ")&IF(CZ1=" ","",CZ1&", ")&IF(DA1=" ","",DA1&", ")&IF(DB1=" ","",DB1&", ")&IF(DC1=" ","",DC1&", ")&IF(DD1=" ","",DD1&", ")&IF(DE1=" ","",DE1&", ")&IF(DF1=" ","",DF1&", ")&IF(DG1=" ","",DG1&", ")&IF(DH1=" ","",DH1&", ")&IF(DI1=" ","",DI1&", ")&IF(DJ1=" ","",DJ1&", ")&IF(DK1=" ","",DK1&", ")&IF(DL1=" ","",DL1&", ")&IF(DM1=" ","",DM1&", ")&IF(DN1=" ","",DN1&", ")&IF(DO1=" ","",DO1&", ")&IF(DP1=" ","",DP1&", ")&IF(DQ1=" ","",DQ1&", ")&IF(DR1=" ","",DR1&", ")&IF(DS1=" ","",DS1&", ")&IF(DT1=" ","",DT1&", ")&IF(DU1=" ","",DU1&", ")&IF(DW1=" ","",DW1&", ")&IF(DX1=" ","",DX1&", ")&IF(DY1=" ","",DY1&", ")&IF(DZ1=" ","",DZ1&", ")&IF(EA1=" ","",EA1&", ")&IF(EB1=" ","",EB1&", ")&IF(EC1=" ","",EC1&", ")&IF(ED1=" ","",ED1&", ")&IF(EE1=" ","",EE1&", ")&IF(EF1=" ","",EF1&", ")&IF(EG1=" ","",EG1&", ")&IF(EH1=" ","",EH1&", ")&IF(EI1=" ","",EI1&", ")

Which works but its long.

[FlameRetired]

I took a guess.

Unless Excel 2016 has the two new functions TEXTJOIN or CONCAT there is a user defined function here called Concatall:
http://www.excelforum.com/tips-and-t...ml#post3096647

The code is installed in the attached. It is:

Please Login or Register  to view this content.

Array enter this formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. It is entered in G7. (G5 has a leftover formula from an earlier guess. Ignore it.)

Please Login or Register  to view this content.

File Type: xlsm how do i merge data from several cells to one, adding a comma and ignoring empty cells.xlsm‎ (65.7 KB, 0 views) Download

[FDibbins]

See if this will help...

Please Login or Register  to view this content.

Find it here http://www.get-digital-help.com/2016...join-function/, directed there by 63falcondude from MrExcel.com

[dmg2016]

Kevinu,

I think Get & Transform(Power Query) would be a better solution for this type of problem. Its located in the data tab on Excel 2016. All you need to do is convert the row to a table, and click the from table button to get the data into power query. From there you can write a query to automatically convert the data using the toolbar.

Example in workbook:

Please Login or Register  to view this content.

Sorry, I didn't provide a great explanation, but hopefully it will help a little.

DMG

[kevinu]

Thanks thats a lot of help and options. Love it.

[FDibbins]

Happy to help and thanks for the feedback

[FlameRetired]

You're welcome. Glad to help and thanks for the feedback.