How can I order rows in excel based on relationship via vba?
the first post is here, hopefully I'll give enough information to get some help.
I have a dataset in excel that contains multiple customers in column 1, I want to order it based on a relationship in blocks of 10. So, for example, if the ratio was 60/40 between customer 1 and 2, it would have to order 6 lines for client 1, 4 lines for client 2, 6 lines for client 1, 4 lines for client 2 to end of data.
Is there a way to do this, either by sorting in excel or using VBA, I have been trying for days and have been stumped.
Thank.
source to share
Hope this helps. This will place your blocks in columns next to each other. I haven't tested this, but hopefully the idea gets you somewhere.
Sub ClientRatios()
'DataSet is in range A:A
'Make the ratios of the Clients Add up to 10
'You can have a maximum of 10 clients
Dim Nextempty As Range
Dim j As Long
Dim i As Long
'Repeat the following idea for each client and you should get what you're looking for
'+++Client1+++
'=================================
i = Range("Clinet1_Ratio")
ClientName = Range("Clinet1_Name")
'=================================
Total = Application.WorksheetFunction.CountIf(Range("A:A"), ClientName)
Do While Total < 0
j = i
c = 2
Do While j > 0
If j > Total Then j = Total
Nextempty = Cells(Cells.Rows.Count, Columns(c)).End(xlUp).Row + 1
Nextempty = ClientName
j = j - 1
Loop
c = c + 1
Total = Total - i
Loop
'+++Client2+++
'Do the same for all 10 clients
End Sub
source to share
Welcome to SO. This is not a coding service, so I'll give you an idea of how to fix this problem.
I suggest you use the object Dictionary
found in the Microsoft Scripting Runtime Library (included with Windows). Links here and here
- Read the raw data in
Dictionary
to keep the customer names and counts of each name. - Scroll through the cells of your relationship, each time writing down customer names in the dictionary and then decreasing the number. If count = 0 for a name, do not write down that name.
- When all customer names are counted in the dictionary = 0, end the loop and you're done.
source to share