LinkBack URL
About LinkBacks
I have a list of about 4,000 contacts each with about 2 or 3 products with us. Each product creates a new contact row.
I would like to merge each row with the same contact into one row. I have attached an example
Thank you so much for your help
I have a list of about 4,000 contacts each with about 2 or 3 products with us. Each product creates a new contact row.
I would like to merge each row with the same contact into one row. I have attached an example
Thank you so much for your help
Czkeit,
Welcome to the forum!
Give the below macro a try.
How to use a macro:
- Make a copy of the workbook the macro will be run on
- Always run new code on a workbook copy, just in case the code doesn't run smoothly
- This is especially true of any code that deletes anything
- In the copied workbook, press ALT+F11 to open the Visual Basic Editor
- Insert | Module
- Copy the provided code and paste into the module
- Close the Visual Basic Editor
- In Excel, press ALT+F8 to bring up the list of available macros to run
- Double-click the desired macro (I named this one tgr)
Please Login or Register to view this content.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Wow tiger that is really cool! There is no information lost in that?
In the example workbook provided there was no information lost. Like I said though, macros should be run on a copy of the original workbook; then compare the results to the original to ensure that both nothing was lost and the macro performed as desired.
I dont know why but it is giving me an error when I try to run the macro the error says
"run-time error '1004':
no cells were found"
and then it gives me an option to "debug" but I dont know what that is.
I tested it on the example that I gave you that came from my origional list and it worked perfect on that
Give this version a try:
Please Login or Register to view this content.
I’m not sure what that one did, I didn't get an error this time but it is not merging the rows that I need merged and it is deleting what seems to be random rows. Is there a way to make the macro merge the rows based on the column title: "custid". So if there are three rows with the custid as "4" those three rows would merge into one and delete the other two? I’m sorry this has been so complicated I truly appreciate your help
Adjusted code, give this a try:
Please Login or Register to view this content.
This one seems to be merging some of the rows correctly, but not all only about a third of them and deleting other essential rows without merging first
That code works on the example workbook. To correct issues I'd have to see where its going wrong
What do you need from me to do that?
A new example workbook that contains data that the code doesn't run correctly on.
Im not sure what I did wrong last time but it is now working you have been so helpful thank you so much
Well I miss spoke It is only working about 50% of the time. It Merges several of the rows together but just deletes others without Merging. Is there a way I can privately share teh workbook with you?
I would recommend scrubbing sensitive data and uploading a sample workbook containing data the macro doesn't run correctly on instead.
From the forum rules:
4. Don't Private Message or email questions to moderators or other members. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members
OK here is a scrubbed version of myScrubed Data.xlsx entire list.
Note that after the Macro is ran CustID #'s 13, 187, 2374, and 2872 have missing policies. These are just random ones that I pulled out. Most of the rows were merged properly I can’t see any pattern to these errors.
Czkeit,
Updated code, verified it works for the lines specified, let me know if you find further bugs:
Please Login or Register to view this content.
Everything is working great I dont see any other bugs. This is very cool I wish I knew how to write Macros. I have a question though, the data that I gave you to work with was based on a template where W through AL were the columns that needed to be merged. Sometimes the data that I get is different would the macro apply if it was W through AC or even V through AL? if not is there an easy way to change the macro?
Czkeit,
I updated the macro so that it can be easily changed as needed:
CstIDCol is the column letter containing CustID's.
StartCol is the column letter that the macro should start looking at.
FinalCol is the column letter that the macro should stop looking at.
Change those three as needed, the macro will adjust as necessary.
Please Login or Register to view this content.
I have another thing that I would like to do to the data (the last thing) but im not sure if it is something that sould be added to this macro or a different one entirely when I get the data it looks like this:
All of the policies are in one column, then I mannually seperate them into there own columns, auto , home, DF ect. Then change each cell to say "True" is this something a macro could automate and could it be done with the same macro you gave me?
Czkeit,
Updated code to include request. Give it a try:
Please Login or Register to view this content.
Hi
Like the previous enquirer, I am at a loss when it comes to writing macros, but I too have a worksheet (portion attached) where I would like to combine the rows where the number in the first column is duplicated - for example customer 288 would have one row, with entries in the input, output, static and offset columns.
Can you help?
rosierat,
Welcome to the Forum.
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
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]
Sorry, I had not picked that up. I will repostOriginally Posted by arlu1201
This is brilliant
I have table that has the product data row after row and need to join row cells together
I have used the tool www.asap-utilities.com which is excellent but the process is manual
Can you assist?
eyecatchers,
Please check post 23.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks