Copy values in other columns for duplicates in column A

[Jannickk]

Hello,

I am trying to create a macro that completes data in a sheet for me, but I'm stuck at this one part. I'm hoping I could get some help here.

I have a big sheet with data that has unique values in column A. At the bottom of this sheet, I manually add certain values that exist in A, but the other columns are empty.
I would like to copy data from certain columns for these duplicates values.

In the attached sheet you can see an example of this problem.

Thanks for looking!

[Marc L]

As next demonstration works only with smart readers on a smart enough worksheet …

As a VBA beginner starter you must paste to the 'First Sheet' worksheet module (so very not in a standard module) :

Sub Demo1() 
        Dim R&, L& 
        Application.ScreenUpdating = False 
    With Me.UsedRange.Rows 
        For R = .Count To 2 Step -1 
            If Application.CountA(.Item(R)) = 1 Then 
                L = Application.Match(.Cells(R, 1), .Columns(1), 0) 
                If L < R Then .Item(L).Copy .Item(R) 
            Else 
                Exit For 
            End If 
        Next 
    End With 
        Application.ScreenUpdating = True 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !

[Jannickk]

Hey Marc, thanks for commenting.

I am a complete beginner at VBA and have mainly used the macro recorder to make my macro's.
I have no idea what your code does and what variables I need to define.

Could you specify your explanation a bit more please?

[Marc L]

You just have to paste the demonstration as it is (so you have nothing to 'define') to the appropriate worksheet module
whatever via the VBE Project window or via a right click on the sheet tab …

Or for a standard code module replace Me statement by the worksheet reference …

[Jannickk]

Hi Marc,

Thank you very much, I don't quite understand the code, but it seems to work in my example sheet :D!
Unfortunately, it jumps from For to If to Else to Exit For to End With in an actual dataworkbook, thus not doing anything.
But since the actual datasheet is more complex, I'm not too surprised it didn't work... I had just hoped it would have worked.

I'll add a more accurate version of the sheets I'm actually working with as a better example.
Most columns have data in them, but I don't need to copy that data, just the columns that are not empty (if that's too complex, I can try to think of a way to clear them later on).
As you can see, all data is stored as text, and the manual added data is not. That is probably the reason the code doesn't work, as the values aren't actually the 'same'.
With text to columns it's possible to convert the data in A to match the manually added data in A, but the code still doesn't work if I do that.

Does this change your entire code, or does it only need some slight altering?

Thank you for the time you've spent on this already, I would really appreciate it if you'd help me with this final touch as well

[Marc L]

My demonstration well works as I wrote on a 'smart enough worksheet' but your original attachment is not
so just clean it like any real worksheet must be so it must work like on my side it does …

The same for your last attachment, worse than your original
as 'blank' cells are not empty so clear the row entirely for the 'duplicates'
then copy for example cell A8 to cell A57 and execute my demonstration :
it works for row #57 so no issue on a smart sheet …

When the initial attachment does not exactly reflect the real workbook that means
you are enough confident with your Excel / VBA skills to amend any helper solution.

For your last attachment as it is, replace the CountA worksheet function = 1
by the CountBlank worksheet function = .Columns.Count - 1
and replace the Application.Match by the Range.Find method (to see in the VBA help) …

[Jannickk]

Hey Marc, thanks so much.

I replaced the CountA = 1 part with the CountBlank = .Columns.Count - 1 and left the Application.Match/Range.Find part as I didn't understand it, but this piece of code seems to do what I wanted!
I'm glad you kept up with my ignorance as I'm pretty clueless when it comes to (in my eyes) advanced VBA coding.

You earned yourself some well deserved reputation, thanks again!

[PCI]

Another way to do
it seems not working with the last file because last records in column "A" with empty end row do not exist in previous rows.

Please Login or Register  to view this content.

[Jannickk]

Hi PCI, thank you for commenting.

I'll try messing around a little with your method too to see what I prefer.

I figure converting the values in A to numbers (like the bottom values) is an importent aspect for this to work.

For now I'll mark this thread as solved as I think I've received enough good ideas.

Thanks for your help guys

[Marc L]

[…] as I didn't understand it

… for your last attachment (edit v2) :

Sub Demo2() 
        Dim R&, L& 
        Application.ScreenUpdating = False 
    With Blad1.UsedRange.Rows 
        For R = .Count To 2 Step -1 
            If Application.CountBlank(.Item(R)) = .Columns.Count - 1 Then 
                L = .Columns(1).Find(.Cells(R, 1), , , xlWhole).Row 
                If L < R Then .Item(L).Copy .Item(R) 
                If Application.CountBlank(.Item(R)) = .Columns.Count - 1 Then .Item(R).EntireRow.Delete 
            Else 
                Exit For 
            End If 
        Next 
    End With 
        Application.ScreenUpdating = True 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !