+ Reply to Thread
Results 1 to 18 of 18

Macro concatenating columns together, exceptions with duplicates, delete duplicates

  1. #1
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Macro concatenating columns together, exceptions with duplicates, delete duplicates

    Hi all
    I have a code that concatenates together information from bunch of columns. I need concatenate all this information together as there is a another report where I need to do match and index ( or simple vlookup) and have a nice overview to see where we are with a deliveries. Concatenation results should go to column X. This is what I have so far:
    Please Login or Register  to view this content.

    This code combines some data together in a way that after I do a vlookup to another report I can read and understand whats going on. So the combined information would look like this example:
    PO 1800786639; Item 10; Part L57952013200; Latest delivery date and time 04/01/2011, 11.30AM TRANSPORT; Manifests 10072; Delivery notes 81630937; Total quantity x1

    Now there are some rows that as per column W are duplicates. I need to combine these lines together into a one row, delete all duplicates and only keep the bottom line. This is where I struggle.

    For the first 5 parameters (PO, Item, Part, Last delivery date and time) I would like the macro to keep the values of the bottom duplicate. With Manifest ID I would like the macro to combine together all Manifest ID-s that are unique. With Delivery notes it should combine all the delivery notes together. Theoretically (and in practically) there will always be only unique delivery notes ( haven't seen singe duplicate delivery note so far). With Total quantity it should sum up the duplicate lines i.e there are are three rows with qyantites 4, 2, 3 so the total sum would be x9.



    Example line where rows have been added together:
    PO 1800824763; Item 10; Part KNA12A3M30701A04; Latest delivery date and time 20/02/2011, 11.30AM TRANSPORT; Manifests 11232, 11250; Delivery notes 81649755, 81649754, 81649753, 81649750, 81649749, 81649746, 81649744; Total quantity x7

    I have attached a xls where there are sheets "before" and "after" along with some decent comments. All the duplicate lines as per column W have been highlighted in red. After you have you run the macro, then sheet "before" should look identical with sheet "after".


    Any help would be greatly appreciated.
    Cheers
    Rain
    Attached Files Attached Files
    Last edited by rain4u; 06-24-2011 at 01:50 PM.

  2. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    See attached file where I modified the macro:
    Please Login or Register  to view this content.
    Regards,
    Antonio
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    Thank you antoka!!!
    I'm very impressed. You have done good job. I wish I would know how to do all this myself. I always study the codes to try to improve my knowledge. You got it almost perfect. Well the column X is spot on. It even brought out a mistake I that I had on sheet "after". Row 19 in my example should had all 3 delivery notes. In my manually created example it had only 1 delivery note. Human error. See, you can always trust code more than humans (provided that the code itself is correct).

    Two bugs.
    1) Currently the code keeps the top duplicate line and deletes the all the bottom ones. Can you advise how to change the code so it would do the opposite i.e. it would keep the bottom line and delete all top ones?

    2) Can you advise how to make it so that it would also sum up the values of column E and return this in column E itself. I added sheet "bugs" to the workbook to explain it bit better. I just have added the lines of how it should look and and current result of the code next to each other so its easy to see the difference. Comments included.

    Thanks for helping me out. I am very thankful of the time you have spent to help me. If you have a chance, can you have a look at these two issues?
    Cheers
    Rain


    EDIT: hehe. Almost forgot to add the attachment
    Attached Files Attached Files
    Last edited by rain4u; 06-09-2011 at 05:57 PM. Reason: EDIT: hehe. Almost forgot to add the attachment

  4. #4
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    I fixed the problem... I'm sorry for my previous error!

    Regards,
    Antonio
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    I apologize because in my previous workbook didn't run correctly the quantity sum.

    Regards,
    Antronio

  6. #6
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    Hi antoka.
    Please do not apologize. In programming its natural that there are bugs and you have to deal with them one by one when they are discovered.
    Remember that you are doing all this from your own spare time so I'm very thankful for you helping me out.

    I think you forgot to add the attachment with your last post with revised code to sum up the quantity. But I'm actually starting to understand how your code works.

    So I changed this:
    Please Login or Register  to view this content.

    Into this (added line myRange(myRow, 5) = myQty):
    Please Login or Register  to view this content.
    And it looks like its actually working. Is this correct? There is a still long way for me to fully understand all of of your code but at least I'm starting to get an idea.



    There is a another report where I would need to do similar things. I will first try to mess around by learning from this code and see if I can do it on my own. But if I fail miserably can I then ask your help again?
    Thank you for your awesome help!!!! This will help me at my work.
    Cheers
    Rain
    Last edited by rain4u; 06-10-2011 at 12:24 PM. Reason: typos

  7. #7
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    Hi again
    I have now massively learned from your code and used this knowledge to sort out a another report that I use at work. I'm almost done but there is a last obstacle that I can not figure our myself.
    this is my current code

    Please Login or Register  to view this content.
    The following two sections are the key sections that I think that would need some sort of editing.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

    I especially struggle to convert this following information
    Column AF ------ Column AG
    (Location) ------ (Delivery note)

    PLT20 03 028 ------ RN-211073
    PLT20 03 028 ------ RN-211072
    C21 03 019 ---------- RN-211511
    GAR 100C ------------ RN-211510
    GAR 100C ------------ RN-211508
    GAR 100C ------------ RN-211512
    GAR 100C ------------ RN-211513


    into this:
    PLT20 03 028--> RN-211073, RN-211072; C21 03 019--> RN-211511; GAR 100C--> RN-211508, RN-211512, RN-211513

    I have left some comments and sheets before and after on attached xls. Current macro is also attached.

    Please note I have changed column AL-s font color into white so its more clearer view. Otherwise text flows from one column to next one.


    If anyone can help me I would be vary thankful.

    Cheers
    Rain
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    See attached file where I modified the macro as below:
    Please Login or Register  to view this content.
    Regards,
    Antonio
    Attached Files Attached Files

  9. #9
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    Your post does not comply with Rule 4 of our Forum RULES. Don't Private Message, Visitor message or email questions to moderators or other members. All questions and answers will benefit other posters like yourself. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.
    Last edited by pike; 06-14-2011 at 05:28 AM.
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  10. #10
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    I apologize for my mistake and I promise to be careful in the future.

    I'm very sorry.

    Regards,
    Antonio

  11. #11
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    Hi Antonio
    The code works exactly like asked. I'm extremely thankful. I will try to learn from this code as well though I think this time it might be bit harder as it looks quite complex.

    This will definitely help me at work so thank you again for your time and effort.


    Cheers
    Rain

    EDIT:
    I have a problem when trying to use with real data. It says "Subscript out of range". I attached the file with real data. Can you look into it.
    Attached Files Attached Files
    Last edited by rain4u; 06-14-2011 at 02:09 PM.

  12. #12
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    See attached file where I modified code.

    Regards,
    Antonio
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    Thank you Antonio
    This new code works again for what I call zflex report. Thank you for helping me to resovle the issue.

    Now I started to have a same error or problem with the previous code that you helped me with for the report that I call as a manifest report.
    This code was working brilliantly after post number 6.
    As it has a similar problem or error as you just fixed I thought to check what what you did to fix the zflex report. So I checked the the differences between previous code and new code. The only difference that I saw was
    Please Login or Register  to view this content.
    Doing little bit research I understood this is a string related issue. I couldn't understand how this one line made it all work again. Why the code was working fine with the example spreadsheet but not with the real data. Then I realized the problem is not with the code but a problem within the data itself. On the real data there was something wrong on some lines where the code line:
    Please Login or Register  to view this content.
    over came this issue.

    I tested this theory with the code for manifest report. I tried the old manifest report that I used when we were creating the code. It worked brilliantly. And then I tried a report that was only generated today by the system at our work. With the newest data the code prompted with the same error as we had with the code for zflex report.

    Now I have tried to fix the code for the manifest report myself. I learned more about it, tried to amend it different ways and tested few things but so far no luck.

    Can you look at the code for the manifest report with the real latest data that is having this issue? I have tried and tried to fix it but Im not having any joy.


    I have attached the manifest report with the code that was still working quite recently.

    If you can help me once more to resolve the problem I would be very grateful. Otherwise all you hard work would go waste if it wont work on some occasions.



    Best regards
    Rain
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    I've seen in your data you have range N12685:N12687 with error and the code:
    Please Login or Register  to view this content.
    caused an error. I changed the code in:
    Please Login or Register  to view this content.
    Regards,
    Antonio
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    Hi Antonio
    Thank you for the fix. I don't know if the system produced these 3 cells or if I accidentally somehow corrupted them.
    How did you discover these lines that were corrupted or how did you find them?

    Cheers
    Rain

  16. #16
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    Excuse me if I answer so late.
    I usually change the code below the line that intercepts the error 'on error goto ...'.

    For instance, in my code I temporarily change then code:
    Please Login or Register  to view this content.
    in:
    Please Login or Register  to view this content.
    so when an error occurs code stops at 'stop' instruction, then pressing F8 you can execute code step by step and the resumes code execution exactly after the instruction that caused the error.
    Regards,
    Antonio

  17. #17
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    Hi all
    I'm doing another report that I would like to resolve in similar manner as with previous macros. I tried to use the knowledge from the previous codes to figure things out but after hours and hours started to realize I was not getting anywhere and was making things very confusing for myself. I guess I'm still a rookie with VBA. Here is the current code. Its quite basic.
    Please Login or Register  to view this content.
    At the moment it does the following in chronological order as:
    1. Prompts with input box window to define StartDate
    2. Prompts with input box window to define EndDate
    3. Moves 5 columns worth of information from sheet called RadarTool to sheet called chart_data_Ilkeston
    And thats it.
    I would like it change this slightly. Before it actually moves all the new info to the return sheet it should do the following (in chronological order I guess):
    1. Delete/remove all other rows of that do not fall between the StartDate and EndDate (inclusively). This is should be actioned with a(i, 1) - new col A on the return sheet.
    2. Delete/remove all rows that do not equal 1000137 (hard coded value that I will adjust when needed) - This is should be actioned with a(i, 5) - new col E on the return sheet.
    3. Delete/remove all rows that do not equal 0 - This is should be actioned with a(i, 3) - new col C on the return sheet.
    4. Using a(i, 1) - col A as a guide to delete all duplicates and at same time sum up the values of these deleted rows in their respective column a(i, 2) , a(i, 3) , a(i, 4). So lets say in according to column A there are 3 duplicate rows (in col A there are dates, so lets assume there are x3 rows with a same value of 01/01/2011), now in column B in those respective rows there are values 1, 2, 3. So after the the duplicate rows are deleted the summed up value would be 1+2+3 which would equal 6. Same principle is for all these a(i, 2) , a(i, 3) , a(i, 4)

    I have attached spreadsheet with this post. It has sheet "After macro". So if you run macro using parameters 02/06/2011 as a start date and 13/06/2011 then results should mirror this sheet.

    I hope someone will have time to look into this.
    Cheers
    Rain.
    Attached Files Attached Files
    Last edited by rain4u; 06-24-2011 at 04:05 PM.

  18. #18
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Macro concatenating columns together, exceptions with duplicates, delete duplicat

    I have now figured this out myself. Maybe its not done very a very elegantly but it works.

    Can any moderator change this topic to solved?


    Cheers
    Rain

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1