Macro to search one column and then add values from a second column

[nliv007]

Hi all, and thanks in advance for any help.

I am trying to set up a macro that will sort and sum the values from a large spreadsheet based on the adjacent cell value.

I can't post the spreadsheet due to liability issues.

Basically, there are over 21 thousand row entries with corresponding event numbers. Many of the rows are from the same event. The event numbers are in column A. In column B is a dollar value for the row. I would like to find a way to put a formula in each row that would search the A column for any number that is identical to that row's event number and add the corresponding B values for all matching rows. Hope that makes sense.

Thanks again.

[AlvaroSiza]

I know you said you cannot post due to liability, but even posting a small sample, say 20 rows, with non-identifiable data would be beneficial and likely lead to a much faster response.

Edit: So you want the sum of the dollar amount in Column B corresponding to each unique event number? A non-programmatic approach would be as follows.

1. Highlight the entire range of Event No.'s in Column A.
2. From the Data tab, Sort & Filter Group, Click Advanced.
3. Enable Copy To Another Location; Specify a location where you would like these to go (I chose G1). Click Ok.
4. Now offset one column to the right of this new, unique range, utilize the SUMIF function.
5. Assuming Column G has my unique event no's, Column A has the total range of Event No.'s spanning 20 rows, and Column B has the dollar amounts also spanning 20 rows:

Please Login or Register  to view this content.

6. Copy and paste down for the entirety of your unique event no. range.

[nliv007]

I understand. I threw this together and it accurately represents my issue.

Basically, a program in cell D2 would return a value of $14 by searching the A column for anything with a number matching A2 (inclusive) and adding the corresponding values for the C column. Thus, it would add C2, C9 and C14 together for a total dollar value of event 1. Dragging the formula down would work the same making D9 and D14 also $14, and returning the appropriate total values for the other events.

Does this make sense?

[nliv007]

I know you said you cannot post due to liability, but even posting a small sample, say 20 rows, with non-identifiable data would be beneficial and likely lead to a much faster response.

Edit: So you want the sum of the dollar amount in Column B corresponding to each unique event number? A non-programmatic approach would be as follows.

1. Highlight the entire range of Event No.'s in Column A.
2. From the Data tab, Sort & Filter Group, Click Advanced.
3. Enable Copy To Another Location; Specify a location where you would like these to go (I chose G1). Click Ok.
4. Now offset one column to the right of this new, unique range, utilize the SUMIF function.
5. Assuming Column G has my unique event no's, Column A has the total range of Event No.'s spanning 20 rows, and Column B has the dollar amounts also spanning 20 rows:

Please Login or Register  to view this content.

6. Copy and paste down for the entirety of your unique event no. range.

Thank you very much, works perfectly. Much appreciated.