Search for multiple cells with a row for matching criteria, then sum up certain column

[drdavidge]

Hey, I am trying to figure out how to do the following with a macro:

There are two sheets. The first one (sheet1) contains a long list of
rows with the following columns:

branch - expense - account_num - currency - amount

Each row is basically a "transaction" that is posted to a certain
branch, expense code, account number, currency, and amount. There is
usually more than one transaction for each set of branch, exp code,
acct num, and currency.

The second sheet (sheet2) has similiar columns:

branch - expense - partial_account_num - currency - total_amount

This sheet contains one row for each branch, exp code, acct num, and
currency combination. The account number is just a subset of the entire
account number - more on this below.

What I need to do is loop through sheet2 and total up all of the
corresponding amounts that match the same criteria in sheet1.



For example:

sheet1:
A - B - C - D - E
branch - expense - account_num - currency - amount
branch2 - 123 - a4567b - USD - 50.00
branch2 - 123 - a4567b - USD - 21.00
branch2 - 123 - a4567b - USD - 79.00
branch2 - 987 - n3455 - USD - 12.00
branch2 - 987 - n3455 - USD - 38.00

sheet2:
A - B - C - D - E
branch - expense - partial_account_num - currency - total_amount
branch2 - 123 - 4567 - USD - 150.00
branch2 - 987 - 3455 - USD - 50.00


I need to go through the rows in sheet2, find all the corresponding
rows in sheet1, sum the "amount" column in sheet 1 for all of the
matches, then compare it with the total_amount in sheet2. if there is a
difference, i need to flag those rows.


My thoughts were this:

- find the number of rows on each sheet
- do a for loop from 1 to number_of_rows on sheet 2
- within that for loop do another on sheet1
- within that for loop, do a bunch of if statements or while's ? to
compute total.
- compare total, maybe mark another cell in that row with a difference,
if any
- end loops


are there any better ways of doing this? im pretty lost as i am new to
excel programming.

thanks, i appreciate any suggestions you may have.

 

[Scott]

Hello,

What I would do is first try to figure out what is the most 'unique' item in
either list. I'll assume that it is account_num. What I suggest is to fill
an array with the smaller of the two sheets (from the sounds of it, sheet2)
and then search for the account_num on sheet one. When it finds it you
impose a couple of criteria, such as the expense number is the same and the
branch is the same. You will now have two 'identical' rows and you can
compare whether the amount is the same. You can do this using a simple if
statement. Then it's up to you to decide whether you want to copy any
different values and put it on a third summary sheet, or if you just want to,
say highlight the questionable rows in yellow.

If you need help with the code I have something I could quickly adapt to
give you an idea.

Cheers,

Scott

 

[drdavidge]

Thanks Scott. Your idea sounds good, but the problem is that it has to
sum up all of the rows in sheet1 that match the set of criteria, and
then compare that amount with the total on the single row in sheet2. i
basically need to figure a way to do subtotals on 4 different columns
of criteria per row, or some kind of nested for loop checking for
similiarities and sum them up.

 

[Scott]

exactly, if you do what I say and for example have an array a(1 to 100) then
each time you find a cell you can write it into the array. say a(1) = first
match, a(2) = second match and then add them up. Or what you can do right
away is say rowsum (a variable) is equal to your first match and then rowsum
= rowsum + second match. This would effectivly sum up all the matches giving
you your sub total.

To make things even fancier you could make it so you write down the cell
addresses of the matches so that if it needs to be flagged you can always go
back and do whatever you want to it.

cheers,

Scott