Over 200,000 lines and need a macro to calculate matching PO valuse

[Karen, Sr. Analyst]

Hi,

I've tried an "IF statement" to solve my problem but that resulted in taking too much of the resources in our servers and taking way too long.
My problem:

I have over 200,000 lines of data. I have a PO number in column H which may or may not repeat in that column all the way down. I need to total the dollar amounts on that same row for each of those matching PO numbers. I haven't been able to figure out how to write the macro and I'm not sure if it will solve the problem of eating up resources. It would be great if I can have the macro total the dollars for the matching POs and if the total equals zero, delete those lines.

Thanks for any help.

[TMS]

Can't you just use SUMIF or SUMIFS.


Regards, TMS

[Karen, Sr. Analyst]

That is what I did. Clogged up the server and took forever. Acted as those it wanted to calculate forever. Even if I turned of calculation and manually calculated.

[Karen, Sr. Analyst]

I can spell, sorry... Acted as though, turned off the calculation

[TMS]

Can you post a sample? Obviously, not the full 200,000 lines

Or maybe a pivot table? Or Advanced filter?


Regards, TMS

[Karen, Sr. Analyst]

I've attached a picture of the spreadsheet. Best I could do.

My sum if statement =IF(SUMIF(H:H,H2,K:K)=0,"remove","keep") Once they were labeled I was going to delete the rows that said remove. If even tried limiting the rows by changing the H:H to H2:H206000, and the same in column K. That didn't return the correct results at all.

[oeldere]

This is an Excel forum, so it would be helpfull is you post your Excel file, without confidentional information.

Obviously, not the full 200,000 lines

[Karen, Sr. Analyst]

Actually it was column J:J. Anyway... the reference number is the PO number. There are several hundred different PO numbers down the column. I need to total all of the same PO numbers with their corresponding dollar amounts in column J.

Thanks again

[Karen, Sr. Analyst]

Maybe this will be more helpful. see attached

[mike7952]

If you can upload a small sample with ALL HEADERS and some data maybe 4 to 5 ref numbers with 1 or 2 of them needing deletedd I cancode you up something pretty quick.

[Karen, Sr. Analyst]

okay, just a second

[oeldere]

It can be done with pivot table.

see the attached file.

[mike7952]

So do you want 1 line per Reference Number? Do you want all the Columns In the output?

[Karen, Sr. Analyst]

Sorry, took me a few minutes. Had to manipulate the data just to get you a general idea of what needs to match. They won't total to zero but they will sum to a number.

Thanks

[Karen, Sr. Analyst]

One line per reference number, with the total. All of the columns are not necessary. Just the Reference, Text, dollar amount, voucher and date are helpful to me.

Thanks so much

[TMS]

One option:

use Dynamic Named Ranges

Amount =Sheet1!$J$2:INDEX(Sheet1!$J:$J,COUNTA(Sheet1!$J:$J))
Reference_Number =Sheet1!$H$2:INDEX(Sheet1!$H:$H,COUNTA(Sheet1!$H:$H))
Formula: =IF(SUMIF(Reference_Number,H2,Amount)=0,"Remove","Keep")

Second, better option, use a Pivot Table. See example.

Regards, TMS

[Karen, Sr. Analyst]

thanks! Hadn't thought about the ranges!

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[Karen, Sr. Analyst]

Mike, leaving work now, but will check back tomorrow. No pressure. I appreciate anything.

thanks

[mike7952]

Here's what I got before you replied back. test on a copy of your workbook. Be sure the sheet1 has the data and you have a 2nd sheet named sheet2. Output will goto sheet2. Let me know how it does for you.

Please Login or Register  to view this content.

And this one list just the 4 columns

Please Login or Register  to view this content.

[Karen, Sr. Analyst]

Mike,

Thank you SOOOO much. Both worked perfectly. I will use the second one you set up for me. Four columns of data popped up on the second tab just as I wanted them. I'm not savy enough to do this. I REALLY appreciate your help!

THANKS AGAIN,

Karen

[oeldere]

And my solution in #12.