Hi. I have a spreadsheet with over 40K rows, and thousands of duplicates. I'm new to running macros, and want to be able to take the rows (if they are identical), total them, and combine into one row and remove all of the duplicates. Example attached. Hopefully the attached (Tab 1 being what it currently looks like - just with some more information that needs to be carried over) and Tab 2 what I would like it to look like. Thank you!
Why are 1 - PJB - Store 3 and 1 - PJB - Store 4 not to be aggregated in your example?
If you like my contribution click the star icon!
Try this code on a sample of your data -
Copy the Excel VBA code
Please Login or Register to view this content.
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste
To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
If that was a "mistake" on your end, create a new module in the VBE editor window and copy & paste the below code into that module
Please Login or Register to view this content.
Hi, all rows are to be consolidated where they match...I was just showing a few examples and didn't continue on...it repeats for about 40,000 rows. Thank you
Try the codes we have provided you and let us know if it works.
It is running...looks like it may take a few hoursIs that normal for such a long worksheet? Also - does it matter that I have many more columns than just ABCD and E? Or will the macro you provided only be if ABC are commen, it totals D and E and removes duplicates? PS Thank you!!! I'll le tyou know if it works.
It shouldnt take a few hours, but maybe a few minutes. Do you have any formulae active in your sheet, then it will take longer. You can add this line at the top of your code Application.Calculation = xlCalculationManual after the other application statement. Add this at the end after the other application statement - Application.Calculation =xlCalculationAutomatic
You can have other columns but do they need to be checked like how A, B and C are being checked?
- which solution are you running?
- mine will only aggregate the values in column D, since in your example column E was not aggregated
- whether or not any columns exist past E is not relevant for my code
- should not take that long as both solutions freeze the display for performance reasons
I realized I had a bunch of hidden columns, therefore making the ones that your code was likely looking up and aggregrating or not aggregating incorrect. Sorry to be a pain, but I have uploaded a proper spreadsheet to show the exact number columns. Which ones need to count if they are the same and combine (they are highlighted), and which ones are sums VS formulas. Does this make it easier? Thank you for your help!!! See attached.
Try the attached one
Try
Please Login or Register to view this content.
Here is my version
Please Login or Register to view this content.
I tried the one from OlieB about an hour and a half ago and it is still 'working'. hhmmmm Hopefully it works, thanks for all your help. I'm just surprised it's taking a while. I'll let you know how it goes.
It worked!!! I haven't gone through to verify the data yet, but it worked! thank you so much!! I will move this to resolved tomorrow when I search through and check the numbers. Wow-amazing!
I do not know which code is working, or not working for you. There are 3 codes and do not know which code you are referring to.
I used the one from Ollie at 10:57am. Worked great! THank you
Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.
In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.
Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.
Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
I appreciate the comments you added to your code.
It help make sense of what you and arlu1201 were doing.
Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks