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 pictures 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

 

[Peter_SSs]

Welcome to the MrExcel board!

1. What does image a.png have to do with this?

2. Can you give us the sample data with XL2BB so that we can easily copy/paste to test with?

 

[Ricko_uk]

Thank you Peter

1) it has nothing to do with it, I uploaded it by mistake instead of Sheet 3. I tried correcting it but there does not seem to be and edit button for my original post which is very bizarre. I attached it here too but how do I edit the original post?

2) for some reason it does not allow me to install the XL2BB. This is the data that hopefully you can cut and paste:

MY Index	Serial Number	Supplier	Sales	Revenue
23​	B52XN2	AccuBuddy	21​	17,274.05​
11​	B07379JP	The Inspired Home	170​	3,568.30​
14​	6HQF8PJ	Arswintech	222​	2,323.45​
13​	B07LKK8	SKCTech	429​	7,288.71​

 

[Peter_SSs]

1) it has nothing to do with it, I uploaded it by mistake instead of Sheet 3.

OK, thought it might be something like that.

New members do not get an Edit button. It is an anti-spam measure. Once you have made a few more legitimate posts you will get one at the bottom left of your post. However, that only lasts for 10 minutes after you first make the post.

2) for some reason it does not allow me to install the XL2BB.

At what point in the process on the page I linked to does it fail and what are the symptoms and/or error messages that indicate you cannot install?

Thanks at least for giving the data in a copyable form.

Ricko_uk 1.xlsm
	A	B	C	D	E
1	MY Index	Serial Number	Supplier	Sales	Revenue
2	23	B52XN2	AccuBuddy	21	17,274.05
3	11	B07379JP	The Inspired Home	170	3,568.30
4	14	6HQF8PJ	Arswintech	222	2,323.45
5	13	B07LKK8	SKCTech	429	7,288.71
6
Sheet1

In the formulas below, you will need to adjust where I have used just 10 rows to cover whatever your data will be.

In the 2 sheets below ..
C2 formula is copied down as far as you might need.
A2 formula is copied across to B2 and down the same as column C.

Ricko_uk 1.xlsm
	A	B	C
1	Idx	SN	Sales
2	23	B52XN2	21.00
3	11	B07379JP	170.00
4	14	6HQF8PJ	222.00
5	13	B07LKK8	429.00
6
7
Sheet2

Cell Formulas
Range	Formula
A2:B7	A2	=IF($C2="","",INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!A$2:A$10)/(Sheet1!$D$2:$D$10=$C2),COUNTIF($C$2:$C2,$C2))))
C2:C7	C2	=IFERROR(AGGREGATE(15,6,Sheet1!D$2:D$10,ROWS(C$2:C2)),"")

Ricko_uk 1.xlsm
	A	B	C
1	Idx	SN	Revenue
2	23	B52XN2	17,274.05
3	13	B07LKK8	7,288.71
4	11	B07379JP	3,568.30
5	14	6HQF8PJ	2,323.45
6
7
Sheet3

Cell Formulas
Range	Formula
A2:B7	A2	=IF($C2="","",INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!A$2:A$10)/(Sheet1!$E$2:$E$10=$C2),COUNTIF($C$2:$C2,$C2))))
C2:C7	C2	=IFERROR(AGGREGATE(14,6,Sheet1!E$2:E$10,ROWS(C$2:C2)),"")