I have this data:
Name 1 2 3 4 Bob X X X Jim X Ned X X
I need it to look like this:
Name Values Bob 1,3,4 Jim 3 Ned 1,4
I found a following formula, which works well for the first column. But I don't know how to customize to add the possibilty of more than one item in the list.
=IFERROR(IF(SEARCH("*X*",H2,1),"1"),"No")
Can you suggest a formula?
Re: Convert columns with Xs to comma delimited list
With PowerQuery:
Hi Sandy. The attached Excel file doesn't appear include any code. All I see is general text. Was I punk'd?
Hello ThumperStrauss and Welcome to Excel Forum.
Here is a formula based option.
1) Four helper columns (which may be moved and/or hidden for aesthetic purposes) are populated using: =IF(B2="X",COLUMN(A:A)&", ","")
2) The column with values is populated using: =LEFT(F2&G2&H2&I2,LEN(F2&G2&H2&I2)-2)
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
It is PowerQuery so you'll need PowerQuery add-in for Ex2013 Pro Plus then you will see how it works (btw. code is inside PowerQuery - result is a data only)Originally Posted by ThumperStrauss
Try this, adapted from a solution developed by daddylonglegs on a similar sort of post from a day or two ago. DLL - imitation is the sincerest form of flattery!!
=SUBSTITUTE(TRIM(SUBSTITUTE(TEXT(SUMPRODUCT(IFERROR(AGGREGATE(15,6,B$1:E$1/(B2:E2="X"),{1,2,3,4}),0),100^{3,2,1,0}),"00 00 00 00"),"00",""))," ",", ")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks