0

I have a spreadsheet that contains a column with people their ZIP code and another with an ID that is assigned to them.

I would like to sort this sheet by the ID column, but also have all people with the same ZIP code grouped together.

A priority list of which row should appear next would be as follows.

  • The next lowest ID with the same ZIP code as the previous entry.
  • The next lowest ID

As an algorithmic implementation this would be fairly simple, but I can't figure out how to do this with Excel options.

What I've tried and failed at is first sorting by ZIP code and then by the ID, but this only groups the zip codes together and sorts the IDs for each ZIP code correctly. It doesn't make the first ID be on top, but rather the first ZIP code.

I have also tried creating groups for each ZIP code, but if I then sort by ID the groups do not stay together.

Some sample data would be:

ID        ZIP

TR-002    7586 KO
TR-004    7896 KL
TR-007    7586 KO
TR-012    7493 KP
TR-088    6868 KK
TR-101    7493 KP

Which I want sorted as:

ID        ZIP

TR-002    7586 KO
TR-007    7586 KO
TR-004    7896 KL
TR-012    7493 KP
TR-101    7493 KP
TR-088    6868 KK
  • Welcome to Super User! What have you tried so far? – bertieb Jul 13 '18 at 9:41
  • Good Question. I'll edit that in since it is relevant. – Roxor9999 Jul 13 '18 at 9:44
  • could you please share sample data with expected output ! – Rajesh S Jul 13 '18 at 10:41
  • I added an example. – Roxor9999 Jul 13 '18 at 12:03
  • Found an answer myself, posted it below. – Roxor9999 Jul 23 '18 at 13:17
0

I found the solution myself.

First sort by the ID then I added a new column with the following formula:

=INDEX($B$1:$B$278,MIN(IF($I$2:$I$278=$I4,ROW($I$2:$I$278))))

Replace the bounds as necessary.

How this works is that it takes a list of all rows with the same ZIP code (the IF statement) and then takes the least one of these (the one with the lowest ID). This maps every ID to the lowest ID with the same ZIP code.

I then sort by this new column and then the ID column and the data is in the order I wanted.

The reason I take the ROW value and then use INDEX to convert it back is because my ID can contain non numeric characters and the MIN function doesn't work with those.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.