mercredi 29 mars 2017

Progressive column grouping spacing issue

I have four columns of Ident codes corresponding to a single individual with multiple entries. I need to get a list of all unique Ident codes corresponding to each individual on one line with a comma followed by a space between them. I have a VBA to remove the duplicates, but the VBA won't work - I think because the commas don't have a space after them in the final row of Ident code data that I'm asking it to look at. I feel like there is an easy solution and I'm just not seeing it. I'm VERY new to VBA and moderately good with Excel.

This is the statement I'm using to return the progressive values of codes pertaining to each person, the final row being the culmination of all of their codes in one cell

=IF(A12=A11,P11&"," &O12, O12)

See the attached file column P - when it gets to four or more codes there's no space after the comma any longer and the VBA duplicate remover doesn't work.

[Sample of my Worksheet

After I get that formula down I should be able to run this VBA to get rid of the duplicates in the line with the culmination of all the codes... I think...

Function RemoveDupes2(txt As String, Optional delim As String = " ") As String
Dim x
'Updateby20140924
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each x In Split(txt, delim)
        If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
    Next
    If .Count > 0 Then RemoveDupes2 = Join(.keys, delim)
End With
End Function

I don't know what else to try, there are hundreds of thousands of these that I need to go through

Any help anyone can give me would be amazing and I would be so thankful. I found the formulas and VBA on this site and modified them to my need, but I think I don't quite understand it enough to modify it correctly.

Aucun commentaire:

Enregistrer un commentaire