Need help with re-sorting a list that I am working on

[hitman989]

Hi,

I have the following sample data:

Column1 Column2
----------------------------
11111 $99999
22222 $88888
33333 $77777
44444 $66666
55555 $55555
11111 $44444
11111 $33333
55555 $22222

Column 1 contains data that have duplicate data
Column 2 is sorted from Largest to Smallest

I want to re-sort the list so that I get this:

Column1 Column2
----------------------------
11111 $99999
11111 $44444
11111 $33333
22222 $88888
33333 $77777
44444 $66666
55555 $55555
55555 $22222

I need to keep Column 2 as a primary sort, but if any of the entries from Column 1 are identical to the first row (11111 $99999) I want to list them right below it.


I hope my explanation makes sense.

Please tell me what to do. Thank you.

[MarvinP]

Hi hitman989 and welcome to the forum,

See if http://www.techonthenet.com/excel/fi...sort2_2010.php helps.

[hitman989]

Hi hitman989 and welcome to the forum,

See if http://www.techonthenet.com/excel/fi...sort2_2010.php helps.

Hi MarvinP,

Your suggested thread doesn't sort it the way I need it to.

[Richard Buttrey]

Hi,

Why not choose Col 1 as the primary sort ascending and column 2 as secondary sort descending. That gives you what you have shown in your example.

[hitman989]

Hi,

Why not choose Col 1 as the primary sort ascending and column 2 as secondary sort descending. That gives you what you have shown in your example.

Your suggestion doesn't work.
The trick is the second part of my requirement.
Column 2 is the primary sort, but if one of the items (identified by Column 1) is a duplicate, it needs to appear immediately after the first occurrence, based on the primary sort, kind of like grouping but not quite.

In my example, because 11111 entries are duplicates, but each has a different $ value.
The first 11111 entry has the highest dollar value of all my data, so I wanted to group the rest of 11111 entries right below the first 11111 entry.
Then I move onto the next highest "unique" entry of 22222.

If you cannot understand, I would probably need to provide real data so that you can see what I'm trying to accomplish.

[hitman989]

Hi,

Why not choose Col 1 as the primary sort ascending and column 2 as secondary sort descending. That gives you what you have shown in your example.

Your suggestion doesn't work.
The trick is the second part of my requirement.
Column 2 is the primary sort, but if one of the items (identified by Column 1) is a duplicate, it needs to appear immediately after the first occurrence, based on the primary sort, kind of like grouping but not quite.

In my example, because 11111 entries are duplicates, but each has a different $ value.
The first 11111 entry has the highest dollar value of all my data, so I wanted to group the rest of 11111 entries right below the first 11111 entry.
Then I move onto the next highest "unique" entry of 22222.

If you cannot understand, I would probably need to provide real data so that you can see what I'm trying to accomplish.

[MarvinP]

Hey Hitman,

There should be no duplicates in column 2 because the Pivot Table will combine the numbers into a single row.

You will need to post a workbook with what you have and what you need to see for me to go further.

[Richard Buttrey]

Your suggestion doesn't work.

In my example, because 11111 entries are duplicates, but each has a different $ value.
The first 11111 entry has the highest dollar value of all my data, so I wanted to group the rest of 11111 entries right below the first 11111 entry.
Then I move onto the next highest "unique" entry of 22222.

If you cannot understand, I would probably need to provide real data so that you can see what I'm trying to accomplish.

Why do you say my suggestion doesn't work? I took your original data and sorted it with col1 as the primary and col2 as the secondary and came up with the output you listed. Seems clear enough to me.

Are you saying that your real data is different to your sample data? In which case you can't expect us to read your mind. I suggest you upload your actual workbook and add the actual results that you expect to see.

[hitman989]

Why do you say my suggestion doesn't work? I took your original data and sorted it with col1 as the primary and col2 as the secondary and came up with the output you listed. Seems clear enough to me.

Are you saying that your real data is different to your sample data? In which case you can't expect us to read your mind. I suggest you upload your actual workbook and add the actual results that you expect to see.

I'm trying to upload the excel. The upload tool is giving me error.

[hitman989]

sample data.XLS

Folks: I managed to upload the sample excel (couldn't do it at work because of security I guess)

[Richard Buttrey]

Hi,

One way is with a helper column - see attached, and sort by the helper column descending.

Note. The data needs to be first sorted by the Unit price column, then the helper column added. At this stage you should copy the helper column and paste it back as values. Then sort by the helper column descending.

If you don't convert the helper column back to values then any sort by the helper column after the first helper sort will not give the same answer.

[hitman989]

Hi,

One way is with a helper column - see attached, and sort by the helper column descending.

Note. The data needs to be first sorted by the Unit price column, then the helper column added. At this stage you should copy the helper column and paste it back as values. Then sort by the helper column descending.

If you don't convert the helper column back to values then any sort by the helper column after the first helper sort will not give the same answer.

Hi,

Thank you for providing the solution.
However, I do not follow you with regards to the helper column.
Could you explain or demonstrate step by step what do I do?

[WHER]

Another option: run the macro "myList" in the attachment.

[Richard Buttrey]

First sort your data by Value descending
Alongside your data in the helper column enter the formulae I show and copy down all rows.
Copy the whole of the helper column and use Paste Special Values to paste the numbers back as hard values rather than formulae.
Now sort your data using the helper column descending.

[hitman989]

First sort your data by Value descending
Alongside your data in the helper column enter the formulae I show and copy down all rows.
Copy the whole of the helper column and use Paste Special Values to paste the numbers back as hard values rather than formulae.
Now sort your data using the helper column descending.

Thank you. I now know how to use your helper column.
Would you mind explaining to me what does the formula of the helper column do, so that I understand totally what the logic is?

[Richard Buttrey]

Hi,

The first part of the formula is

Please Login or Register  to view this content.

Pay particular attention to the $ signs in here. The first bit $B$5:$B5 fixes $B$5 when the formula is copied down the list. So that when the formula is copied to say row 6 the formula becomes

Please Login or Register  to view this content.

i.e. the B5 cell reference remains whilst the range of the COUNTIF has expanded to row 6. Similarly when copied to say row 10 it will read =COUNTIF($B$5:$B10,B10)
The effect of this is to count the number of occurencies of B5 (B6, B7..etc in the ranges B5:B5, B5:B6, B5:B7 ..etc.). In other words if there is more than one occurrence of a column B value, on each row where there is an occurrence the COUNTIF() function will record a value of 2,3,4,5 etc.

I suggest you test this out by putting the formula =COUNTIF($B$5:$B10,B10) in another column and copying it down to see the effect.

So knowing where the occurrencies happen, i.e. when the COUNTIF value is > 1 we can then use the MATCH() function to find on which row in the overall $B$5:$B$35 the ID number in column B FIRST occurs. The MATCH() function will return the same row number for each occurrence. So for instance the MATCH function on say row 15 of the original unsorted list will return the value 5, which means the ID 412-0001 in B15 first occurs on row 9, (i.e. the 5th row in the range B5:B35). Similarly the Match function on row 17 (the same ID) again returns the value 5.

So now we know where the first occurrence (in a list that is sorted by Value) of the ID number first appears. Knowing this we can then finally use the INDEX() function to get the UNIT Price value that occurs at the first occurrence of each ID number. If you look at the helper column you'll see that the same Value is returned for each ID and this value is the highest value since the list is first sorted by the Value column.

Of course once the list has been sorted by the helper column, the helper column is no longer working with a column already sorted by Value so any subsequent sort on the helper column would not give the same results. That's why I suggested that after the first sort the helper column values are converted to values. You'll note that I carry a copy of the formula in D1 so that should I ever need to use it again I can simply copy and paste it again - which is a technique that is useful a lot of the time.