# Looping Function for Matching Rows

1. ## 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!

2. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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

9. ## 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. ## Re: Looping Function for Matching Rows

``Please Login or Register  to view this content.``

11. ## 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. ## Re: Looping Function for Matching Rows

SNB, thank you! that worked great! Much appreciated

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