Sort comma separated values in string 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,

This is not an MS Office (native features) issue.

This would be best addressed in forum707.

 

[SkipVought]

But the short answer is to sort the data on the sheet and use the Remove Duplicates spreadsheet feature before the concatenation of your string.

 

[strongm]

Gosh, there are any number of ways of achieving this. Here's one example using Excels built-in functionality:

[blue]Option Explicit

Sub macro1()
    demo "A1", "FL,MO,TX,AZ,CA,CO,ID,IL,KS,MO,NE,NV,NM,OH,OR,TX,UT,WA"
End Sub

Sub demo(startcell As String, strSource As String)
    Dim Arr() As String
    Dim myRange As Range
    
    Arr = Split(strSource, ",")
    Set myRange = Range(startcell).Resize(UBound(Arr), 1)
    myRange.Value = Application.Transpose(Arr)
    myRange.Sort Range(startcell)
    myRange.RemoveDuplicates 1
End Sub[/blue]

 

[jpl458]

Skip, the concatenation is what causes duplicates and thanks for sending me to the right place.

Strongm I get the data in a workbook with one sheet that contains the original data. Some 10,000 rows. I am doing, in SQL parlance, a Group_By on company name. Iwill have two sheets at the end, one with the onput and another with the consolidated data. Around 4,000 rows. Since I am only fair at VBA, I would like to use your method sort and remove duplicates, but I don't see how to turn it into a function. The data to be sorted is in an array at Rout(RoutRow, 5) every time I go through the loop. If you could show me how that is done I would really appreciate it. I understand how to create a function, but need some help with your code.

Thanks in advaance

jpl

 

[SkipVought]

The concatenation comes fron DATA IN COLUMN A,

So if you were to 1) SORT column A and 2) REMOVE DUPLICATES, then CONCATENATE.

 

[jpl458]

Skip:

In the data in the worksheet each row represents an entity that has locations in states. Many of those entities have the same name but have different states. My task is to do a Group By where there is only one entity name, but have all of the states for all the entities in one column in that row. In the input data there are no duplicates in the state column. The duplicates occur after the concatenation because some of those entites with the same name operate in like states. If you look at my code I wanted to do the sort and duplicate removal on the fly in the arrays in memory. But I will go with what works. I just don't understand the code required to turn Strongm's caode into a function that I can use in the loop. Hope this is a more thorough explanation..

Thanks

jpl

 

[SkipVought]

You ought to look at the PivotTable Feature. This is a VERY POWERFUL reporting and analysis tool that every serious spreadsheet user needs to know about and how to employ to their advantage!

You wouldn't need to worry about all the things in this thread!

 

[jpl458]

Pivot tables will not work for this solution. I use big pivot tables every day. IN Pivot tables I can count the states or sum the states. I can't display the states. I put the data in memory in an array and process it into another array, and part of that process is concatenating two "Field"s, the result of which creates duplicates. When the processing in memory is done I will write the output array to a worksheet. I never mentioned column A, the data is not in column A. Strongm brought it up in his example of code. In the future I might get 100,000 rows of data, which I will process in memory because it is faster than doing it in worksheets. If there is a way of sorting a string in memory, thats what I would prefer and be able to do it as a function.

jpl

 

[SkipVought]

Well it's difficult to make targeted suggestions when the big picture is missing. You've given us just a peek so such focused information is improbable.

Major issue is that you've assumed that your approach is the best approch!