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)

 

[alansidman]

Highlight your first row (Headers)
Select Data-->Filter
Click on the drop down filter for GM column
Select Number Filters
Select Less than
Place a 0 in the criteria.

 

[Zubair]

Highlight your first row (Headers)
Select Data-->Filter
Click on the drop down filter for GM column
Select Number Filters
Select Less than
Place a 0 in the criteria.

Hi alansidman,

Many thanks but I wanted to bring data in other sheet.

 

[alansidman]

Highlight filtered data
copy-->paste.

If you want to automate, then do these steps and record as Macro.

 

[alansidman]

Here is a vba code for you

Option Explicit

Sub NegNum()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim i As Long, lr As Long, lr2 As Long
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    s1.Range("A1:E1").Copy s2.Range("A1")
    For i = 2 To lr
        If s1.Range("D" & i) < 0 Then
            s1.Range("A" & i & ":E" & i).Copy
            lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row + 1
            s2.Range("A" & lr2).PasteSpecial xlPasteValues
        End If
    Next i
    Application.CutCopyMode = False
    MsgBox "Completed"
End Sub

 

[Peter_SSs]

I wanted to bring data in other sheet.

Does the other sheet already exist?
If so, does it already have any other data on it?

Is it ever possible that GP is less than zero and CPL is greater than zero, or vice versa? If so, what should happen with those rows?

How big (approximate number of rows) is your 'huge' data?

Is there just 5 columns & and are they A:E?

 

[Zubair]

Does the other sheet already exist?
If so, does it already have any other data on it?

Is it ever possible that GP is less than zero and CPL is greater than zero, or vice versa? If so, what should happen with those rows?

How big (approximate number of rows) is your 'huge' data?

Is there just 5 columns & and are they A:E?

in new sheet which is blank
yes GP is less than Zero and I want to shown only those sales transactions which are having negative Gross Margin (means selling price is less than cost).
I have 24 rows and 27910 columns and data is keep increasing.
Column A:X and GM CPL is in column X

 

[Zubair]

Business

Actual/Plan

Area

Sector

Item

Description

Product Family

Pack

IMP/Local

Year

Quarter

Month

YTD No.

Volume

GSV SAR

Rebate

Discount

Turnover SAR

Std Cost SAR

Std Gross Profit SAR

Turnover US$

Std Cost US$

Std Gross Profit US$

GM CPL

Region 4

Actuals

Area Manager 18

Segment 4

PKG09010208

HyoJet 68 208 ltr

Hyo

208​

Loc

Actuals

Q1

Jan

90​

13,408

6,456,045

(129,121)

(3,357,143)

2,969,781

5,109,318

(2,139,537)

791,056

1,360,961

(569,905)

(42.50)

 

[Peter_SSs]

I have 24 rows and 27910 columns and data is keep increasing.

Are you sure you have that the correct way around?
Rows run across the worksheet and columns run down the worksheet

Column A:X and GM CPL is in column X

Previously you showed GM and CPL in separate columns, now you show them in a single column. Can you confirm which it actually is?

Are there any formulas in the worksheet?

in new sheet which is blank

My question was does the sheet already exist? Or can the code create a new sheet as part of the process?

 

[Zubair]

Business

Actual/Plan

Area

Sector

Item

Description

Product Family

Pack

IMP/Local

Year

Quarter

Month

YTD No.

Volume

GSV SAR

Rebate

Discount

Turnover SAR

Std Cost SAR

Std Gross Profit SAR

Turnover US$

Std Cost US$

Std Gross Profit US$

GM CPL

Region 4

Actuals

Area Manager 18

Segment 4

PKG09010208

HyoJet 68 208 ltr

Hyo

208​

Loc

Actuals

Q1

Jan

90​

13,408

6,456,045

(129,121)

(3,357,143)

2,969,781

5,109,318

(2,139,537)

791,056

1,360,961

(569,905)

(42.50)

Are you sure you have that the correct way around?
Rows run across the worksheet and columns run down the worksheet

apologies it vice versa