+ Reply to Thread
Results 1 to 12 of 12

Looping Function for Matching Rows

  1. #1
    Registered User
    Join Date
    09-07-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    17

    Looping Function for Matching Rows

    Hello all,

    Need a little help converting this IF statement in VB to a loop:

    Please Login or Register  to view this content.
    A2 B2 C2 D2 E2 F2 G2
    A3 B3 C3 D3 E3 F3 G3
    becomes:
    A2 B2 C2 D2D3 E2 F2 G2
    A3 B3 C3 D3 E3 F3 G3

    Basically, the macro goes through each row and the row below it. If col A, B, C and E, F, G match, then it combines the contents of col D, then highlights the row below it (marked as a duplicate).

    I am 1) trying to write this into a loop to go through as many as 3000 rows and 2) simply the IF statement when possible.

    Any help on this would be greatly appreciated! Thanks!
    Last edited by davesexcel; 09-07-2010 at 07:24 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,534

    Re: Looping Function for Matching Rows

    Hi ExcelGuy160

    Here is how to combine all those ANDs and makes it more readable. I only did the top for you. You can figure out the second one.

    Please Login or Register  to view this content.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,534

    Re: Looping Function for Matching Rows

    Another method using For Next loop and cells instead of range would be: (Top only)
    Please Login or Register  to view this content.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,534

    Re: Looping Function for Matching Rows

    This would also work as the above but a little simpler?
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-07-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Looping Function for Matching Rows

    Thanks so much for your help! I guess my question is: how do I make this a loop to go through as many as 3K lines on the worksheet. Will I have to code each line like this in VB or is there a shorter way write a looping macro?

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Looping Function for Matching Rows

    Is it possible for non-consecutive rows to match, say rows 5, 200, 603, etc, or are you just compairing consecutive rows?

  7. #7
    Registered User
    Join Date
    09-07-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Looping Function for Matching Rows

    There is a possibilty of a group of rows to match that are consecutive, but nothing non-consecutive like 5, 200, etc. For example:
    A2 B2 C2 D2 E2 F2 G2
    A3 B3 C3 D3 E3 F3 G3
    A4 B4 C4 D4 E4 F4 G4
    becomes:
    A2 B2 C2 D2D3D4 E2 F2 G2
    A3 B3 C3 D3 E3 F3 G3
    A4 B4 C4 D4 E4 F4 G4

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Looping Function for Matching Rows

    Try this
    Please Login or Register  to view this content.
    This should clear all duplicates and add the contents of the deleted row (Col D) to the remaining one.

    Because it filters out all duplicate rows it need only step through rows of data and not every cell

    If you need to keep the deleted rows then change this line
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

    Hope this helps
    Last edited by Marcol; 09-08-2010 at 03:35 PM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  9. #9
    Registered User
    Join Date
    09-07-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Looping Function for Matching Rows

    Thank you for your help! However, the code didn't work for me - didn't seem to make any changes to the worksheet. Everything in the rows needs to match for the row to be deleted except col D. the contents of col D need to be combined with the col D cell above it, then the row deleted if A, B, C, E, F, and G are a match. Does that make sense?

  10. #10
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Looping Function for Matching Rows

    Please Login or Register  to view this content.

  11. #11
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Looping Function for Matching Rows

    You would do best to post a sample workbook showing Before and After.

    It should clearly illustrate your problem and not contain any sensitive data.

    Cheers
    .

  12. #12
    Registered User
    Join Date
    09-07-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Looping Function for Matching Rows

    SNB, thank you! that worked great! Much appreciated

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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