A "not so simple" Sorting Problem

[JasonTruman]

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

 

[Special-K99]

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]

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]

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.

 

[JasonTruman]

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]

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

[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]

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.

 

[JasonTruman]

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

[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]

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