Forum Discussion

JStroyer's avatar
JStroyer
Copper Contributor
Jan 02, 2023
Solved

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 ...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jan 03, 2023

    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)

JStroyer's avatar
JStroyer
Copper Contributor
Jan 03, 2023
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?
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 03, 2023

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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 03, 2023

    JStroyer 

    HansVogelaar 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)
  • JStroyer's avatar
    JStroyer
    Copper Contributor
    Jan 03, 2023

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

Resources