Subtotalling Similar Rows (VBA?)

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
119
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

Well-known Member
Joined
Feb 6, 2012
Messages
951
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

Board Regular
Joined
Aug 20, 2018
Messages
119
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

Well-known Member
Joined
Feb 6, 2012
Messages
951
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

Board Regular
Joined
Aug 20, 2018
Messages
119
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

Board Regular
Joined
Aug 20, 2018
Messages
119
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
 
Last edited:

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
951
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

Board Regular
Joined
Aug 20, 2018
Messages
119
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
 
Last edited:

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
951
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,""))
 

Forum statistics

Threads
1,082,243
Messages
5,363,972
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top