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

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


wrote:
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

Hi

Mark your range of data, then DataAdvanced Filterclick Unique Records
onlyMove to Another location and select where you want the unique list
to be placed.

--
Regards

Roger Govier


wrote in message
oups.com...
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 wrote:
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


wrote:
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

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((COU NTIF($B$1:B11,newList)=0)*(newList<""),ROW(newLis t)))-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
wrote:
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


wrote:
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