Filtering/Sorting Data

[Pumper]

Hi All, I have a list of data and would like to return only rows that are not 0 in cell C and adjust the order of one of the columns

ie column C goes to column B once filtered and column C would be 0 if that row is not blank

Any help would be much appreciated

 

[Peter_SSs]

You haven't told us whether you are looking for a formula approach, vba or perhaps Power Query?

You also don't show us what version(s) of Excel you are using. 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’)

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

One more thing, I do not know what you mean by this.

column C would be 0 if that row is not blank

 

[maabadi]

Try this Formula:

Book1
	A	B	C	D	E
1	Fund	Target	Trade
2	ABC	1	0
3	DEF	2	5
4	GHI	3	0
5	JKL	4	-14
6
7
8	Fund	Trade	should have zero if not Blank
9	DEF	5	0
10	JKL	-14	0
11
Sheet1

Cell Formulas
Range	Formula
B9:B10	B9	=VLOOKUP(A9,$A$2:$C$5,3,FALSE)
A9:A11	A9	=IFERROR(INDEX($A$2:$A$5,SMALL(IF($C$2:$C$5<>0,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$8:A8))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

You also don't show us what version(s) of Excel you are using.

If you have Excel 365 you could try a formula like this which only needs to be entered in a single cell and the other results will automatically 'spill' across and down to the other required cells.

21 02 18.xlsm
	A	B	C	D
1	Fund	Target	Trade
2	ABC	1	0
3	DEF	2	5
4	GHI	3	0
5	JKL	4	-14
6
7
8	Fund	Trade
9	DEF	5	0
10	JKL	-14	0
11
Extract and Filter

Cell Formulas
Range	Formula
A9:C10	A9	=FILTER(CHOOSE({1,2,3},A2:A5,C2:C5,D2:D5),C2:C5<>0,"")
Dynamic array formulas.

 

[Pumper]

Sorry about that, have updated my profile, the version is 2016.

Was hoping for a VBA Macro as I need to run daily.

@ Peter_SSs - Column C should equal 0 if A and B are populated as the result gets loaded into a system that requires a zero in the last cell of a result.

I will try those ideas above, thanks very much for your time

 

[Peter_SSs]

Sorry about that, have updated my profile, the version is 2016.

Thanks for that.

Was hoping for a VBA Macro

Good idea to state that in your opening post.
Try this with a copy of your data. I have assumed headings in row 1 with actual data starting in row 2. Code puts results in columns E:G.

Sub ExtractAndFilter()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
  a = Range("A2", Range("C" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 3)
  For i = 1 To UBound(a)
    If a(i, 3) <> 0 Then
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(i, 3)
      b(k, 3) = 0
    End If
  Next i
  If k > 0 Then
    With Range("E2:G2").Resize(k)
      .Value = b
      .Rows(0).Value = Array("Fund", "Trade", "???")
      .EntireColumn.AutoFit
    End With
  End If
End Sub

My sample data and results

21 02 18.xlsm
	A	B	C	D	E	F	G
1	Fund	Target	Trade		Fund	Trade	???
2	ABC	1	0		DEF	5	0
3	DEF	2	5		JKL	-14	0
4	GHI	3	0
5	JKL	4	-14
6
Extract and Filter (2)

 

[Pumper]

Brilliant thanks very much Peter_SSs works perfectly.

I will investigate more on the use of the UBound function

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.