+ Reply to Thread
Results 1 to 3 of 3

Match column between sheet, copy offset, combine duplicate

  1. #1
    Registered User
    Join Date
    07-23-2018
    Location
    Philippines
    MS-Off Ver
    Office 365
    Posts
    2

    Question Match column between sheet, copy offset, combine duplicate

    Hi guys, need your help with below codes,it's not working correctly. The task is to match column between 2 sheets and copy offset on desired columns and combine if 2 or more matches found. Appreciate all your help. Thank you.

    PHP Code: 
    Private Sub CommandButton1_Click()
    Application.ScreenUpdating False
           Dim lRow 
    As Long
           Dim fValue 
    As Range
            Dim n 
    As Long
    lRow 
    Sheet1.Range("A" Rows.Count).End(xlUp).Row
    For Each cell In Sheet1.Range("A1:A" lRow)
             
    Set fValue Sheet2.Columns("C:C").Find(cell.Value)
              If 
    fValue Is Nothing Then GoTo NextCell
    If cell.Offset(01).Value fValue.Offset(03).Value And cell.Offset(02).Value "Layer1" Then
    fValue
    .Offset(02) = cell.Offset(05)
    ElseIf 
    cell.Offset(01).Value fValue.Offset(03).Value And cell.Offset(02).Value "Layer2" Then
    fValue
    .Offset(03) = cell.Offset(05)
    ElseIf 
    cell.Offset(01).Value fValue.Offset(03).Value And cell.Offset(02).Value "Layer3" Then
    fValue
    .Offset(04) = cell.Offset(05)
    End If
    NextCell:
    Next cell
    Application
    .CutCopyMode False
    Application
    .ScreenUpdating True

    End Sub 
    Attached Files Attached Files
    Last edited by keyboard_jv; 07-23-2018 at 03:54 AM. Reason: file attached

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Match column between sheet, copy offset, combine duplicate

    Why aren't you using a pivot table?
    Attached Files Attached Files
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    07-23-2018
    Location
    Philippines
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Match column between sheet, copy offset, combine duplicate

    Hi, I prefer vba codes to auto fill cells. Can't figure out what is wrong with my existing codes. Thank you.

+ 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. Match 2 Columns in two sheets; copy an offset Column of matched Row to Sheet 1
    By sureng20 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-01-2017, 03:08 AM
  2. [SOLVED] Combine vlookup or index/match with offset
    By Simon1185 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-31-2017, 03:37 PM
  3. [SOLVED] Compare two lists in Sheet 1 & 2 Column A, then combine the match adjacent cells in sheet3
    By Raulus in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-06-2013, 08:51 AM
  4. merge duplicate and if value in b column not match copy to
    By jamalmail in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-18-2012, 07:54 PM
  5. How Do I Combine OFFSET With INDEX MATCH
    By joconnor125 in forum Excel General
    Replies: 8
    Last Post: 06-15-2012, 04:47 AM
  6. Replies: 5
    Last Post: 05-31-2012, 10:20 AM
  7. Search column a for duplicate data and copy entire row to duplicate sheet
    By crazyAMP in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-20-2012, 08:21 PM

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