interesting excel sorting problem

[adog]

Hi:

I have an interesting excel sorting problem. I have two lists of data (list "A" and list "B&quot. Each row in each list contains a "rank" and a "value". For example:

List A: List B:
rank value rank value
A1 8 B1 10
A2 7 B2 6
A3 9 B3 4
A4 2 B4 5
A5 1 B5 3


I would like to combine A and B into a single list, and then sort the data into a specific order. The order will nominally depend on the value: i.e. "10" followed by "9", followed by "8" etc. However, the final list should obey the previously-determined group rank: i.e. no matter where the "A" entries fall relative to the "B" entries, "A1" should be before "A2", etc. Please see the sample below.


What I want:

B1 10
A1 8
A2 7
A3 9
B2 6
B3 4
B4 5
B5 3
A4 2
A5 1


I am a relatively new excel usuer...and discovered with dismay that this is not a simple "sort by", "then by" problem... Perhaps there is not an easy solution to this? I am stumped...any advice would be much appreciated! Thanks in advance for your help.

-A

 

[JVFriederick]

Construct a "key" in another column that will combine the parts into the proper sort order, then sort by that key:

=TEXT($B1,"00&quot&RIGHT($A1,1)&LEFT($A1,1)

From your description, I could not replicate the order as shown, but I think using a key will accomplish what you are attempting to do.

 

[adog]

JV-
I'm not sure if I understand your advice. I pasted your command into my spreadsheet and played with it, and couldn't quite figure out what it was supposed to do.

Let me take a step back and make sure I understand the idea that you propose:
Are you suggesting that I write a new column that will contain unique data for each entry in A and B, that will somehow know the final order when I sort the entire list based on that column? How will this "key" column in the A list "know" what the values are in the B list? I'm sure I'm showing my ignorance, here.

thanks so much for your help!
-A

 

[JVFriederick]

Sorry, I didn't mention that A and B groups need to be combined together in a single table two columns wide x the number of rows. Then construct the unique "key" in column C.

Yes, the new column will contain unique data, that when sorted, will give the desired results. However, as I mentioned earlier, I could not follow the logic of your sort.

 

[adog]

OK, I understand the process you suggest. I agree that would be a wonderful way to solve the problem. But I can't figure out how to write the formula in the "C" column to sort the data in the way I want. You wrote earlier that you "can't follow the logic of my sort." That's understandable...my first post on the subject wasn't very explicit. If you're interested, I am outlining in specific detail how I would do the sort by hand. Perhaps if this is clear, you can help me come up with a C-column formula that would acheive this (if it's possible to write such a formula).


STEP ONE: Make a combined list, and sort by the value of the entries.

A1 8 B1 10
A2 7 B2 6
A3 9 B3 4
A4 2 B4 5
A5 1 B5 3

becomes

B1 10
A3 9
A1 8
A2 7
B2 6
B4 5
B3 4
B5 3
A4 2
A5 1


STEP TWO: Tweak the new list's order, making sure that the original order of the individual lists is preserved (i.e., no matter where A3 is in the new list, it has to be after A2).

A quick look at the list above shows that we have two problems:
* A3 is listed higher than A1 and A2,
To correct this, I need to move A3 down two spots and bump A1 and A2 up one spot.

* B4 is listed higher than B3
To correct this, I need to move B4 down one spot, and bump B3 up one spot.

The new list (sorted the way I need) is thus:

B1 10
A1 8
A2 7
A3 9
B2 6
B3 4
B4 5
B5 3
A4 2
A5 1

Is this clear? Can you think of a way to write a "C-column" formula that would allow me to do this automatically? Thanks so much for all of your help!

-A

 

[JVFriederick]

Sorry, cannot follow the logic. It seems that there is no relationship between A and B, unlike the importance of 1,2,3,4 after the letter. If this is true, then why would A3 come before B2 ?

 

[adog]

There are two pieces of information in each entry. For clarity, I'll call one the "label" (A1, A2, etc.), and one the "value" (10, 9, etc.).

The "label" only has meaning when compared to others in that series. e.g. "A1" should be ranked before "A2"...but "A1" has no meaning when compared to "B2".

The "value" has meaning when compared between the series. e.g. A "10" from "A" should be ranked above a "9" from "B".


In the combined list sort, whenever deciding rank between an "A" and a "B", the *value* should dictate which is ranked higher. But when deciding between an "A" and another "A" or a "B" and another "B", the *label* should dictate which is ranked higher.

That's the best I can do. I'm sure of the logic--it makes sense, and I can do it by hand for small sets. But to do it for a large set would take forever--I need a way to do it automatically...but don't have the excel programming know-how to implement it. If the logic is still unclear to you, I guess I'll give up. Thanks for your help.

-A

 

[JVFriederick]

Allright. Let me print this out again and give it another go. There is no proof yet that I consistently understand the English language.

I'll get back with you later in any case.

 

[sfvb]

I'm not quite sure this is what you want, and I had to use two extra columns, but...

A   B   C   D   E   F   G   H   I

1

2

  A1   8  B1  10      B1  10  B2  D3

3

  A2   7  B2   6      A1   8  B3  D3

4

  A3   9  B3   4      A2   7  B4  D3

5

  A4   2  B4   5      A3   9  B5  D3

6

  A5   1  B5   3      B2   6  B5  D4

7

                      B3   4  B5  D5

8

                      B4   5  B5  D6

9

                      B5   3  B5  D7

10

                     A4   2  B6  D7

11

                     A5   1  B7  D7

The formulas are:
F2 =IF(B2>D2,A2,C2)
G2 =MAX(B2,D2)
H2 ="B"&IF(B2>D2,ROW(B2)+1,ROW(B2))
I2 ="D"&IF(B2>D2,ROW(D2), ROW(D2)+1)

F3:F11 =IF(INDIRECT(H2)>INDIRECT(I2),OFFSET(INDIRECT(H2),0,-1),OFFSET(INDIRECT(I2),0,-1))

G3:G11 =MAX(INDIRECT(H2),INDIRECT(I2))

H3:H11 =IF(INDIRECT(H2)>INDIRECT(I2),"B" &RIGHT(H2,LEN(H2)-1)+1,H2)

I3:I11 =IF(INDIRECT(H2)<INDIRECT(I2),&quot;D&quot; &RIGHT(I2,LEN(I2)-1)+1,I2)

 

[JVFriederick]

sfvb - Thanks.