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)

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 03, 2023

JStroyer 

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

JStroyer's avatar
JStroyer
Copper Contributor
Jan 03, 2023
Yes, i think its because i'm using Excel 2016. How would i use it as an array formula? Thanks!
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    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 very much, that works great!! Really appreciate your help! 🙂
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 03, 2023

    JStroyer 

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

    • 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.

Resources