A "not so simple" Sorting Problem

JasonTruman

Board Regular
Joined
Sep 30, 2003
Messages
81
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

Thanks in advance

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
 

Some videos you may like

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
Joined
Nov 7, 2006
Messages
8,348
Your output is wrong.

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
Joined
Sep 30, 2003
Messages
81
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
Joined
Nov 7, 2006
Messages
8,348
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
Joined
Sep 30, 2003
Messages
81

ADVERTISEMENT

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
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
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)
r.Sort Key1:=Columns([COLOR=brown]"B"[/COLOR]), order1:=xlAscending, Header:=xlYes

[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, _
  Key2:=Columns([COLOR=brown]"B"[/COLOR]), order2:=xlAscending, Header:=xlYes
Columns([COLOR=brown]"C"[/COLOR]).ClearContents
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,348

ADVERTISEMENT

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
Joined
Sep 30, 2003
Messages
81
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)
r.Sort Key1:=Columns([COLOR=brown]"B"[/COLOR]), order1:=xlAscending, Header:=xlYes

[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, _
  Key2:=Columns([COLOR=brown]"B"[/COLOR]), order2:=xlAscending, Header:=xlYes
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
Joined
Sep 30, 2003
Messages
81
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,502
Messages
5,523,295
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top