Extracting data from a column into another but sorted and with 2 related cells next to it

[Ricko_uk]

Hi,
first time posting here so hopefully this is the right area.

Please see the attached spreadsheet with sample data. What I need to do is the following:

1) only paste data into the first sheet (formatted as already shown in there) and Excel extract data from Sheet 1 to fill in Sheet 2 and Sheet 3

2) Sheet 2 contains only 3 of the 5 columns of Sheet 1: MyIndex, Serial Number and Sales. It is sorted by Sales LOW to HIGH. And next to each Sales cell it has the related Serial Number and MyIndex value.

3) Sheet 3 is the same as Sheet 2 (explained in point 2 above) but: 1) instead of Sales it contains the Revenue column and 2) the Revenue Column is sorted from HIGH to LOW

NOTE: If it helps I can make sure that the data pasted on Sheet 1 is always a known/fixed number of rows

If possible:
- not using extra columns/sheets (but if cannot be avoided then that's ok)
- to do it with formulas and not macros/programming

Many thanks

[wk9128]

Did you make SHEET2 and SHEET3 modes from the SHEET1 source table? if yes pls ref this attachment file

[Ricko_uk]

Thank you PMwk9128

Yes.

Please see attached updated spreadsheet. I tried the following but it seems to work only for Sheet 3 and not Sheet 2:

1) I duplicated the formulas of a row in Sheet 3 to several other rows below it in Sheet 2 and also done the same in Sheet 3
2) I added some random data in Sheet 1 to test the additional formulas I copied in the additional rows in Sheet 2 and 3

But while Sheet 3 does seem to work Sheet 2 gives errors.

Why is that?

Also if you don't mind could you please explain the logic behind the formula? I looked it up and tried to figure it out but couldn't make much sense of it.

Thank you

[wk9128]

pls ref this attachment file

[oeldere]

@ Ricko_uk

Why not make a table of your data in sheet 1.

After that just the (in built) sort function.

=> Data => sort

[Ricko_uk]

Thank you AMwk9128,
now it works!

May I just ask you why in all formulas (Sheet 2 and 3) you use/reference cells which are above the one which the formula is in?

Thank you again

Attachment 692233

[JeteMc]

Put the cursor on the word ROW in ROW(A8) to see that it is the k argument for the LARGE function.
In the case of the screenshot it is retrieving the 8th largest value.
Let us know if you have any questions.