Forum Discussion
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)
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!
Depends on your Excel version, perhaps you shall use it as array formula.
- Yes, i think its because i'm using Excel 2016. How would i use it as an array formula? Thanks!
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)
I practically don't remember 2016. Try to enter the formula with Ctrl+Shift+Enter instead of Enter.
- 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?