pick negative figures with all related columns data

[Zubair]

I have a huge data with many years and products, from that I want to pick only negative figures and there corresponding row/column data, please help with formula.

DATA

Product	Month	Volume	GM	CPL
Product A	Feb	12	14.00	1.17
Product B	Jan	13	15.00	1.15
Product B	Mar	14	(15.00)	(1.07)
Product B	Feb	15	10.00	0.67
Product A	Jan	16	(15.00)	(0.94)
Product A	Mar	17	15.00	0.88

Result required.

Product	Month	Volume	GM	CPL
Product B	Mar	14	(15.00)	(1.07)
Product A	Jan	16	(15.00)	(0.94)

 

[Zubair]

Peter - This is Marvellous!

The formula is perfectly matching with what I was thinking. Grate and Many thanks.

 

[Peter_SSs]

You're welcome. Hope it still works when you have the large data!

 

[Zubair]

Hope you don't mind to make some advancement in the formula.

In the "Result" tab I have made Summary structure as follows (reduced many column) and wanted to apply same formula with some modification.


1. Tab "Results" A1 to C1 I have applied data validation with list Data!Z1:Z24 copy/paste value transposed from Data!A1:X1, wanted data to come based on particular selection A1 = Area, B1 Description, C1 = Month and fixed headings D1:H1.

2. Tab "Result" J2 I put year 2021 for current year and will change 2022 for next year and so on.

3. Tab "Result" K2 for management review if they put 0 means all information required based on negative GM CPL, if 5 means less than or equal to 5 GM CPL.


Result

Area	Description	Month	Volume	Turnover US$	Std Cost US$	Std Gross Profit US$	Sum of GM CPL		Year	CPL review <=
Area Manager 1	SetJet 50 CD Bulk	Jan	2,000	114,918	189,448	(74,530)	(37.26)		2021	0​
Area Manager 1	SetJet 50 CD 1 ltr	Feb	4,563	305,906	463,190	(157,284)	(34.47)
Area Manager 1	SetPlus 20W50 CF4 1 ltr	Mar	407	27,720	41,101	(13,381)	(32.89)
Area Manager 1	SetJet 50 CD Bulk	Apr	2,000	114,918	189,448	(74,530)	(37.26)
Area Manager 1	SetJet 50 CD 1 ltr	Jan	6,676	447,513	677,606	(230,093)	(34.47)
Area Manager 4	BetJet 20W50 CNG CD 4 ltr	Feb	5,523	566,690	587,702	(21,012)	(3.80)
Area Manager 5	BetJet 20W50 CNG CD 4 ltr	May	23,942	2,456,617	2,547,703	(91,086)	(3.80)
Area Manager 5	BetJet 20W50 CNG CD 4 ltr	Jun	3,093	317,331	329,096	(11,766)	(3.80)
Area Manager 7	NetJet 5W30 SG 5 ltr	Jul	2,174	182,463	231,377	(48,915)	(22.50)
Area Manager 9	NetJet 5W30 SG 5 ltr	Jan	8,403	805,908	894,211	(88,303)	(10.51)

Data

Business

Actual/Plan

Area

Sector

Item

Description

Product Family

Pack

IMP/Local

Year

Quarter

Month

YTD No.

Volume

GSV PKR

Rebate

Discount

Turnover PKR

Std Cost PKR

Std Gross Profit PKR

Turnover US$

Std Cost US$

Std Gross Profit US$

GM CPL

Business

Region 1

Actuals

Area Manager 1

Segment 5

PKG02010000

SetJet 50 CD Bulk

Set

1​

Loc

2021​

Q1

Jan

90​

2,000

898,800

(467,376)

431,424

711,224

(279,800)

114,918

189,448

(74,530)

(37.26)

Actual/Plan

Region 1

Actuals

Area Manager 1

Segment 1

PKG02010001

SetJet 50 CD 1 ltr

Set

1​

Loc

2021​

Q1

Feb

90​

4,563

2,392,565

(1,244,134)

1,148,431

1,738,908

(590,477)

305,906

463,190

(157,284)

(34.47)

Area

Region 1

Actuals

Area Manager 1

Segment 2

PKG02020001

SetPlus 20W50 CF4 1 ltr

Set

1​

Loc

2021​

Q1

Mar

90​

407

216,808

(112,740)

104,068

154,303

(50,235)

27,720

41,101

(13,381)

(32.89)

Sector

Region 1

Actuals

Area Manager 1

Segment 4

PKG02010000

SetJet 50 CD Bulk

Set

1​

Loc

2021​

Q1

Apr

90​

2,000

898,800

(467,376)

431,424

711,224

(279,800)

114,918

189,448

(74,530)

(37.26)

Item

Region 1

Actuals

Area Manager 1

Segment 5

PKG02010001

SetJet 50 CD 1 ltr

Set

1​

Loc

2021​

Q1

Jan

90​

6,676

3,500,109

(1,820,057)

1,680,053

2,543,867

(863,815)

447,513

677,606

(230,093)

(34.47)

Description

Region 1

Actuals

Area Manager 4

Segment 2

PKG03020004

BetJet 20W50 CNG CD 4 ltr

Bet

4​

Loc

2021​

Q1

Feb

90​

5,523

4,432,223

(2,304,756)

2,127,467

2,206,349

(78,882)

566,690

587,702

(21,012)

(3.80)

Product Family

Region 1

Actuals

Area Manager 5

Segment 2

PKG03020004

BetJet 20W50 CNG CD 4 ltr

Bet

4​

Loc

2021​

Q1

May

90​

23,942

19,213,812

(9,991,182)

9,222,630

9,564,586

(341,956)

2,456,617

2,547,703

(91,086)

(3.80)

Pack

Region 1

Actuals

Area Manager 5

Segment 2

PKG03020004

BetJet 20W50 CNG CD 4 ltr

Bet

4​

Loc

2021​

Q1

Jun

90​

3,093

2,481,921

(1,290,599)

1,191,322

1,235,494

(44,172)

317,331

329,096

(11,766)

(3.80)

IMP/Local

Region 2

Actuals

Area Manager 7

Segment 2

PKG04010005

NetJet 5W30 SG 5 ltr

Net

5​

Loc

2021​

Q1

Jul

90​

2,174

1,630,954

(456,667)

(489,286)

685,001

868,636

(183,635)

182,463

231,377

(48,915)

(22.50)

Year

Region 2

Actuals

Area Manager 9

Segment 3

PKG04010005

NetJet 5W30 SG 5 ltr

Net

5​

Loc

2021​

Q1

Jan

90​

8,403

6,303,205

(3,277,667)

3,025,538

3,357,046

(331,508)

805,908

894,211

(88,303)

(10.51)

Quarter

Month

YTD No.

Volume

GSV PKR

Rebate

Discount

Turnover PKR

Std Cost PKR

Std Gross Profit PKR

Turnover US$

Std Cost US$

Std Gross Profit US$

GM CPL

Thanks in Advance.