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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for multiple cells with a row for matching criteria, then s
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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for multiple cells with a row for matching criteria, then s
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 wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for multiple cells with a row for matching criteria, th
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 "drdavidge" wrote: 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 wrote: 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" wrote: 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. |
Reply |
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 |