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

andersen_yunan

New Member
Joined
Feb 7, 2018
Messages
36
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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
This custom function should do what you want.

Code:
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.
Code:
[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.
 
Upvote 0
───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂
 
Upvote 0
I'd be interested to see if Rick could do a no loop version of this

Like:


Excel 2010
ABCDEFGHIJK
1MON-1TUES-1WED-1THURS-1FRI-1SAT-1MON-2TUES-2WED-2THURS-2
2Andrew121212
3Ashely121112
4Alan12121212
5Billy12
6Charlie121212
7William1212
8Wilbert12
9
10
11AndrewTUES-1,THURS-1,MON-2
12AshelyMON-1,WED-1,THURS-2
13AlanTUES-1,FRI-1,TUES-2,WED-2
14BillySAT-1
15CharlieTUES-1,TUES-2,WED-2
16WilliamWED-1,THURS-1
17WilbertMON-2
Sheet10
Cell Formulas
RangeFormula
B11=condtextjoin(B2:K2,$B$1:$K$1,",")


Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top