Forum Discussion

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

Detlef_Lewin

Silver Contributor

Jan 02, 2023

JStroyer

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

JStroyer

Copper Contributor

Jan 03, 2023

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!

SergeiBaklan
Diamond Contributor
Jan 03, 2023
JStroyer

Depends on your Excel version, perhaps you shall use it as array formula.
- 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
    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)
Detlef_Lewin
Silver Contributor
Jan 03, 2023
JStroyer
Why didn't it work for you?
- JStroyer
  Copper Contributor
  Jan 03, 2023
  Sorry should have been more clear, its giving me the #NAME error. I'm using Excel 2016 so i believe thats why.