Need Help [Raw Data to Join Text with comma separated List]

[andersen_yunan]

Hi, I've been thinking to solve this problem for a while but to no avail
So I got bunch of raw data with delivery guys who would do shipment in a month like this

[TABLE="width: 704"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: #000000"]MON-1[/TD]
[TD="width: 64, bgcolor: #000000"]TUES-1[/TD]
[TD="width: 64, bgcolor: #000000"]WED-1[/TD]
[TD="width: 64, bgcolor: #000000"]THURS-1[/TD]
[TD="width: 64, bgcolor: #000000"]FRI-1[/TD]
[TD="width: 64, bgcolor: #000000"]SAT-1[/TD]
[TD="width: 64, bgcolor: #000000"]MON-2[/TD]
[TD="width: 64, bgcolor: #000000"]TUES-2[/TD]
[TD="width: 64, bgcolor: #000000"]WED-2[/TD]
[TD="width: 64, bgcolor: #000000"]THURS-2[/TD]
[/TR]
[TR]
[TD="bgcolor: #000000"]Andrew[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #000000"]Ashely[/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]11,00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[/TR]
[TR]
[TD="bgcolor: #000000"]Alan[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #000000"]Billy[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #000000"]Charlie[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #000000"]William[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #000000"]Wilbert[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]12,00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

and I want to make a summary data that would be like this

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Visit Day[/TD]
[/TR]
[TR]
[TD="bgcolor: #000000"]Andrew[/TD]
[TD="bgcolor: transparent"]TUES-1,THURS-1,MON-2[/TD]
[/TR]
[TR]
[TD="bgcolor: #000000"]Ashely[/TD]
[TD="bgcolor: transparent"]MON-1,WED-1,THURS-2[/TD]
[/TR]
[TR]
[TD="bgcolor: #000000"]Alan[/TD]
[TD="bgcolor: transparent"]TUES-1,FRI-1,TUES-2,WED-2[/TD]
[/TR]
[TR]
[TD="bgcolor: #000000"]Billy[/TD]
[TD="bgcolor: transparent"]SAT-1[/TD]
[/TR]
[TR]
[TD="bgcolor: #000000"]Charlie[/TD]
[TD="bgcolor: transparent"]TUES-1,TUES-2,WED-2[/TD]
[/TR]
[TR]
[TD="bgcolor: #000000"]William[/TD]
[TD="bgcolor: transparent"]WED-1,THURS-1[/TD]
[/TR]
[TR]
[TD="bgcolor: #000000"]Wilbert[/TD]
[TD="bgcolor: transparent"]MON-2[/TD]
[/TR]
</tbody>[/TABLE]

Is there any formula that I could use to have result like this?
Any help would be appreciated. Thanks in advance!

 

[mole999]

you multiple attempts to repeat posts were moderated by the system, please read the rules you accepted when you joined the site that explained what happens

 

[lrobbo314]

This custom function should do what you want.

Function getString(lVal As String, r As Range) As String
Dim V()
Dim iRow As Long
Dim Res As String
V = r.Value
For i = 2 To UBound(V)
    If V(i, 1) = lVal Then
        iRow = i
        Exit For
    End If
Next i
For j = 2 To UBound(V, 2)
    If V(iRow, j) <> "" Then
        Res = Res & V(1, j) & ", "
    End If
Next j
Res = Left(Res, Len(Res) - 2)
getString = Res
End Function

Then your formula would look like this. =getString(A12,$A$1:$K$8). Where A12 is the first name in your list of names from the table, and A1:K8 is your entire table.

I'd be interested to see if Rick could do a no loop version of this kind of like in the post below.

It would take too long to explain the following code in detail, but I thought you (or future readers of this thread) would find it interesting that a macro can be written that uses no loops... the only restriction is that there cannot be more than 65,535 rows of data total. Note that I sent the output to Column B, but it could be changed to replace the existing data by changing the B1 in the last line of code to A1.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub SplitOnSlashes()
  Dim Addr As String, Arr As Variant
  Addr = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Address
  Arr = Split(Mid(Join(Application.Transpose(Evaluate(Replace("IF(@="""","""",SUBSTITUTE(MID(@,FIND(""/"",@),LEN(@)),""/"",""-""&LEFT(@,FIND(""/"",@)-1)&""/""))", "@", Addr))), ""), 2), "-")
  Range("B1").Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

I tried but I'm still not very familiar with that method.

 

[andersen_yunan]

Hi, thank you so much for your help! it's working perfectly

 

[lrobbo314]

───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂

 

[sheetspread]

I'd be interested to see if Rick could do a no loop version of this

Like:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1		MON-1	TUES-1	WED-1	THURS-1	FRI-1	SAT-1	MON-2	TUES-2	WED-2	THURS-2
2	Andrew		12		12			12
3	Ashely	12		11							12
4	Alan		12			12			12	12
5	Billy						12
6	Charlie		12						12	12
7	William			12	12
8	Wilbert							12
9
10
11	Andrew	TUES-1,THURS-1,MON-2
12	Ashely	MON-1,WED-1,THURS-2
13	Alan	TUES-1,FRI-1,TUES-2,WED-2
14	Billy	SAT-1
15	Charlie	TUES-1,TUES-2,WED-2
16	William	WED-1,THURS-1
17	Wilbert	MON-2
Sheet10

Cell Formulas
Range	Formula
B11	=condtextjoin(B2:K2,$B$1:$K$1,",")

Function condtextjoin(rng As Range, rng2 As Range, delimiter As String)
Dim myarray As Variant
myarray = Evaluate("=IF(--ISBLANK(" & rng.Address & ")=0," & rng2.Address & ")")
condtextjoin = Replace(Replace(Join(myarray, delimiter), delimiter & "False", ""), "False" & delimiter, "")
End Function