VBA to return only values over £500 / under -£500 (including against multiple lines with the same reference)

[dids86]

Hi,

I'd like to be able to run a VBA that returns all transaction lines that are over £500 and under -£500 (highlighted in green below)

What makes it slightly trickier is some invoices will be across multiple rows (138XXX for example) and I'd need to include these as well

Any help would be greatly appreciated

Thanks

Tom

 

[saboh12617]

Hello,

I also read your previous post, but why do you want to use VBA ?

You could do this with simple formulas or Pivot tables or PowerQuery. It's more "Excel-friendly" & cross-platform.

 

[Joe4]

You can do this pretty easily without VBA using the new FILTER function, if you add a "helper" column to the end of your original data.
So, in cell E5, enter this formula and copy down to to get the total for each distinct invoice:

=SUMIFS(D:D,A:A,A4,C:C,C4)

Then, enter this FILTER formula to enter the records you want (I put this formula in cell H4):

=FILTER(A4:D10,ABS(E4:E10)>=500,"")

Here is what the results look like:

 

[dids86]

You can do this pretty easily without VBA using the new FILTER function, if you add a "helper" column to the end of your original data.
So, in cell E5, enter this formula and copy down to to get the total for each distinct invoice:

=SUMIFS(D:D,A:A,A4,C:C,C4)

Then, enter this FILTER formula to enter the records you want (I put this formula in cell H4):

=FILTER(A4:D10,ABS(E4:E10)>=500,"")

Here is what the results look like:
View attachment 123689

That's great thank you

 

[dids86]

Hello,

I also read your previous post, but why do you want to use VBA ?

You could do this with simple formulas or Pivot tables or PowerQuery. It's more "Excel-friendly" & cross-platform.

Thanks Saboh, I've decided to go down the PowerQuery Route which looks to have worked