Comparing two columns of data to find duplicates??

L

lindajones

New Member
Joined
Apr 8, 2003
Messages
4
*What I am starting with: 1 spreadsheet containing 11,000 rows of account data and a 2nd spreadsheet containing 350 rows of account data (both sheets have a column for 'Account#' to identify each entry.

*What I would like to accomplish: I need to compare the account number column in the small sheet with the account number column in the large sheet----if duplicate values exist, I would like to somehow flag the duplicates in the large sheet so that I can delete the duplicate rows entirely.

[i.e. I am trying to filter the entries from the small sheet OUT of the large sheet so that I can use the remaining rows of good data.]

Any ideas, besides manually searching and deleting 350 times?

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
I am not sure if I understood what you need, but it might help you, simple use CountIf and if result is bigger than 0 it's your flag:
Book1
ABCD
1AccSheet1AccSheet2Flag
21420
3221
43250
5471
6591
76250
8711
9831
109350
1110
1211
1312
1413
1514
1615
1716
1817
1918
2019
2120
Sheet1
 
Upvote 0
Let Sheet1!A:B house the longer list with A1:B1 with Acc# and desc as labels.

Select all the cells of the shorter list housing account numbers.
Go to the Name Box on the Formula Bar and type ShortList, and hit enter.

Activate an empty sheet, say, Sheet3.

Leave A1 empty.

In A2 enter:

=ISNA(MATCH(Sheet1!A2,ShortList,0))

Select A1:A2.

Activate Advanced Filter.

Check Copy to another location.

Enter Sheet1!$A:$B in the box for List range.

Enter $A$1:$A$2 in the box for Criteria range.

Enter $A$3 in the box for Copy to.

Leave Unique records only unchecked.

Click OK.
Book1
ABCD
1
2TRUE
3Acc#Desc
404157E
500107>
607622D
704484C
809278Z
9053249
1005201Z
1107811:
1203460Q
Sheet3
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
VBA Code for comparing two sheets and copying non duplicates
Replies
7
Views
328
mumps
mumps
N
  • Locked
  • Question
Compare data in columns, remove duplicates and return unique values in a new column
2
Replies
12
Views
444
Peter_SSs
Peter_SSs
H
  • Question
Finding Duplicate Values/ Different Sorting Methods
Replies
8
Views
371
DanteAmor
DanteAmor
C
  • Question
Find duplicates when pulling data from other sheets
Replies
3
Views
176
Trebor76
T
H
  • Solved
Macro to Delete all Duplicates that contain similar text and retain last Duplicate
Replies
5
Views
596
howard
H

Forum statistics

Threads
1,214,911
Messages
6,122,198
Members
449,072
Latest member
DW Draft

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