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!
Last edited by sulin360; 08-25-2013 at 06:01 PM.
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.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
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.
Last edited by sulin360; 08-25-2013 at 06:15 PM.
Nah, I still don't have it...
Last edited by sulin360; 08-25-2013 at 05:40 PM.
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.Originally Posted by mehmetcik
Yep, it was wrong! I've fixed it now. Sorry.
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.
OPk
I have a formula for you
Your Sample data is wrong in rows 3, 4 and 8 ( All row 3, All row4, and last column row 8)
Please Login or Register to view this content.
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.
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).OPk
I have a formula for you
Your Sample data is wrong in rows 3, 4 and 8 ( All row 3, All row4, and last column row 8)Please Login or Register to view this content.
Hi,
OK, here's a mod which now picks up and allows for there being duplicate values in a row.
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.
Hi
Sorry about the delay.
had to do some stuff in the garden.
Here is your file.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks