Forum Discussion
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)
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)
14 Replies
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.
