List Duplicates & Unique records

OttoMan

New Member
Joined
Jun 26, 2008
Messages
14
Hi,
I have the following information in two lists. List A changes each week and List B is like the Master List.
Problem is when I receive list A each week there are duplicate ID's in list A ( there are more than 10,000 ID's in the list) and also there are unique ID's appearing each week which are not in List B ( which I have to update)
Can you please help me with a formula (NO Macro's please) which can identify and list duplicates in List A and to find and identify the unique ID's in List A compared to List B?

Excel Workbook
BCDEFGHIJKLMNO
21. to find and list the duplicates from the range B4:F38 (List A)
3List AList BDuplicates List
4This WeekWeek1Week2Week3ID No'sID No'sID No'sThis WeekWeek1Week2Week3
5119320257341438043802203238129297487
6466174132644878487822032279490347303
7288448197861348863933562919876492239
81051192152241400389303562942223034826
938129297487220329101575502242823121
102794903473032203211628575501859271271
112924623654682599513488656053422171335
12350478624653165814003656057742145212
13499981142134769220328170330023455364
141987649223935629259958170335197494178
154222303482635629263405689732304130405
161902920730037844315595689737304145857
174032233452024962831658
18459235135665707034769
1922428231215755035629
2018592712715755037844
213714501864556086940622
221873023443246545045280
2334221713356560549628
2477421452126560557070
25412194133776661057550
264852067712471800608692. to compare List A, ID No's against List B ID no's and list the unique ID's in List A. (there could be duplicates here as well)
274984094714597373265450
28323041304057951065605
29300234553648170366610Unique ID Numbers
30351974941788170371800ID No'sThis WeekWeek1Week2Week3
31498409471459899987373289998498409471459
323230413040556897795105689732304130405
333730414585756897817035689737304145857
Sheet1
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Ottoman using your "Exact" layout:

Formula in G5:

=IF(ISERROR(MATCH($F5,$H$5:$H$1005,0)),10000+ROW(F5),IF(COUNTIF($F$5:$F$1004,$F5)>1,ROW(F5),99999))

copy down to last row of List A ID numbers.

Formula in I5:

=SMALL($G$5:$G$1005,ROW(I5)-4)

copy down to last row in table -- your example this would be I23

Formula in J5:

=IF($I5>9999,"",INDEX($F$1:$F$1005,I5))

copy down to last row in table -- your example this would be J23

Formula in K5:

=IF($I5>9999,"",INDEX(B$1:B$1005,$I5))

this formula can be used for all weeks -- so your exmample would be range K5:N23

For UNIQUE listings

Formula in I30 (header row of 2nd table):

=COUNTIF($I$5:$I$23,"<10000")

Formula in I31:

=SMALL($G$5:$G$1005,$I$30+(ROW(I31)-30))

copy down to last row of data in table 2 - your example I33

Formula in J31:

=IF($I31=99999,"",INDEX($F$1:$F$1005,I31-10000))

copy down to last row of data in table 2 - your example J33

Formula in K31:

=IF($I31=99999,"",INDEX(B$1:B$1005,$I31-10000))

this formula can be used for all weeks -- your example K31:N33

as you expand your data you will need to revise some of the formulae / ranges etc...

also this makes the assumption that if an ID is unique it will go in table 2 ONLY regardless of whether or not it's duplicated....
 
Upvote 0
Hi Lasw10,

Thanks heaps for your detailed answer,... it works !!! beautiful !!!

-Ottoman
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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