VBA - compare list without duplicate

P

PeterB1988

New Member
Joined
Jun 26, 2015
Messages
5
Hi,
I have been working on a VBA code for the last week or so and can get about 5% away from the result that I require.

I have 2 worksheets:

Sheet1 - which has a list of data in E:E
Sheet2 - which has a list of data in G2:G
Both lists have matching values in the list.

What I am trying to do is check if each cell in sheet1 is in sheet2, if it is then copy and paste the data from that row into the relative row of sheet2's matched value. This bit I can do........Where I am struggling is when I have a single value in sheet1 but multiple values in sheet2. I only want to copy and paste the data across in one match but it will copy and paste the info across to both matches. Similarly if I have 2 values in sheet1 but only one value in sheet2 it overwrites the information.

Below I have shown the code I am using.

Sub FillinAlegs()

Application.ScreenUpdating = False
Dim names As Range, name As Range, values As Range, value As Range, rng As Range



Set values = Worksheets("Sheet1").Range("E1:E" & Worksheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Row) '
Set names = Worksheets("Sheet2").Range("G2:G" & Worksheets("Sheet2").Range("G" & Rows.Count).End(xlUp).Row) '

For Each name In names
For Each value In values
If value.value = name.value Then
Worksheets("Sheet1").Range("A" & value.Row & ":P" & value.Row).Copy Destination:=Worksheets("Sheet2").Range("C" & name.Row & ":R" & name.Row)
End If
Next value
Next name

Application.ScreenUpdating = True

End Sub
Click to expand...

Please help me. I am pulling my hair out!

Thanks :)
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
PeterB1988 said:
Hi,
I have been working on a VBA code for the last week or so and can get about 5% away from the result that I require.

I have 2 worksheets:

Sheet1 - which has a list of data in E:E
Sheet2 - which has a list of data in G2:G
Both lists have matching values in the list.

What I am trying to do is check if each cell in sheet1 is in sheet2, if it is then copy and paste the data from that row into the relative row of sheet2's matched value. This bit I can do........Where I am struggling is when I have a single value in sheet1 but multiple values in sheet2. I only want to copy and paste the data across in one match but it will copy and paste the info across to both matches. Similarly if I have 2 values in sheet1 but only one value in sheet2 it overwrites the information.

Below I have shown the code I am using.



Please help me. I am pulling my hair out!

Thanks :)
Click to expand...
I would suggest using DICTIONARY.
 
Upvote 0
How about
Code: 
Sub CopyCompare()
   Dim Cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet

Application.ScreenUpdating = False
   Set Ws1 = Sheets("Pcode")
   Set Ws2 = Sheets("Sheet2")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("E1", Ws1.Range("E" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, -4).Resize(, 16)
      Next Cl
      For Each Cl In Ws2.Range("G2", Ws2.Range("G" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then
            .Item(Cl.Value).Copy Cl.Offset(, -4)
            .Remove (Cl.Value)
         End If
      Next Cl
   End With
End Sub
 
Upvote 0
HI Fluff, that code seems to have the same problems as my code. The issue is when there is a duplicate in E:E. It only copies the first duplicated value across to sheet2. At least yours doesn't overwrite the data which is great. If it helps with a solution; although column E:E will have duplicates, the data in column B:B wont be the same for them

Below is sheet1 - columns A-P
[TABLE="width: 888"]
<tbody>[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[TD="align: center"]M
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]O
[/TD]
[TD="align: center"]P
[/TD]
[/TR]
[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]06:00
[/TD]
[TD="align: center"]06:45
[/TD]
[TD="align: center"]BlueCar
[/TD]
[TD="align: center"]A Leg
[/TD]
[TD="align: center"]Garage
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]AB
[/TD]
[TD="align: center"]CD
[/TD]
[/TR]
[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]06:00
[/TD]
[TD="align: center"]06:45
[/TD]
[TD="align: center"]BlueCar
[/TD]
[TD="align: center"]A Leg
[/TD]
[TD="align: center"]Garage
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]AB
[/TD]
[TD="align: center"]CD
[/TD]
[/TR]
[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]06:00
[/TD]
[TD="align: center"]06:45
[/TD]
[TD="align: center"]BlueCar
[/TD]
[TD="align: center"]A Leg
[/TD]
[TD="align: center"]Garage
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]AB
[/TD]
[TD="align: center"]CD
[/TD]
[/TR]
</tbody>[/TABLE]


Below is sheet 2 - the output of your code - columns C-R

[TABLE="width: 888"]
<tbody>[TR]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[TD="align: center"]M
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]O
[/TD]
[TD="align: center"]P
[/TD]
[TD="align: center"]Q
[/TD]
[TD="align: center"]R
[/TD]
[/TR]
[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]06:00
[/TD]
[TD="align: center"]06:45
[/TD]
[TD="align: center"]BlueCar
[/TD]
[TD="align: center"]A Leg
[/TD]
[TD="align: center"]Garage
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]AB
[/TD]
[TD="align: center"]CD
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]BlueCar
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]BlueCar
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


Below is the desired outcome - there are 5 BlueCars in the sheet2 so the 3 from sheet1 will be pasted in and then leave the other 2 blank.

[TABLE="width: 888"]
<tbody>[TR]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[TD="align: center"]M
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]O
[/TD]
[TD="align: center"]P
[/TD]
[TD="align: center"]Q
[/TD]
[TD="align: center"]R
[/TD]
[/TR]
[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]06:00
[/TD]
[TD="align: center"]06:45
[/TD]
[TD="align: center"]BlueCar
[/TD]
[TD="align: center"]A Leg
[/TD]
[TD="align: center"]Garage
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]AB
[/TD]
[TD="align: center"]CD
[/TD]
[/TR]
[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]06:00
[/TD]
[TD="align: center"]06:45
[/TD]
[TD="align: center"]BlueCar
[/TD]
[TD="align: center"]A Leg
[/TD]
[TD="align: center"]Garage
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]AB
[/TD]
[TD="align: center"]CD
[/TD]
[/TR]
[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]06:00
[/TD]
[TD="align: center"]06:45
[/TD]
[TD="align: center"]BlueCar
[/TD]
[TD="align: center"]A Leg
[/TD]
[TD="align: center"]Garage
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]AB
[/TD]
[TD="align: center"]CD
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]BlueCar
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]BlueCar
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Ok,how about
Code: 
Sub CopyCompare()
   Dim Cl As Range
   Dim ValU As String
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet

Application.ScreenUpdating = False
   Set Ws1 = Sheets("Pcode")
   Set Ws2 = Sheets("Sheet2")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("E1", Ws1.Range("E" & Rows.Count).End(xlUp))
         ValU = Cl.Value & "|" & Cl.Offset(, 3).Value
         If Not .exists(ValU) Then .Add ValU, Cl.Offset(, -4).Resize(, 16)
      Next Cl
      For Each Cl In Ws2.Range("G2", Ws2.Range("G" & Rows.Count).End(xlUp))
         ValU = Cl.Value & "|" & Cl.Offset(, 3).Value
         If .exists(ValU) Then
            .Item(ValU).Copy Cl.Offset(, -4)
            .Remove (ValU)
         End If
      Next Cl
   End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
191
krunal123
K
B
  • Solved
OPTIMIZE my code
Replies
5
Views
303
BritsBlitz
B
M
  • Solved
VBA to Insert Mismatched Row in separate worksheet
2 3
Replies
21
Views
753
Peter_SSs
Peter_SSs
S
  • Question Question
Duplicate sheet comes in listing sheets
Replies
1
Views
62
Senthil Murugan
S
N
  • Question Question
VBA loop to duplicate sheets between one existing excel file and anew one
Replies
2
Views
121
ND1979
N

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top