Removing duplicates

ZeroData

New Member
Joined
Feb 4, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Hi Everyone, to explain my project I have two lists of people. One list is very large list of everyone in the org, and the other is a relatively small list of who has a specific training.I have to compare the two lists and identify who needs the training. I tried to identify the duplicates but excel didn’t like that either. I’m working with employee ID numbers. Any help or direction would be very much appreciated
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Welcome to the Board!

There are multiple ways you can do this.

One way is to use VLOOKUP to lookup a value from one list to see if it is found in the other.
See here for details and examples: MS Excel: How to use the VLOOKUP Function (WS)
If you want to replace with ERROR that is returned when someone isn't found, you can wrap it in an IFERROR function, i.e.
Excel Formula:
=IFERROR(VLOOKUP(...),"Not found")

You could also use a COUNTIF formula to count how many times a value shows up in the other list.
See: MS Excel: How to use the COUNTIF Function (WS).
 
Upvote 0
Solution
One list is very large list of everyone in the org, and the other is a relatively small list of who has a specific training.
With VBA, try this:
The result is in col C. You can sort col C to get the intended employee.
VBA Code:
Sub a1160683a()
Dim i As Long
Dim va, vb, vc, x
Dim d As Object

va = Range("A2", Cells(Rows.Count, "A").End(xlUp))
vb = Range("D2", Cells(Rows.Count, "D").End(xlUp))

ReDim vc(1 To UBound(va, 1), 1 To 1)
Set d = CreateObject("scripting.dictionary")
d.CompareMode = vbTextCompare

    For Each x In vb
        d(x) = Empty
    Next
    
    For i = 1 To UBound(va, 1)
        If d.Exists(va(i, 1)) Then
        vc(i, 1) = 1
        Else
        vc(i, 1) = 0
        End If
    Next

'put the result in col C
Range("C2").Resize(UBound(vc, 1), 1) = vc
End Sub

dhee - check valid date.xlsm
ABCD
1List 1ResultList 2
2A1A
3B1B
4C0D
5D1F
6E0H
7F1I
8G0M
9H1K
10I1O
11J0
12K1
13L0
14M1
15N0
16O1
17P0
18Q0
19R0
20S0
21T0
22U0
23V0
24W0
25X0
26Y0
27Z0
Sheet2
 
Upvote 0
Welcome to the Board!

There are multiple ways you can do this.

One way is to use VLOOKUP to lookup a value from one list to see if it is found in the other.
See here for details and examples: MS Excel: How to use the VLOOKUP Function (WS)
If you want to replace with ERROR that is returned when someone isn't found, you can wrap it in an IFERROR function, i.e.
Excel Formula:
=IFERROR(VLOOKUP(...),"Not found")

You could also use a COUNTIF formula to count how many times a value shows up in the other list.
See: MS Excel: How to use the COUNTIF Function (WS).
Thank you, VLOOKUP did the trick
 
Upvote 0
You are welcome.
Glad we were able to help.
As you can see, there are multiple ways of attacking this problem in Excel!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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