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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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

U
  • Solved
Doing a search for text within a call, from entries in a defined name range
Replies
3
Views
127
Upex
U
R
  • Solved
How to use conditional formatting to color the row in red based on cell value in other sheet
2
Replies
14
Views
171
etaf
etaf
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
238
krunal123
K
D
  • Question Question
Can't get folder to create on desktop. When debugging it shows no errors.
2 3
Replies
24
Views
435
jolivanes
jolivanes
B
  • Solved
OPTIMIZE my code
Replies
5
Views
330
BritsBlitz
B

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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