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]

Please ignore previous which was only example and treat new as actual
Yes column M, R, T to X having formulas
Need formula or code in new sheet please.
Apologies again for unclear question.

 

[Peter_SSs]

What about these questions that I also asked?

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

 

[Zubair]

What about these questions that I also asked?

no new sheet not exist.

 

[Peter_SSs]

new sheet not exist.

OK, try this with a copy of your workbook.

I have assumed that the original data is on a sheet called "Data". Change that in the code where indicated.

Sub Extract_Negatives()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
  Sheets("Data").Copy After:=Sheets("Data") '<- Edit sheet name as required
  With Sheets(Sheets("Data").Index + 1)
    a = .Range("X2", .Range("X" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      If a(i, 1) >= 0 Then
        b(i, 1) = 1
        k = k + 1
      End If
    Next i
    If k > 0 Then
      Application.ScreenUpdating = False
      With .Range("A2:Y2").Resize(UBound(a))
        .Columns(25).Value = b
        .Sort Key1:=.Columns(25), Order1:=xlAscending, Header:=xlNo
        .Resize(k).EntireRow.Delete
      End With
      Application.ScreenUpdating = True
    End If
  End With
End Sub

 

[Zubair]

OK, try this with a copy of your workbook.

I have assumed that the original data is on a sheet called "Data". Change that in the code where indicated.

Sub Extract_Negatives()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  Sheets("Data").Copy After:=Sheets("Data") '<- Edit sheet name as required
  With Sheets(Sheets("Data").Index + 1)
    a = .Range("X2", .Range("X" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      If a(i, 1) >= 0 Then
        b(i, 1) = 1
        k = k + 1
      End If
    Next i
    If k > 0 Then
      Application.ScreenUpdating = False
      With .Range("A2:Y2").Resize(UBound(a))
        .Columns(25).Value = b
        .Sort Key1:=.Columns(25), Order1:=xlAscending, Header:=xlNo
        .Resize(k).EntireRow.Delete
      End With
      Application.ScreenUpdating = True
    End If
  End With
End Sub

Many thanks but can you share steps to perform this I was expecting formula I am very weak in VBA

 

[Peter_SSs]

can you share steps to perform this

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2 & edit the sheet name (twice) in the line indicated.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

 

[Zubair]

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2 & edit the sheet name (twice) in the line indicated.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Peter - thanks its working fine Excellent !, it will be more convenient if same data may come through formula instead of VBA if possible, As I have some dashboards so I wanted to link negative figures from the data.

 

[Peter_SSs]

it will be more convenient if same data may come through formula

With 24 columns and nearly 30,000 rows I suspect a formula solution may make your workbook very sluggish. However, if you want to try please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Zubair]

Account details updated thanks, Please share formula 30k rows contained 7 years data and normal view is for current and last year so if will be good if selection of year may include in the formula.

 

[Peter_SSs]

You haven't given us more than one row of meaningful sample data so my sample is just random numbers but the logic of using column X (GM CPL) and column J (Year) to identify the rows required remains the same.

I strongly suggest that you try this on something much smaller than your 30,000 rows to start with.

Here is my sample data.

Zubair.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	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
2	-9	2	-5	0	0	7	6	5	-4	2019	-1	-6	3	-9	10	7	-5	-7	-1	10	-1	2	-1	1
3	2	8	10	9	-1	9	5	3	-6	2021	-1	-1	-8	-1	-1	1	2	-2	-1	2	6	2	-2	-5
4	-1	0	-2	-2	7	-2	-1	-4	-6	2020	-6	-4	-1	-4	9	0	-8	5	0	4	0	4	-2	-6
5	9	4	10	5	6	5	7	7	-3	2021	-4	-3	8	-3	-9	8	-4	-7	-1	5	-2	5	8	-2
6	-2	-3	10	10	1	10	-5	-7	-9	2019	0	3	-7	3	3	0	0	10	3	4	-5	4	7	10
7	-5	10	-6	9	8	9	-9	-1	3	2019	9	-2	4	-2	5	2	0	10	8	10	2	10	5	-9
8	-6	-6	-2	1	9	1	-3	-2	-1	2021	4	10	2	0	9	-4	-8	1	-5	-3	10	1	5	1
9	0	6	4	1	7	-4	-1	-3	3	2019	3	-1	7	-3	4	1	8	3	-7	3	0	-8	-3	9
10	9	3	-5	9	1	1	5	-6	1	2021	2	10	3	-3	-1	-8	-7	-4	10	4	-9	1	7	-7
11	4	4	-6	-3	-8	7	-8	2	-3	2021	-6	9	3	3	2	5	8	-5	-3	-4	5	-8	-1	0
12	0	-5	10	-6	8	0	3	-9	-1	2019	5	7	6	-9	-3	3	0	-3	0	-1	7	9	-3	-4
13	0	1	-7	-2	1	-8	-4	-3	6	2021	-7	-8	-5	3	-7	4	-4	-5	-3	3	-3	-9	0	-1
14	-7	3	-1	4	10	8	-6	7	-2	2019	3	-4	-1	-7	-6	0	6	10	8	-6	0	4	-3	-9
15	8	-1	10	-6	7	5	-8	-9	3	2019	7	8	-5	5	3	9	2	8	-8	-5	7	1	-9	7
Data

.. and formula Results sheet.

Zubair.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	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
2	2	8	10	9	-1	9	5	3	-6	2021	-1	-1	-8	-1	-1	1	2	-2	-1	2	6	2	-2	-5
3	-1	0	-2	-2	7	-2	-1	-4	-6	2020	-6	-4	-1	-4	9	0	-8	5	0	4	0	4	-2	-6
4	9	4	10	5	6	5	7	7	-3	2021	-4	-3	8	-3	-9	8	-4	-7	-1	5	-2	5	8	-2
5	9	3	-5	9	1	1	5	-6	1	2021	2	10	3	-3	-1	-8	-7	-4	10	4	-9	1	7	-7
6	0	1	-7	-2	1	-8	-4	-3	6	2021	-7	-8	-5	3	-7	4	-4	-5	-3	3	-3	-9	0	-1
7
8
Results

Cell Formulas
Range	Formula
A2:X8	A2	=IFERROR(INDEX(Data!A:A,AGGREGATE(15,6,ROW(Data!A$2:A$30000)/((Data!$X$2:$X$30000<0)*((Data!$J$2:$J$30000=YEAR(TODAY()))+(Data!$J$2:$J$30000=YEAR(TODAY())-1))),ROWS(A$2:A2))),"")