Lookup and summing comparison

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Graham Haughs - 31 Oct 2006 20:37 GMT

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

Reply to this Message

Toppers - 31 Oct 2006 21:08 GMT

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

> 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
[quoted text clipped - 35 lines]
> Graham Haughs
> Turriff, Scotland

Reply to this Message

Toppers - 31 Oct 2006 21:23 GMT

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,
>
[quoted text clipped - 72 lines]
> > Graham Haughs
> > Turriff, Scotland

Reply to this Message

Graham Haughs - 31 Oct 2006 21:30 GMT

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

> Graham,
>
[quoted text clipped - 72 lines]
>> Graham Haughs
>> Turriff, Scotland

Reply to this Message

Toppers - 31 Oct 2006 21:46 GMT

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)

> 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
[quoted text clipped - 80 lines]
> >> Graham Haughs
> >> Turriff, Scotland

Reply to this Message

Harlan Grove - 31 Oct 2006 22:07 GMT

Graham Haughs wrote...
...

>(Sheet A) as an example there could be two columns as below
>Column A Column B
[quoted text clipped - 13 lines]
>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
[quoted text clipped - 3 lines]
> 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

Reply to this Message

Harlan Grove - 31 Oct 2006 22:08 GMT

Graham Haughs wrote...
...

>(Sheet A) as an example there could be two columns as below
>Column A Column B
[quoted text clipped - 13 lines]
>Field 3 67
>Field 4 54

...

Reply to this Message

Graham Haughs - 31 Oct 2006 22:21 GMT

> Graham Haughs wrote...
> ...
[quoted text clipped - 37 lines]
> 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

Reply to this Message

Toppers - 31 Oct 2006 22:25 GMT

.. 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.

> Graham Haughs wrote...
> ....
[quoted text clipped - 37 lines]
> until C3, which is the last instance of Field 1 in column A of your
> first table.

Reply to this Message