Identify duplicate rows and copy/Paste the first cell to all cells of equal rows

thanks for the reply. good tips. very useful. however, I'm still struggling to copy the first ID of each bunch and paste for all the other cells in that bunch. E.g. if there are four equal rows with four different IDs in column B, then I want the B1 to be copied to B2, B3 and B4. same should be repeated for all the separated rows. E.g. 20000 lines could be separated into 150 bunches where each bunch contains equal rows and then the first cell of each bunch in column B will be pasted into the rest of the cells of column B of that bunch.

Aha. I see what you mean.

How about:

Please Login or Register  to view this content.

If you put this loop after the first loop (i.e. the one inserting the blank rows) then it should cycle through change all 'B' values within a group to the first 'B' value of that group.

Hopefully should work.

Thanks TKCZBW. I'm going to give it a try. will let you know how it went.

cheers

I've just spotted an error in my code, if you replace (on line 5) the first 'i' with i+1 and then the 'i+1' with i, that should (fingers crossed) do it.

Hi TKCZBW. thanks again for your input. I have tried to use your method. however, it gives me the same results as my original code, above, did. it copies B2 to A3 then B3 to A4 and so on. what I need is to copy the B2 to A2, A3, and so on as long as the rows are equal based on columns D,E,F,G. below is the new code based on your inputs. I've also included a spread sheet example of what the code does on sheet 1 and what I need on sheet 2. Many thanks

Sub MIdCopyNew()
Dim lrow As Long


lrow = ActiveSheet.UsedRange.Rows.Count

For i = lrow To 2 Step -1



If Cells(i, 4).Value = Cells(i + 1, 4).Value And Cells(i, 5).Value = Cells(i + 1, 5).Value And Cells(i, 6).Value = Cells(i + 1, 6).Value And Cells(i, 7).Value = Cells(i + 1, 7).Value Then ' and rows D,E and so on here as well in an 'and' statment)

If Cells(i + 1, 2).Value <> "" Then 'Skip blank rows by sampling column 'C'
Cells(i + 1, 1).Value = Cells(i, 2).Value
End If
End If

Next i


End Sub


What the Code Does:
What Code Does.jpg

What I need it to do:
What I need it to do.jpg

Ok, so you want each data block to have the same new primary value as the first recorded ID value within that block?

If the code is correctly inserting a new row to deliniate the blocks (which doesn't appear on your examples, but was in your initial query) then I would:

Please Login or Register  to view this content.

Let me know how it works.

Perfect TKCZBW. much appreciated. it works just fine. Brilliant.

cheers

Good! Sorry it took me so long to understand the question! If you could mark the question as answered and add reputation, that would be most appreciated!

Sure. No worries. It's already done. !

thanks again