How to create lists for custom sort in VBA

[Waimea]

Hi, I am currently using custom sort to sort a big list.

I want to apply the custom sort 39 or n times.

Sub SortCity2()
  
range("A42:H81").Select


ActiveWorkbook.worksheets("Snabb2").Sort.SortFields.Clear


ActiveWorkbook.worksheets("Snabb2").Sort.SortFields.add key:=range("A42"), SortOn:=xlSortOnValues, _
    Order:=xlAscending, CustomOrder:="City2, City1, City3, City4, City5, City6, City7, City8, City9, City10", City11, City12, City13, City14, City15, City16, City17, City18, City19, City20, City21, City22, City23, City24, City25, City26, City27, City28, City29, City30, City31, City32, City33, City34, City35, City36, City37, City38, City39, City40", DataOption:=xlSortNormal
    
With ActiveWorkbook.worksheets("Snabb2").Sort
.SetRange range("A42:H81")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

The sorted list contains 40 * 40 cities for a total of 1600 rows + 1 header row.

The list starts with City1 to City40, then it starts over at City2, City1, City3, ..., City40.

City1, City2, City3, City4, City5, City6, City7, City8, City9, City10", City11, City12, City13, City14, City15, City16, City17, City18, City19, City20, City21, City22, City23, City24, City25, City26, City27, City28, City29, City30, City31, City32, City33, City34, City35, City36, City37, City38, City39, City40

City2, City1, City3, City4, City5, City6, City7, City8, City9, City10", City11, City12, City13, City14, City15, City16, City17, City18, City19, City20, City21, City22, City23, City24, City25, City26, City27, City28, City29, City30, City31, City32, City33, City34, City35, City36, City37, City38, City39, City40

City3, City1, City2, City4, City5, City6, City7, City8, City9, City10", City11, City12, City13, City14, City15, City16, City17, City18, City19, City20, City21, City22, City23, City24, City25, City26, City27, City28, City29, City30, City31, City32, City33, City34, City35, City36, City37, City38, City39, City40

City4, City1, City2, City3, City5, City6, City7, City8, City9, City10", City11, City12, City13, City14, City15, City16, City17, City18, City19, City20, City21, City22, City23, City24, City25, City26, City27, City28, City29, City30, City31, City32, City33, City34, City35, City36, City37, City38, City39, City40

Edit: In my workbook the city names doesn't contain the numbers after the cities.

 

[Waimea]

Perhaps a sub or function that passes the array, the number of items and the nth position would work?

I don't know however where to start to create such a sub or function?

Function CUSTOMSORT(myArray As Array, numberOfItems as Integer, nthPosition As Integer) As Variant
' code to change the value of the 1 first row to the nth position

End function

 

[Waimea]

Could someone give me some pointers on how to go about creating a sub that places the nth element first in a list?

 

[Waimea]

I have created an array with 40 items in it, with index at 0.

I can access the arrays positions by myarray(0) to myarray(39).

I want to use the array with the nth item as myarray(nth).

I guess I could create 40 arrays but that seems like a lot of manual work?

 

[Akuini]

I don't know how to use custom sort to solve your problem.
How about using a temporary helper column?
I can write a macro to insert a sequence of numbers into the helper column so you can sort data according to your requirement above.

 

[Waimea]

Hi Akuini,

thank you for your reply! I am not using custom sort to solve this, I am trying to create automated lists to use with custom sort.

Can you explain how you would solve it using a helper column?

 

[Akuini]

Hi Akuini,

thank you for your reply! I am not using custom sort to solve this, I am trying to create automated lists to use with custom sort.

Can you explain how you would solve it using a helper column?

For example try this:

Sub a1081405a()
'https://www.mrexcel.com/forum/excel-questions/1081405-how-create-lists-custom-sort-vba.html
Dim i As Long, n As Long, m As Long, k As Long, c As Long
Dim x As String
Dim va As Variant, z As Variant
Dim dar As Object

va = Range("A1", Cells(Rows.count, "A").End(xlUp))
Set dar = CreateObject("System.Collections.Sortedlist")
    For i = 1 To UBound(va, 1)
        If Not dar.contains(va(i, 1)) Then
         dar.Add va(i, 1), 1
        End If
    Next

For c = 0 To dar.count - 1
    n = 10101 + m
        For k = 0 To dar.count - 1
             z = dar.GetKey(k)
                For i = 1 To UBound(va, 1)
                    If va(i, 1) = z Then
                        va(i, 1) = n
                        n = n + 1
                        Exit For
                    End If
                Next
        Next
    m = m + 100
Next

For i = 1 To UBound(va, 1)
    x = CStr(va(i, 1))
    If Mid(x, 2, 2) = Mid(x, 4, 2) Then
        va(i, 1) = Left(x, Len(x) - 1) & "0"
    End If
Next

Range("C1").Resize(UBound(va, 1), 1) = va

End Sub

EXAMPLE:

Book1
	A
1	Harry
2	Mark
3	Mark
4	Harry
5	Aaden
6	Aaden
7	Leandro
8	Leandro
9	Aaden
10	Harry
11	Leandro
12	Leandro
13	Aaden
14	Mark
15	Harry
16	Mark
Sheet1

AFTER RUNNING THE MACRO:

Book1
	A	B	C
1	Harry		10102
2	Mark		10104
3	Mark		10204
4	Harry		10200
5	Aaden		10100
6	Aaden		10201
7	Leandro		10103
8	Leandro		10203
9	Aaden		10301
10	Harry		10302
11	Leandro		10300
12	Leandro		10403
13	Aaden		10401
14	Mark		10304
15	Harry		10402
16	Mark		10400
Sheet1

YOU CAN SORT BY COLUMN C, THE RESULT:

Book1
	A	B	C
1	Aaden		10100
2	Harry		10102
3	Leandro		10103
4	Mark		10104
5	Harry		10200
6	Aaden		10201
7	Leandro		10203
8	Mark		10204
9	Leandro		10300
10	Aaden		10301
11	Harry		10302
12	Mark		10304
13	Mark		10400
14	Aaden		10401
15	Harry		10402
16	Leandro		10403
Sheet1

 

[Waimea]

Hi, thank you for your reply! I am not sure I get what you are doing, you are creating a helper column with unique rankings in them but after that I don't understand?

I want to run custom sort 39 or 40 times, each time I want the first item/city/name to change.

Ex.

First custom sort:
Aaden (1), Harry (2), Leandro (3), Mark (4), Harry (5)

Second custom sort:
Harry (2), Aaden (1), Leandro (3), Mark (4), Harry (5)

Third custom sort:
Leandro (3), Aaden (1), Harry (2), Mark (4), Harry (5)

Fourth custom sort:
Mark (4), Aaden (1), Harry (2), Leandro (3), Harry (5)

Fifth custom sort:
Harry (5), Aaden (1), Harry (2), Leandro (3), Mark (4)

EDIT: I got it wrong!

 

[Akuini]

The sorted list contains 40 * 40 cities for a total of 1600 rows + 1 header row.

Hm, I might misunderstand something
You have data that contains 40 unique cities in 1600 rows, right?

 

[Waimea]

Hi, I think it is my fault that I explained it poorly and I apologize.

I have 1600 rows where the city1 to city40 repeats in batches of 40. I am using VBA to insert formulas into my worksheet and for every 40th row I want to change the reference what is being used in the formulas.

Perhaps you could help me to figure out how to offset

"=SUMPRODUCT($B$2:$H$2,B2:H2)"

so that it increases with 40 rows but with 40 + 1 rows for the reference. The reference is B2:H2 for the first 40 rows, the reference is B42:H42 for the next 40 rows and so on.

Name	Metric1	Metric2	Metric3
City1	65	71	95		City1 is at the top
City2	45	63	1
City3	66	91	16
City4	28	70	54
City5	30	12	19
City6	76	34	2
City7	74	24	47
City8	0	46	29
City9	55	6	24
City10	67	93	35
City11	85	1	12
City12	35	16	89
City13	39	96	29
City14	39	93	69
City15	60	9	100
City16	12	50	2
City17	77	97	99
City18	42	21	12
City19	13	9	55
City20	13	20	10
City21	68	69	33
City22	70	89	20
City23	51	82	4
City24	3	30	15
City25	9	76	98
City26	71	59	34
City27	44	54	97
City28	35	31	13
City29	80	77	96
City30	42	15	90
City31	25	66	13
City32	3	98	93
City33	63	60	20
City34	77	64	90
City35	42	3	66
City36	83	89	63
City37	27	75	7
City38	0	60	64
City39	73	69	29
City40	32	91	28
City2	54	85	19		City2 is at the top
City1	42	30	32
City3	78	1	20
City4	63	44	68
City5	13	85	95
City6	42	16	81
City7	21	8	2
City8	78	86	9
City9	89	1	12
City10	90	87	52
City11	60	1	13
City12	76	85	54
City13	65	28	78
City14	51	54	72
City15	58	50	10
City16	21	72	85
City17	81	62	58
City18	14	30	10
City19	25	8	10
City20	41	4	19
City21	86	20	50
City22	53	36	54
City23	92	100	55
City24	20	85	71
City25	12	97	69
City26	28	3	91
City27	28	68	36
City28	62	93	47
City29	39	25	28
City30	93	51	41
City31	45	17	99
City32	97	89	97
City33	91	31	72
City34	18	67	40
City35	59	78	92
City36	73	89	80
City37	74	0	85
City38	30	0	33
City39	66	81	90
City40	43	3	86
City3	67	11	44		City3 is at the top
City1	21	48	55
City2	25	52	31
City4	85	90	60
City5	55	7	48
City6	99	35	65
City7	48	80	21
City8	8	55	92
City9	71	44	49
City10	47	3	13
City11	27	28	3
City12	91	91	44
City13	91	59	28
City14	98	24	40
City15	18	47	32
City16	88	83	38
City17	81	37	0
City18	79	79	11
City19	29	52	86
City20	100	46	72
City21	40	48	20
City22	51	91	59
City23	99	39	42
City24	57	8	96
City25	76	73	96
City26	22	6	8
City27	15	100	91
City28	72	62	15
City29	56	24	79
City30	96	35	95
City31	31	37	17
City32	86	17	0
City33	72	58	55
City34	27	60	19
City35	1	74	34
City36	45	62	17
City37	22	7	86
City38	76	31	45
City39	3	47	82
City40	75	84	99
City4	73	6	5		City4 is at the top
City1	73	89	69
City2	58	48	70
City3	36	9	34
City5	76	70	86
City6	94	91	100
City7	38	53	22
City8	33	48	21
City9	1	98	100
City10	98	98	54
City11	71	45	91
City12	14	68	95
City13	71	69	94
City14	24	53	99
City15	69	49	25
City16	42	18	78
City17	66	89	4
City18	81	98	68
City19	43	98	15
City20	47	73	48
City21	70	82	49
City22	29	65	39
City23	38	40	91
City24	80	100	79
City25	83	65	99
City26	66	13	91
City27	52	35	29
City28	62	48	56
City29	83	70	88
City30	41	64	43
City31	28	64	12
City32	60	87	51
City33	33	29	40
City34	34	23	0
City35	93	5	33
City36	29	64	7
City37	97	92	16
City38	45	24	61
City39	36	55	70
City40	37	27	66
etc

<tbody>
</tbody>