|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Extracting data from a column into another but sorted and with 2 related cells next to it
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 |
#2
|
|||
|
|||
If i have read this right there are a few options, the old skool formula works but you need to be careful with how many rows are input and output and duplicates make it more complex.
Do a Small on the sales column =SMALL(Sheet1!$D$2:$D$5,ROWS($A$2:A2)) then index/match to bring back the serial and index numbers Probably the easiest solution is to use pivot tables but you need to put the sorted column first, but then updating could be as easy as a refresh Then we have the new dynamic arrays which i think are only in Excel 365, but they are very cool - Filter With a sort =FILTER(SORT(Sheet1!A2:E5,4,1),{1,1,0,1,0}) See attached |
#3
|
|||
|
|||
Thank you, I used that for part of the solution
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Extracting unique values from a list and sorted alphabetically | Marcia | Excel | 2 | 10-13-2018 11:25 PM |
eliminating blan rows between cells in a column cantaining data | FUGMAN | Excel Programming | 6 | 03-01-2017 07:35 AM |
Keeping the source format when extracting data from multiple cells using Vlookup function | praboos2001 | Excel | 6 | 11-15-2016 04:40 AM |
Inserted Pictures from Local Data to cells column in excel | jessicakencana | Excel Programming | 1 | 03-26-2016 03:51 AM |
Lookup a value from non-sorted data | udea | Excel | 12 | 04-25-2011 04:34 AM |