Lookup and summing comparison

[Graham Haughs]

I am struggling with finding a simple solution to this and I am not sure
if it is a function option or a programming option. In one worksheet
(Sheet A) as an example there could be two columns as below
Column A Column B
Field 1 12
Field 1 55
Field 2 24
Field 2 43
Field 2 18
Field 3 16
Field 4 21
Field 4 41

In another worksheet (Sheet B) there are also two columns as below
Column A Column B
Field 1 28
Field 2 65
Field 3 67
Field 4 54

There are obviously more columns etc but the concept is the same.
Ideally what I would like to do is in Sheet A, in column C, at the point
where there is the last entry of a Field in column A, a warning flag of
any kind to appear if the totals of Column B are greater than the value
in Sheet B of Column B for the same field. eg in Sheet A, for Field 1,
the total is 77, while the respective figure in Sheet B is 28 for the
same field. Thus the flag of "Too High!" or some equivalent should
appear in cell C2 of Sheet A.
It is further complicated by varying number of entries in Sheet A for
the Field 1, 2 etc.
I can do it using VLookup and Sumif etc but it is long and convoluted, I
just feel there is a simpler solution. I have put it in the Functios
group but if you feel a programming solution is more effective then I
would value any pointers.
Thanks for taking the time to read through this garbled explanation.

Kind Regards
Graham Haughs
Turriff, Scotland

 

[Guest]

Graham,

Try this: assumes data starts column 2 on both sheets

Sub HiLow()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long, r As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
r = 2 '<=== start row of data
Do
n = Application.CountIf(Range("A:A"), .Cells(r, "A"))
Sum1 = Application.SumIf(Range("A:A"), .Cells(r, "A"), Range("B:B"))
sum2 = Application.VLookup(.Cells(r, "A"), ws2.Range("A:B"), 2, 0)
If IsError(sum2) Then
.Cells(r + n - 1, "C") = "No match"
Else
If Sum1 > sum2 Then
.Cells(r + n - 1, "C") = "Too high"
Else
If Sum1 < sum2 Then
.Cells(r + n - 1, "C") = "Too Low"
End If
End If
End If
r = r + n
Loop Until r > lastrow
End With
End Sub

HTH

 

[Guest]

Couple of corrections to ensure correct data is selected [.Range (...)
instead of just Range(..)]

n = Application.CountIf(.Range("A:A"), .Cells(r, "A"))
Sum1 = Application.SumIf(.Range("A:A"), .Cells(r, "A"), .Range("B:B"))

 

[Graham Haughs]

Thanks for feedback. Seems to be problematical in that a "No Match" is
appearing in every row in column C of Sheet 1. It does not appear to
take the totals from that sheet and compare with Sheet 2 entries, it is
comparng every entry I think. I will try a few variations. Thanks for
your help.

graham

 

[Guest]

Graham,
i tested it on your posted data and it worked OK. Assumption
is that data in Sheet1 is sorted by "Field" as indicated in your posting.

"Sum1" is total from sheet1 which is compared with "Sum2" (Vlookup) from
Sheet2.

If you want, post sample to me

toppers<at>nospamjohntopley.fsnet.co.uk (remove nospam)

 

[Harlan Grove]

Graham Haughs wrote...
....

(Sheet A) as an example there could be two columns as below
Column A Column B
Field 1 12
Field 1 55
Field 2 24
Field 2 43
Field 2 18
Field 3 16
Field 4 21
Field 4 41

In another worksheet (Sheet B) there are also two columns as below
Column A Column B
Field 1 28
Field 2 65
Field 3 67
Field 4 54 ....
Ideally what I would like to do is in Sheet A, in column C, at the point
where there is the last entry of a Field in column A, a warning flag of
any kind to appear if the totals of Column B are greater than the value
in Sheet B of Column B for the same field. eg in Sheet A, for Field 1,
the total is 77, while the respective figure in Sheet B is 28 for the
same field. Thus the flag of "Too High!" or some equivalent should
appear in cell C2 of Sheet A.

....

If the first table were sorted on its column A and the second table
were named Tbl and also sorted on its first column, then if the topmost
record were in row 2, the C2 formula could be

C2:
=IF(AND($A2<>$A3,SUMIF($A$2:$A2,$A2,B$2:B2)>VLOOKUP($A2,Tbl,COLUMN(B2)),
"Too High!","")

Is this really too cumbersome? It's a LOT more flexible and robust than
any macro alternative. Note: this won't produce a result other than ""
until C3, th

 

[Harlan Grove]

Graham Haughs wrote...
....

(Sheet A) as an example there could be two columns as below
Column A Column B
Field 1 12
Field 1 55
Field 2 24
Field 2 43
Field 2 18
Field 3 16
Field 4 21
Field 4 41

In another worksheet (Sheet B) there are also two columns as below
Column A Column B
Field 1 28
Field 2 65
Field 3 67
Field 4 54 ....
Ideally what I would like to do is in Sheet A, in column C, at the point
where there is the last entry of a Field in column A, a warning flag of
any kind to appear if the totals of Column B are greater than the value
in Sheet B of Column B for the same field. eg in Sheet A, for Field 1,
the total is 77, while the respective figure in Sheet B is 28 for the
same field. Thus the flag of "Too High!" or some equivalent should
appear in cell C2 of Sheet A.

....

If the first table were sorted on its column A and the second table
were named Tbl and also sorted on its first column, then if the topmost
record were in row 2, the C2 formula could be

C2:
=IF(AND($A2<>$A3,SUMIF($A$2:$A2,$A2,B$2:B2)>VLOOKUP($A2,Tbl,COLUMN(B2)),
"Too High!","")

Is this really too cumbersome? It's a LOT more flexible and robust than
any macro alternative. Note: this won't produce a result other than ""
until C3, which is the last instance of Field 1 in column A of your
first table.

 

[Graham Haughs]

Graham Haughs wrote...
...
...

If the first table were sorted on its column A and the second table
were named Tbl and also sorted on its first column, then if the topmost
record were in row 2, the C2 formula could be

C2:
=IF(AND($A2<>$A3,SUMIF($A$2:$A2,$A2,B$2:B2)>VLOOKUP($A2,Tbl,COLUMN(B2)),
"Too High!","")

Is this really too cumbersome? It's a LOT more flexible and robust than
any macro alternative. Note: this won't produce a result other than ""
until C3, which is the last instance of Field 1 in column A of your
first table.

No Harlan I really don't think that this is too cumbersome, only my
efforts with Sumif and VLookup had failed to yield a composite of the
purity which you have created. It was my ideal to have a single entry
like this. I still value the macro construction, which I have now got to
work, as it offers another dimension to a problem and it is great to
have these options.
I am indebted to you.

Graham

 

[Guest]

.. but it doesn't give a "Too Low" message or "error" if no match found ...
although the OP didn't specify this as a requirement.