Subtotalling Similar Rows (VBA?)

[steveh8204]

I've made a spreadsheet which compares values from two sheets (the values in Column I and P in the image link below) and then calculates the differences in Row Q, all simple stuff so far.


The problem I've got is some common values that I use to draw the info (Column F - highlighted) have values in multiple rows due to there begin an entry for each due date (Column L) so the Variance (Column Q -highlighted in Red) works them all out seperately which is incorrect.


Is there a way to show the variance in row Q to be the difference between the PO Total in Column I (the sum of cells I31-I33) and the PO total (repeated in cells P31-P33)?


Ideally when the PO information is similar as in this example Cells P31, P32 and Q31, Q32 would be blank, P33 would be 908.700 and Q33 would be 31,500.


Some of the units are different but the code I have that currently works out the variance converts these.


I know its an ask but I've spent hours trying stuff without any real progress. Thanks in advance for any help.

https://drive.google.com/open?id=1psqKBmGuZLtTYBHFM9ZDHi_6eI2m2yz7

 

[mrshl9898]

So you want 31500 in Q33 only?

Try: =IF(P31<1,"",IF(COUNTIFS(B:B,B31,I:I,">"&0)=COUNTIF(B$1:B31,B31),SUMIF(B:B,B31,I:I)-P31,"")) in Q31 copied down

 

[steveh8204]

Hi, thanks sorry I've had a mare in work and couldn't log on at home.



That seems to be exactly what I need except for a couple of instances where it hasn't done what it should.


In the image below you can see the value in Q3 shouldn't be there as there is no variance but its the value from P2.



Theres also no variance near the bottom (highlighted) when there should be one (3,000).



Stranges everywhee else t's wored it out right.


https://drive.google.com/file/d/1matsScNoD0Mf4I1RiUlNK_rmohw8t_1K/view?usp=sharing

 

[mrshl9898]

So maybe in Q2:

=IF(P2<1,"",IF(COUNTIFS(B:B,B2,I:I,">"&0)=COUNTIF(B$1:B2,B2),SUMIF(B:B,B2,I:I)-SUMIF(B:B,B2,P:P),""))

That will fix the problem on rows 2 and 3.

I reckon you must have another 3000 for K135202085 somewhere, explaining that 0 (as I am getting -3000 with just the data in that screenshot)

 

[steveh8204]

https://drive.google.com/open?id=1-ZlVi6HxvqbmJ6ujxAADrK5uWfOX553c

Sorry its took a while to respond but I've been away the weekend and been off worrk so not been able to test this.

I've just tested it now and it seems to be fine except for one instance which may be because there are three similar rows to subtotal.


In the example above you can see it has no values in row q even though there is a variance (total is 490,200 and the PO quantity is 458,700)

 

[steveh8204]

Sorry its took so long to reply again but it's took me a while to find the time to trouble shoot this one. It appears to work fine but a couple of random rows aren't calculating it the same as the others. I've double checked the formulas are all similar so I'm not sure where the problem lies.

In the following image the amount circled on the far right has taken the value from the one circled above. If I delete the row highlighted yellow the amount on the far right circled disappears (which means its worked it out correct as the 92,750 total equals the 81,500+11,250).

The formula in M23 (the last column) is "=IF(P23<1,"",IF(COUNTIFS(B:B,B23,I:I,">"&0)=COUNTIF(B$1:B23,B23),SUMIF(B:B,B23,I:I)-SUMIF(B:B,B23,P:P),""))" so I don't understand why it draws the 412,500. Could it be because Colum H is empty?

https://drive.google.com/file/d/1pCnuJ5nbaedriM2hHJEGHNLEEavRnlzq/view?usp=sharing

 

[mrshl9898]

Can't quite follow this one, can you please upload another screenshot, filter column B by the reference number in question, and include the row numbers and column names in the picture?

 

[steveh8204]

Can't quite follow this one, can you please upload another screenshot, filter column B by the reference number in question, and include the row numbers and column names in the picture?

The 2 images attached are the output. The ones in Green are where it has worked correctly and the ones in Red are where it hasn't. The formula in row Q is the following (the *1000 near the end is to convert the units as Column P units are 1.000 for a thousand) copied all the way down:

=IF(P3<1,"",IF(COUNTIFS(B:B,B3,I:I,">"&0)=COUNTIF(B$1:B3,B3),SUMIF(B:B,B3,I:I)-P3*1000,""))

https://drive.google.com/file/d/188Y3bwsTww02ofJsaEHXGBs4wZnMMlIi/view?usp=sharing

https://drive.google.com/file/d/1talqrTL2mkO2a_mdIv8ciHGw2E2PPUaF/view?usp=sharing

 

[mrshl9898]

Thanks for that info.

I see there's a bit of a misunderstanding, try this, it should get rid of most of the issues at least.

If there are still problems please add in column R, with what you want the result to be.

=IF(B3="","",IF(COUNTIF(B:B,B3)=COUNTIF(B$1:B3,B3),SUMIF(B:B,B3,I:I)-SUMIF(B:B,B3,P:P)*1000,""))

 

[steveh8204]

Right I've tried this formula and I'm still getting some that work and some that don't which is very strange. i thought maybe some further down were throwing it out but then some below that work again.

https://drive.google.com/file/d/1LAxLCLrDMlpqTt4tLG-q5SIfHi73IERn/view?usp=sharing

The subtotalling in Q needs to be based on similar values in F (and adding the values in I).

Thanks