Hello,
There are times where I have to add 2-4 more orders from the Master column to the Individual worksheet.
Example, B12 might have prices that auto populate from another program.
I would need to add the 4 highest prices from the Master columns with order numbers below it without duplicating any of them.
If B12 & B13 had prices, I would need to add 3 more orders. I need to keep the prices that populate in column B and add to it.
I thought I could use the Large function which works perfectly if the prices that auto populate are the highest prices in the list.
For example, if I change the price of B12 to 35.02, the next entry becomes 3487.76. It should be 5894.21.
Is there a way to show the next highest prices without duplicating what is already listed?
Any help is appreciated
There are times where I have to add 2-4 more orders from the Master column to the Individual worksheet.
Example, B12 might have prices that auto populate from another program.
I would need to add the 4 highest prices from the Master columns with order numbers below it without duplicating any of them.
If B12 & B13 had prices, I would need to add 3 more orders. I need to keep the prices that populate in column B and add to it.
I thought I could use the Large function which works perfectly if the prices that auto populate are the highest prices in the list.
For example, if I change the price of B12 to 35.02, the next entry becomes 3487.76. It should be 5894.21.
Is there a way to show the next highest prices without duplicating what is already listed?
Any help is appreciated
Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Individual | Master | |||||||
2 | Order | Price | Code | Order | Price | Code | |||
3 | w1111 | 752 | BK | 1026w8 | 14.12 | Crt | |||
4 | w1112 | 367.94 | BK | 1026w6 | 20.32 | Crt | |||
5 | w1113 | 358.56 | BK | 1026w28 | 47.47 | Crt | |||
6 | w1234 | 97.89 | BK | 1026w26 | 41.66 | Crt | |||
7 | w8769 | 1112.89 | BK | 1026w24 | 35.02 | Crt | |||
8 | w5421 | 3574.76 | BK | 1026w22 | 29.28 | Crt | |||
9 | 1202 | 5981.21 | BK | 1026w20 | 23.08 | Crt | |||
10 | 1203 | 107.32 | BK | 1026w18 | 16.88 | Crt | |||
11 | 1204 | 101.12 | BK | 1026w16 | 10.68 | Crt | |||
12 | 1025w6 | 5894.21 | Crt | 1026w14 | 9.58 | Crt | |||
13 | 9999 | 3487.76 | 1026w12 | 1.72 | Crt | ||||
14 | 9988 | 1025.89 | 1026w10 | 7.92 | Crt | ||||
15 | 9984 | 665 | 1025w6 | 5894.21 | Crt | ||||
16 | 9985 | 280.94 | 9999 | 3487.76 | Crt | ||||
17 | 9988 | 1025.89 | Crt | ||||||
18 | 9987 | 10.89 | Crt | ||||||
19 | 9986 | 271.56 | Crt | ||||||
20 | 9985 | 280.94 | Crt | ||||||
21 | 9984 | 665 | Crt | ||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A12 | A12 | =INDEX($E$3:$E$21,AGGREGATE(15,6,(ROW($F$3:$F$21)-ROW($F$3)+1)/($F$3:$F$21=$B12),COUNTIF($B12:B$12,$B12))) |
A13:A16 | A13 | =INDEX($E$3:$E$21,AGGREGATE(15,6,(ROW($F$3:$F$21)-ROW($F$3)+1)/($F$3:$F$21=$B13),COUNTIF($B$12:B13,$B13))) |
B13:B16 | B13 | =LARGE($F$3:$F$21,ROWS(B$12:B13)) |