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

[JStroyer]

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)

 

[#2]

@JStroyer 

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

[#3]

@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!

[#4]

@JStroyer 

Why didn't it work for you?

[#5]

@JStroyer 

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

[#6]

Sorry should have been more clear, its giving me the #NAME error. I'm using Excel 2016 so i believe thats why.

[#7]

Yes, i think its because i'm using Excel 2016. How would i use it as an array formula? Thanks!

[#8]

@JStroyer 

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

[#9]

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 question...my 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?

[#10 · Accepted solution]

@JStroyer 

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)

[#11]

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

[#12]

@JStroyer 

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.

[#13]

@Hans Vogelaar 

Oh, yes, it appeared in 2019

[#14]

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

[#15]

@JStroyer 

@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)