Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for multiple cells with a row for matching criteria, then sum up certain column
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search worksheets in same book to return value matching criteria? | Excel Worksheet Functions | |||
MAX value matching multiple criteria | Excel Worksheet Functions | |||
return multiple records matching multiple criteria | Excel Worksheet Functions | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
Sum column if multiple criteria are met in adjacent cells | Excel Worksheet Functions |