Ordering one table by the rules from another table

[sulin360]

Hello there!

I'm trying to organise two table of matching values by descending order. Here the values in the columns A-B-C-D respectively correspond to ones in columns E-F-G-H (A correspond to E, B to F, etc.). I would like to order the contents of A-B-C-D table and reorganise the E-F-G-H table to keep this relationship.

I include the example file to showcase my idea. I'm looking for formula ideas to automate this process.

Thanks!

[Richard Buttrey]

Hi,

Are you sure all your green results are correct? See attached. The formulas give the right result for the yellow table but only agree with the first row of your green table. If yours are correct can you explain the working.

[sulin360]

Hi,

Are you sure all your green results are correct? See attached. The formulas give the right result for the yellow table but only agree with the first row of your green table. If yours are correct can you explain the working.

Yep, it was wrong! I've fixed it now. Sorry.

[sulin360]

Hi, OP here. The original file was incorrect (my bad!), so I fixed it.

I've attempted solving this problem myself, but I did't get too far with it. Here is what I have now.

Here the red table lists the new positions of the entries in the green table. So that to get a new green table the old one should rearrange itself in the way listed in the red one. I've mistakenly used the LARGE function to get there (blue table), but the logic was flawed.

[Richard Buttrey]

OK,

I hope we're not going round in circles here, but your new green table in N1:Q9 now agrees with the table I gave you in the workbook I attached originally, in E13:H21.

i.e. I queried whether your original table was correct and you agreed it wasn't and you had made a mistake. I believe that this means that my first solution was correct. Do you agree now.

[sulin360]

OK,

I hope we're not going round in circles here, but your new green table in N1:Q9 now agrees with the table I gave you in the workbook I attached originally, in E13:H21.

i.e. I queried whether your original table was correct and you agreed it wasn't and you had made a mistake. I believe that this means that my first solution was correct. Do you agree now.

Sorry, I missed it completely!

The solution almost works, however the formula produces anomalies when it deals with two ordering possibilities. E.g. if you look at rows 8 and 20 the solution is 50-67<->44-86 (where 67 and 44 are interchangeable), but the formula results in double 67s, which is not in the original data.

[Richard Buttrey]

Sorry, I missed it completely!

The solution almost works, however the formula produces anomalies when it deals with two ordering possibilities. E.g. if you look at rows 8 and 20 the solution is 50-67<->44-86 (where 67 and 44 are interchangeable), but the formula results in double 67s, which is not in the original data.

Hi,

OK, here's a mod which now picks up and allows for there being duplicate values in a row.

[sulin360]

Hi, thanks for your help, Richard.

Is there a way to allow for such duplicates in the other places? The data I'll be restructuring has over 12000 rows, so I'm sure there will be all sorts of possible permutations of such anomalies. Here is your file where I made up some data to reveal problems with code.

[sulin360]

Nah, I still don't have it...

[mehmetcik]

OPk

I have a formula for you

=OFFSET($E1,0,MATCH(J1,$A1:$D1,0)-1)

Your Sample data is wrong in rows 3, 4 and 8 ( All row 3, All row4, and last column row 8)

[sulin360]

OPk

I have a formula for you

=OFFSET($E1,0,MATCH(J1,$A1:$D1,0)-1)

Your Sample data is wrong in rows 3, 4 and 8 ( All row 3, All row4, and last column row 8)

Thanks for your efforts! I really appreciate it! There is a problem however. In the row 8 of your file you can see that the OFFSET formula produces double 67s instead of 67-44 (interchangeable order).

[mehmetcik]

Ok

I have figured out how to do this with a macro.

will your data always be in this format?

[sulin360]

Ok

I have figured out how to do this with a macro.

will your data always be in this format?

Unfortunately no, there will be all sorts of formats I'll have to use. So formula solution is much preferred... I'll edit my previous entries to show what I have so far in a second.

[mehmetcik]

Hi

Sorry about the delay.

had to do some stuff in the garden.

Here is your file.