How to make a list from an excel spreadsheet with many columns

Copper Contributor

Hi, I have an excel spreadsheet with results from an order form.  I'm trying to get a formula to format each line into a list showing exactly what each person purchased.  I was using the CONCATENATE formula with CHAR(10) to enter to a new line for each product, but then I wasn't sure how to eliminate a line if the qty was blank for a product.  Please see screenshot below.  I'd love some advice on how to make this work.  Thanks!


The formula I have so far is:  =CONCATENATE(A2,CHAR(10),"-",B2," ",B1,CHAR(10),"-",C2," ",C1,CHAR(10),"-",D2," ",D1,CHAR(10),"-",E2," ",E1,CHAR(10),"-",F2," ",F1,CHAR(10),"-",G2," ",G1)


Excel Screenshot.jpg 


14 Replies


=TEXTJOIN(CHAR(10),TRUE,A2,IF(B2:G2="","","-"&B2:G2&" "&B1:G1))


@Detlef Lewin Thanks for your help!  I tried this but it didn't work for me.  I've never used the TEXTJOIN formula, is this in conjunction with the one i had posted above or should this give me what i need including eliminating anything that is blank?  Sorry, not well versed in Excel formulas!


Why didn't it work for you?



Depends on your Excel version, perhaps you shall use it as array formula.

Sorry should have been more clear, its giving me the #NAME error. I'm using Excel 2016 so i believe thats why.
Yes, i think its because i'm using Excel 2016. How would i use it as an array formula? Thanks!


I practically don't remember 2016. Try to enter the formula with Ctrl+Shift+Enter instead of Enter.

Thanks! It didn't work on my Excel but i tried it on Google Sheets and it worked there! I couldn't get it to work there before either so this must be the fix! That is awesome, thank you so much!!
My next list will be longer, and I need it to keep grabbing the top row (product names) along with the qty. Is there a way to have it continue taking the name along with the qty from say 6B, 6C, etc?
best response confirmed by VI_Migration (Silver Contributor)


You need a different kind of formula in Excel 2016. TEXTJOIN is not available there. Try this:


=A2&IF(B2="","",CHAR(10)&"- "&B2&" "&B$1)&IF(C2="","",CHAR(10)&"- "&C2&" "&C$1)&IF(D2="","",CHAR(10)&"- "&D2&" "&D$1)&IF(E2="","",CHAR(10)&"- "&E2&" "&E$1)&IF(F2="","",CHAR(10)&"- "&F2&" "&F$1)&IF(G2="","",CHAR(10)&"- "&G2&" "&G$1)

Thanks very much, that works great!! Really appreciate your help! :)


If compare with Google Sheets when Excel 365. Both have pros and cons, depends on scenario to use. But Excel 2016 is outdated version.

As for the second part I didn't catch. Do you mean same name could be in different rows or what? I appreciate if you could illustrate on the sample.

@Hans Vogelaar 

Oh, yes, it appeared in 2019


@Sergei Baklan Yes, So the product copies as well, 6 apples, etc. Please see below screenshot. The formula @Hans Vogelaar sent made it work.



@Hans Vogelaar formula definitely works. To make it bit more human friendly

=A2 &
    IF(B2 = "", "", CHAR(10) & "- " & B2 & " " & B$1) &
    IF(C2 = "", "", CHAR(10) & "- " & C2 & " " & C$1) &
    IF(D2 = "", "", CHAR(10) & "- " & D2 & " " & D$1) &
    IF(E2 = "", "", CHAR(10) & "- " & E2 & " " & E$1) &
    IF(F2 = "", "", CHAR(10) & "- " & F2 & " " & F$1) &
    IF(G2 = "", "", CHAR(10) & "- " & G2 & " " & G$1)