Identify Duplicate Items Based On Multiple Criteria

[calummurdo]

Hi,

I have a rather large list (approx 20,000 rows) of suppliers of
training. I have their name in column 1, type of training they provide
in column 2 (e.g. health and safety, first aid, generic stuff) and the
name of the course in column 3.

e.g.
Col 1
Provider 1
Provider 1
Provider 1
Provider 2
Provider 3

Col 2
H&S
H&S
First Aid
H&S
Other

Col 3
5 Day Course
5 Day Course
3 Day Course
Chainsaw Training
Something else

The problem is many of the courses are duplicated so one supplier of
one type of course with a given name might have fifty entries that are
identical. What I would like to do is to have one entry for each
variation but can't think how. I did consider an array but only end up
counting the number of duplicates

Any ideas would be most appreicated!

Best Regards,


CalumMurdo Kennedy

 

[shail]

Hi,

What you need to do then, like removing the duplicate entries or just
you want to mark them.

That will be helpful to know to help you


Thanks,
Shail

 

[Roger Govier]

Hi

Mark your range of data, then Data>Advanced Filter>click Unique Records
only>Move to Another location and select where you want the unique list
to be placed.

 

[calummurdo]

Hi,

What you need to do then, like removing the duplicate entries or just
you want to mark them.

That will be helpful to know to help you


Thanks,
Shail

Hi Shail,

I will need to remove the duplicated entries from the list leaving only
unique rows (i.e. just one of each). I've tried the Filter by Selection
which (surprisingly!) has done the job but would be interested in
hearing if you had a formula as it's been doing my head in!

Best Regards,

CalumMurdo Kennedy

 

[shail]

I too would have suggested you to use the Advanced Filter. But you can
use a formula too.

I am assuming your original column is A. First you need to name your
range (let's name it - newList). Now at the adjacent column (at B)
leaving the top cell blank, enter the formula as at B2:

=IF(ISERROR(INDEX(newList,MIN(IF((COUNTIF($B$1:B1,newList)=0)*(newList<>""),ROW(newList)))-MIN(ROW(newList))+1)),"",INDEX(newList,MIN(IF((COUNTIF($B$1:B11,newList)=0)*(newList<>""),ROW(newList)))-MIN(ROW(newList))+1))

You need to array enter the formula <CRTL><SHIFT><ENTER>

Copy down till the end of your column. You will get the unique values.
I myself has learnned it here in this group from Leo Heuser.

Hope this works for you.

Thanks

Shail