Combining Data Into A Single Row

[bornsteij]

This is how the spreadsheet looks now....

Claim ID Rule # Payer<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
100 6474 Medicaid-NY
100 3200 Medicaid-NY
<o> </o>
<o> </o>
What I want to get to:
<o> </o>
Claim ID Rule # Payer<o></o>
100 6474; 3200 Medicaid-NY
<o> </o>
Once it's in the format above I'll use a text to columns to separate the rule numbers. What's the easiest way to get the data in this format?

Thanks,

Jake

 

[MickG]

Try this:-
I assumed that for every uniqueClaim I/D the payer was the same, as per your example.
This will place in columns starting column "D".

[COLOR="Navy"]Sub[/COLOR] MG20Apr30
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To Columns.Count)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.value) [COLOR="Navy"]Then[/COLOR]
        n = n + 1
        .Add Dn.value, Array(n, 2)
        ray(n, 1) = Dn.value: ray(n, 2) = Dn(, 2): ray(n, 3) = Dn(, 3)
[COLOR="Navy"]Else[/COLOR]
        Q = .Item(Dn.value)
        Temp = ray(Q(0), Q(1) + 1)
        Q(1) = Q(1) + 1
        ray(Q(0), Q(1)) = Dn(, 2):
        ray(Q(0), Q(1) + 1) = Temp
        oMax = Application.Max(oMax, Q(1))
        .Item(Dn.value) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Range("D2").Resize(.Count, oMax + 1) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick