# A "not so simple" Sorting Problem

#### JasonTruman

##### Board Regular
Hey all

I try and get around all my Excel difficulties myself, but this one has me stumped. I have a list of jobs (of which there will be duplicates) and each job either has a credit or a debit attached to it). I need to organise all the jobs into smallest amount per job BUT keep all other job amounts together

In the example below, I have Jobs sorted in Job order and each Job with different amounts. I would like it sorted so the lowest amount is shown first (which is -120 for Job A) but show all other amounts for Job A directly underneath it in small to large order. Then for it to show the next lowest amount (which is -110 for Job F) and repeat the process onwards

I have tried sorts, subtotals, pivots and am now at a loose end. The problem is I have 55000 records!!!

Any help would be appreciated

Jason

CURRENT WANT
JOB AMOUNT JOB AMOUNT
A -120 A -120
A 120 A -35
A -9 A -9
A 35 A 120
B 30 F -110
B -30 F -90
C 40 F 50
D -30 D -35
D -35 D -30
D 60 D 60
E -34 E -34
E 40 E 40
F -110 B -30
F 50 B 30
F -90 C 40

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### Special-K99

##### Well-known Member

You have 35 under A and -35 as the output

Use a Helper column
in column C
=MIN(IF(A\$1:A\$15=A1,B\$1:B\$15))
Array formula, use Ctrl-Shift-Enter

Now sort on column C and B

Unfortunately this is an array formula solution which may slow things down a lot due to the high volume of data you have.

#### JasonTruman

##### Board Regular
Hi Special

Yes, the value for A should be -35 so the output is the same

The helper column only returns -120 in all cells (which of course is the lowest amount) so sorting it does not help with the problem as it is only looking at A1 (value A).

Thanks anyway

#### Special-K99

##### Well-known Member
Nope, it doesn't return -120 in all cells.

You need to enter it as an array formula then you get the lowest value for each group.
Select columns A B and C, sort by column C and by column B.

Column B is now in the order you wanted.

Last edited:

#### JasonTruman

##### Board Regular

Then I'm obviously doing something wrong

Select the range of cells where I want the array formula to appear
Enter in the first cell of the range the formula required
Press CTRL+SHIFT+ENTER

All results still populate as -120

#### Akuini

##### Well-known Member
Assuming there's header in row 1 & data start at row 2, try this:

Code:
``````[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1090682a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1090682-not-so-simple-sorting-problem.html[/COLOR][/I]

[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] va
[COLOR=Royalblue]Dim[/COLOR] d [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR]

[COLOR=Royalblue]Set[/COLOR] r = Range([COLOR=brown]"A1:C"[/COLOR] & Cells(Rows.count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)

[COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
d.CompareMode = vbTextCompare
va = Range([COLOR=brown]"A2"[/COLOR], Cells(Rows.count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
[COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] d.Exists(va(i, [COLOR=crimson]1[/COLOR])) [COLOR=Royalblue]Then[/COLOR]
x = x + [COLOR=crimson]1[/COLOR]: d(va(i, [COLOR=crimson]1[/COLOR])) = x
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
va(i, [COLOR=crimson]1[/COLOR]) = d(va(i, [COLOR=crimson]1[/COLOR]))
[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"C2"[/COLOR]).Resize(UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = va
r.Sort Key1:=Columns([COLOR=brown]"C"[/COLOR]), order1:=xlAscending, _
Columns([COLOR=brown]"C"[/COLOR]).ClearContents
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]``````

#### Special-K99

##### Well-known Member

1. Copy your example data from this forum into A1 ignoring headers.
2. Use text to Columns with a space as a separator.
3. insert a column after column B. So columns D and E are now your required output.

4. copy the formula in C1 as an array formula.
5. copy the formula down the column

6. Select columns A B and C and sort by column C and column B.

The data should be int he order you specified.

Last edited:

#### JasonTruman

##### Board Regular
Assuming there's header in row 1 & data start at row 2, try this:

Code:
``````[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1090682a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1090682-not-so-simple-sorting-problem.html[/COLOR][/I]

[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] va
[COLOR=Royalblue]Dim[/COLOR] d [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR]

[COLOR=Royalblue]Set[/COLOR] r = Range([COLOR=brown]"A1:C"[/COLOR] & Cells(Rows.count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)

[COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
d.CompareMode = vbTextCompare
va = Range([COLOR=brown]"A2"[/COLOR], Cells(Rows.count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
[COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] d.Exists(va(i, [COLOR=crimson]1[/COLOR])) [COLOR=Royalblue]Then[/COLOR]
x = x + [COLOR=crimson]1[/COLOR]: d(va(i, [COLOR=crimson]1[/COLOR])) = x
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
va(i, [COLOR=crimson]1[/COLOR]) = d(va(i, [COLOR=crimson]1[/COLOR]))
[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"C2"[/COLOR]).Resize(UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = va
r.Sort Key1:=Columns([COLOR=brown]"C"[/COLOR]), order1:=xlAscending, _
Columns([COLOR=brown]"C"[/COLOR]).ClearContents
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]``````

Thank you, that has worked perfectly - I just need to apply it to my data as the actual value I need to use is in column S I think. I was just simplifying it for the purpose of understanding (I'm back home now so will check it in the morning)

Thanks

#### JasonTruman

##### Board Regular
1. Copy your example data from this forum into A1 ignoring headers.
2. Use text to Columns with a space as a separator.
3. insert a column after column B. So columns D and E are now your required output.

4. copy the formula in C1 as an array formula.
5. copy the formula down the column

6. Select columns A B and C and sort by column C and column B.

The data should be int he order you specified.

Thank you. I was highlighting the range first, whereas I put the array formula in the first cell and then copied the array formula

Works a treat now thanks

Replies
12
Views
109
Replies
11
Views
116
Replies
3
Views
31
Replies
1
Views
51
Replies
3
Views
101