+ Reply to Thread
Results 1 to 11 of 11

Searching for patterns, deleting records, and moving values

  1. #1
    Registered User
    Join Date
    07-03-2012
    Location
    Norge
    MS-Off Ver
    Excel 2010
    Posts
    40

    Searching for patterns, deleting records, and moving values

    Hi everyone,

    I've got this problem I'm not sure how to approach, other than that I must use VBA to solve it (I guess).
    So, with no prior experience with VBA, I ask you here.

    The same part can have one or more rows, depending on the number of machines it belongs to.
    If one part belongs to several machines, it has its own row.

    I need this list to be altered to remove the duplicate rows, and add the additional machines in columns behind
    the first machine.

    This way I only get one line per part number, and have the machines listed progressively by adding columns to the line for the part.
    Right now my workbook contains some 20000+ rows, and about 19000 of them are duplicates.

    Column A = Part number
    Column B = Quantity
    Column C = Machine

    BEFORE
    A B C
    1234 1 10
    1324 0 11
    1234 1 12

    AFTER
    NEW NEW
    A B C D E
    1243 1 10 11 12

    So basically:
    1. Find duplicate rows with the same part number
    2. Move value (col C) from the extra rows to D, E, F... in the first row.
    3. Add the total quantity number from range BX:BY to cell BX
    4. Delete the extra rows.

    Can anyone help with this? It might be easy, but I'm completely new to VBA.

    The attached Excel-file shows the entire data set, minus some columns not relevant for the processing.

    Best regards,
    Knut F. Henriksen
    Norway
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Searching for patterns, deleting records, and moving values

    Ok,

    Try this. It takes about 2 minutes to run due to the large amount of data and my laziness not to spend another hour of testing to optimize it.

    Please Login or Register  to view this content.
    Please take time to read the forum rules

  3. #3
    Registered User
    Join Date
    07-03-2012
    Location
    Norge
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Searching for patterns, deleting records, and moving values

    You're a genius!

    The file works very well, after checking much of the processed data.
    In my main workbook, the one needing processing, I have some additional columns.

    The supplier component number is still in col A, but quantity is now in col F, and machine is in col K.
    Can the code you've written be used in the same sheet?

    The other columns just contains additional parts data.

    Thank you in advance!

    Best regards,
    Knut

  4. #4
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Searching for patterns, deleting records, and moving values

    Yes, then change it to this

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-03-2012
    Location
    Norge
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Searching for patterns, deleting records, and moving values

    Thanks! I actually figured it out just before your reply, by playing around.
    But when processing the entire sheet, I'm not satisfied with the results:

    Before running the macro, I use Excels function for removing duplicates.
    It does a decent job, but I still have duplicates, since the Excel function
    only takes into account the whole row.

    So I would also need to find duplicates before running the macro.
    With the duplicate search only taking into account part number and
    machine number. Since the original list can have more than one part record
    per machine, where the same machine uses the one part in x quantity.

  6. #6
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Searching for patterns, deleting records, and moving values

    Ok,

    Then add this to your code

    Please Login or Register  to view this content.
    It removes duplicate rows looking at both column a and b

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,776

    Re: Searching for patterns, deleting records, and moving values

    Different method
    Attached Files Attached Files
    Last edited by jindon; 07-05-2012 at 03:36 AM.

  8. #8
    Registered User
    Join Date
    07-03-2012
    Location
    Norge
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Searching for patterns, deleting records, and moving values

    Do I make a new subroutine for it, or embed it somewhere in the existing code?

  9. #9
    Registered User
    Join Date
    07-03-2012
    Location
    Norge
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Searching for patterns, deleting records, and moving values

    Ok,

    Then add this to your code


    Sheets(2).Range("A1:L" & Sheets(1).UsedRange.Rows.Count).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    It removes duplicate rows looking at both column a and b
    Do I make a new subroutine for it, or embed it somewhere in the existing code?

  10. #10
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Searching for patterns, deleting records, and moving values

    You can add it at the topf of an existing routine

  11. #11
    Registered User
    Join Date
    07-03-2012
    Location
    Norge
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Searching for patterns, deleting records, and moving values

    I still cannot get this to work correctly.
    The first and major part of the task works, but avoiding the last duplicates seems like a hassle.

    After running this code:
    Please Login or Register  to view this content.
    I get:
    shot1.jpg

    Which comes from the raw data:
    shot2.jpg

    The problem lies in that I still get duplicates.
    Might have implemented the new code bit wrong, but anyhow.

    Your help is immensely appreciated!

+ 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