+ Reply to Thread
Results 1 to 10 of 10

Copy values in other columns for duplicates in column A

  1. #1
    Registered User
    Join Date
    03-05-2020
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    17

    Question Copy values in other columns for duplicates in column A

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,886

    Cool Hi ! Try this !


    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) :

    PHP Code: 
    Sub Demo1()
            
    Dim R&, L&
            
    Application.ScreenUpdating False
        With Me
    .UsedRange.Rows
            
    For = .Count To 2 Step -1
                
    If Application.CountA(.Item(R)) = 1 Then
                    L 
    Application.Match(.Cells(R1), .Columns(1), 0)
                    If 
    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 !

  3. #3
    Registered User
    Join Date
    03-05-2020
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    17

    Re: Hi ! Try this !

    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?

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,886

    Arrow Re: Copy values in other columns for duplicates in column A


    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

  5. #5
    Registered User
    Join Date
    03-05-2020
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    17

    Re: Copy values in other columns for duplicates in column A

    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
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,886

    Arrow Re: Copy values in other columns for duplicates in column A


    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)

  7. #7
    Registered User
    Join Date
    03-05-2020
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    17

    Re: Copy values in other columns for duplicates in column A

    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!

  8. #8
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,661

    Re: Copy values in other columns for duplicates in column A

    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.
    - Battle without fear gives no glory - Just try

  9. #9
    Registered User
    Join Date
    03-05-2020
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    17

    Re: Copy values in other columns for duplicates in column A

    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

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,886

    Lightbulb A variation

    Quote Originally Posted by Jannickk View Post
    [] as I didn't understand it
    for your last attachment (edit v2) :

    PHP Code: 
    Sub Demo2()
            
    Dim R&, L&
            
    Application.ScreenUpdating False
        With Blad1
    .UsedRange.Rows
            
    For = .Count To 2 Step -1
                
    If Application.CountBlank(.Item(R)) = .Columns.Count 1 Then
                    L 
    = .Columns(1).Find(.Cells(R1), , , xlWhole).Row
                    
    If 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 !
    Last edited by Marc L; 03-21-2020 at 08:08 AM. Reason: optimization

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Transfer values from row to a new column if other columns are consecutives duplicates
    By Galuppo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-20-2016, 02:14 PM
  2. Replies: 7
    Last Post: 01-31-2016, 12:18 PM
  3. Replies: 6
    Last Post: 01-29-2015, 02:54 AM
  4. Matching values in two columns - one column has duplicates
    By morganz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-08-2015, 10:38 AM
  5. Replies: 4
    Last Post: 07-15-2013, 01:37 PM
  6. [SOLVED] Copy data from two columns and paste without duplicates in another column
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-22-2012, 07:56 PM
  7. Identify Duplicates in columns and copy corresponding colums in next column
    By sreesatya in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-04-2012, 01:02 PM

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