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

Ricko_uk

New Member
Joined
Aug 22, 2020
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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 :)
 

Attachments

  • a.png
    a.png
    28.3 KB · Views: 6
  • Sheet 1.png
    Sheet 1.png
    10.4 KB · Views: 5
  • Sheet 2.png
    Sheet 2.png
    14.7 KB · Views: 5

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,513
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 22, 2020
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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 IndexSerial NumberSupplierSalesRevenue
23​
B52XN2AccuBuddy
21​
17,274.05​
11​
B07379JPThe Inspired Home
170​
3,568.30​
14​
6HQF8PJArswintech
222​
2,323.45​
13​
B07LKK8SKCTech
429​
7,288.71​
 

Attachments

  • Sheet 3.png
    Sheet 3.png
    15.7 KB · Views: 1
  • 1598156007957.png
    1598156007957.png
    34.6 KB · Views: 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,513
Office Version
  1. 365
Platform
  1. Windows
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. (y)


Ricko_uk 1.xlsm
ABCDE
1MY IndexSerial NumberSupplierSalesRevenue
223B52XN2AccuBuddy2117,274.05
311B07379JPThe Inspired Home1703,568.30
4146HQF8PJArswintech2222,323.45
513B07LKK8SKCTech4297,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.

Cell Formulas
RangeFormula
A2:B7A2=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:C7C2=IFERROR(AGGREGATE(15,6,Sheet1!D$2:D$10,ROWS(C$2:C2)),"")



Cell Formulas
RangeFormula
A2:B7A2=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:C7C2=IFERROR(AGGREGATE(14,6,Sheet1!E$2:E$10,ROWS(C$2:C2)),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,119,058
Messages
5,575,850
Members
412,688
Latest member
KYLE1788
Top