Sort a string and remove duplicate entries 2

[jpl458]

Writing a data conversion app that concatenates groups of two digit state codes and is written in ECXEL vba. I redd the data into an array and output what I need to an output array Rout, and when done write the result to a worksheet. There is one column that I would like to sort immediately after the concatenation and remove duplicates in that string. Here is that portion of the code:

RinCol = 1
RoutRow = 1
RoutCol = 0

For RinRow = 2 To LastRow - 2
'If same, concatenate States and Record IDs
If Rin(RinRow, RinCol) = (Rout(RoutRow, RoutCol)) Then
Rout(RoutRow, 5) = Rout(RoutRow, 5) & "," & Rin(RinRow, 6)
'Sort Rout Here
'Remove Rout duplicates here
Rout(RoutRow, 6) = Rout(RoutRow, 6) & "," & Rin(RinRow, 0)

Else
'Move next company to Rout
Rout(RoutRow + 1, 0) = Rin(RinRow, 1)
Rout(RoutRow + 1, 1) = Rin(RinRow, 2)
Rout(RoutRow + 1, 2) = Rin(RinRow, 3)
Rout(RoutRow + 1, 3) = Rin(RinRow, 4)
Rout(RoutRow + 1, 4) = Rin(RinRow, 5)
Rout(RoutRow + 1, 5) = Rin(RinRow, 6)
Rout(RoutRow + 1, 6) = Rin(RinRow, 0)

RoutRow = RoutRow + 1

End If

String looks like FL,MO,TX,AZ,CA,CO,ID,IL,KS,MO,NE,NV,NM,OH,OR,TX,UT,WA


So I need two functions, one to sort the string and the next to remove duplicates. I want to do it in place in memory, if I can. I have looked at different solutions on the web, but they seem to be overKill for what I want. Not great at veba just OK.

Using office 10 on windows 7.

Thanks in advance

jpl

 

[SkipVought]

Hi,

Did you try sorting on the sheet and using the Remove Duplicates Excel Spreadsheet Features, that I suggested, prior to doing the concatenation?

 

[jpl458]

The concatenation is what causes duplicate. There are no duplicates prior to the concatenation.

jpl

 

[SkipVought]

Huh

Aren't you getting this data from column A? Does your code put data in the array that is not in the sheet?

I believe that you're making this much too complicated.

 

[ck1999]

i would think you should loop through your entire page and then sort. This stops you from continuously sorting after each entry.

    Dim sx() As String
    
    'strx change this to the info you want changed  so possibly (rout(routrow,5))
    strx = "nc,al,va,oh,va,nc"  '(this was my example text)
    
    sx = Split(strx, ",")
    'sort states  
    For counter1 = 0 To UBound(sx)
        For counter2 = 0 To UBound(sx) - 1
            If sx(counter1) < sx(counter2) Then
                holder = sx(counter1)
                sx(counter1) = sx(counter2)
                sx(counter2) = holder
            End If
        Next counter2
    Next counter1
    
    
  ' Remove Duplicates
    
    counter2 = UBound(sx)
      
    For counter1 = 0 To counter2 - 1
        If sx(counter1) = sx(counter1 + 1) Then sx(counter1) = ""
    Next counter1
       
    strx = ""
    For counter1 = 0 To UBound(sx)
        If sx(counter1) <> "" Then
            strx = strx & sx(counter1) & ","
        End If
    Next counter1
    
    'strx will be the output of states in alphabetical order with no duplicates (rout(routrow,5))
    strx = Left(strx, Len(strx) - 1)

if this is not what you are asking please clarify some more.

Chris

 

[strongm]

Chris, you might like to visit thread68-1745178, where the OP has provided a little more info (and has some alternative solutions)

 

[jpl458]

ck1999: I have to sort the data with each row. I doing something like an SQL group by where the ouput data is a summary of the input data. There may be 20 entries for a company and the states they are in, and I had to reduce it to one entry for the company, but have all the states they operated in. I din't want to do the sort and remove in a spreadsheet, that is not elegant, so I found a sort and delete duplicates code on the web, did some fixing up and it runs fine. Because of the nature of the problem I have started to convert it to Python, which is way better for this type of problem. I will post the code I used a little later since I am late for work right now

Thanks to all