SOLVED

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

Copper Contributor

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

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)

14 Replies

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

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

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

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

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

Why didn't it work for you?

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

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

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

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

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

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

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

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

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

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?
best response confirmed by VI_Migration (Silver Contributor)
Solution

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

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)

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

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

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

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.

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

Oh, yes, it appeared in 2019

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

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

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

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