Hi,
In columns A:L in Sheet1 I have sample sales and profit growth (%) data at the report date and at a previous period (t-1) over 1 year and 3 years.
In columns N:Y I would like to extract from A:L the Product Code, Product Name, Country, Value Sales and Growth numbers for products in which the previous growth value was negative and the current value was positive for each period (1 year and 3 years), or vice-versa.
For example, Product AAA had previous growth of 2.00% (positive number) and current growth of -1.00% (negative number) over 1 year. EEE had previous growth of -1.00% and current growth of 2.00% over 3 years.
Both of these products are therefore included in N:Y.
Product CCC has previous vs current sales growth rates with the same sign in both 1 year and 3 year periods and so is not extracted to N:Y
Can someone please suggest how best to extract data based on these conditions.
Thanks!
PS Someone has previously kindly suggested a solution using FILTER () but didn't quite perform as required (probably due to my poor explanation!)
In columns A:L in Sheet1 I have sample sales and profit growth (%) data at the report date and at a previous period (t-1) over 1 year and 3 years.
In columns N:Y I would like to extract from A:L the Product Code, Product Name, Country, Value Sales and Growth numbers for products in which the previous growth value was negative and the current value was positive for each period (1 year and 3 years), or vice-versa.
For example, Product AAA had previous growth of 2.00% (positive number) and current growth of -1.00% (negative number) over 1 year. EEE had previous growth of -1.00% and current growth of 2.00% over 3 years.
Both of these products are therefore included in N:Y.
Product CCC has previous vs current sales growth rates with the same sign in both 1 year and 3 year periods and so is not extracted to N:Y
Can someone please suggest how best to extract data based on these conditions.
Thanks!
PS Someone has previously kindly suggested a solution using FILTER () but didn't quite perform as required (probably due to my poor explanation!)
| Extracting Data v2.xlsx | |||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
| 1 | PRODUCT CODE | PRODUCT NAME | COUNTRY | VALUE | 1Y SALES-1 | 1Y SALES | 1Y GROWTH-1 | 1Y GROWTH | 3Y SALES-1 | 3Y SALES | 3Y GROWTH-1 | 3Y GROWTH | PRODUCT CODE | PRODUCT NAME | COUNTRY | VALUE | 1Y SALES-1 | 1Y SALES | 1Y GROWTH-1 | 1Y GROWTH | 3Y SALES-1 | 3Y SALES | 3Y GROWTH-1 | 3Y GROWTH | |||
| 2 | AAA | AAA PLC | England | 1000 | 100 | 110 | 2.00 | -1.00 | 50.00 | 55.00 | 3.00 | 4.00 | AAA | AAA PLC | England | 1000 | 100 | 110 | 2.00 | -1.00 | |||||||
| 3 | BBB | BBB PLC | England | 1500 | 110 | 120 | 3.00 | 4.00 | 55.00 | 60.00 | 3.00 | 5.00 | EEE | EEE PLC | England | 3000 | 70 | 75 | -1.00 | 2.00 | |||||||
| 4 | CCC | CCC PLC | England | 2000 | 120 | 130 | -1.00 | -2.00 | 60.00 | 65.00 | -1.00 | -2.00 | KKK | KKK PLC | Scotland | 3500 | 150 | 160 | 2.00 | -1.00 | |||||||
| 5 | DDD | DDD PLC | England | 2500 | 130 | 140 | 3.00 | 5.00 | 65.00 | 70.00 | -2.00 | -1.00 | OOO | OOO PLC | Scotland | 5500 | 95 | 100 | -1.00 | 2.00 | |||||||
| 6 | EEE | EEE PLC | England | 3000 | 140 | 150 | -1.00 | -2.00 | 70.00 | 75.00 | -1.00 | 2.00 | VVV | VVV PLC | Wales | 6000 | 200 | 210 | 2.00 | -1.00 | |||||||
| 7 | KKK | KKK PLC | Scotland | 3500 | 150 | 160 | 2.00 | -1.00 | 75.00 | 80.00 | 3.00 | 4.00 | ZZZ | ZZZ PLC | Wales | 8000 | 120 | 125 | -1.00 | 2.00 | |||||||
| 8 | LLL | LLL PLC | Scotland | 4000 | 160 | 170 | 3.00 | 4.00 | 80.00 | 85.00 | 3.00 | 5.00 | |||||||||||||||
| 9 | MMM | MMM PLC | Scotland | 4500 | 170 | 180 | -1.00 | -2.00 | 85.00 | 90.00 | -1.00 | -2.00 | |||||||||||||||
| 10 | NNN | NNN PLC | Scotland | 5000 | 180 | 190 | 3.00 | 5.00 | 90.00 | 95.00 | -2.00 | -1.00 | |||||||||||||||
| 11 | OOO | OOO PLC | Scotland | 5500 | 190 | 200 | -1.00 | -2.00 | 95.00 | 100.00 | -1.00 | 2.00 | |||||||||||||||
| 12 | VVV | VVV PLC | Wales | 6000 | 200 | 210 | 2.00 | -1.00 | 100.00 | 105.00 | 3.00 | 4.00 | |||||||||||||||
| 13 | WWW | WWW PLC | Wales | 6500 | 210 | 220 | 3.00 | 4.00 | 105.00 | 110.00 | 3.00 | 5.00 | |||||||||||||||
| 14 | XXX | XXX PLC | Wales | 7000 | 220 | 230 | -1.00 | -2.00 | 110.00 | 115.00 | -1.00 | -2.00 | |||||||||||||||
| 15 | YYY | YYY PLC | Wales | 7500 | 230 | 240 | 3.00 | 5.00 | 115.00 | 120.00 | -2.00 | -1.00 | |||||||||||||||
| 16 | ZZZ | ZZZ PLC | Wales | 8000 | 240 | 250 | -1.00 | -2.00 | 120.00 | 125.00 | -1.00 | 2.00 | |||||||||||||||
Sheet1 | |||||||||||||||||||||||||||
