Remove Duplicates (Macro)
 

Using Excel 2007.
I need to be able to select a series of cells in a single column and count
the unique data (no duplicates). The existing cell format is "General".
Currently each cell is a hyperlink in the format shown below:
1-2323456
1-4345356
1-5454232
1-2323456

If these cells are selected, the answer should be 3 (because top and bottom
are the same).
Thanks

Remove Duplicates (Macro)
 

On Saturday, October 13, 2012 9:35:47 AM UTC-5, JCO wrote:
Using Excel 2007.

I need to be able to select a series of cells in a single column and count

the unique data (no duplicates). The existing cell format is "General".

Currently each cell is a hyperlink in the format shown below:

1-2323456

1-4345356

1-5454232

1-2323456



If these cells are selected, the answer should be 3 (because top and bottom

are the same).

Thanks

I think you will need a macro to loop thru the hyperlink ADDRESS

Remove Duplicates (Macro)
 

Il 13/10/2012 16:35, JCO ha scritto:
Using Excel 2007.
I need to be able to select a series of cells in a single column and
count the unique data (no duplicates). The existing cell format is
"General". Currently each cell is a hyperlink in the format shown below:
1-2323456
1-4345356
1-5454232
1-2323456

If these cells are selected, the answer should be 3 (because top and
bottom are the same).
Thanks


=SUMPRODUCT(1/COUNTIF(A1:A4,A1:A4))

E.

Remove Duplicates (Macro)
 

In article , says...

Il 13/10/2012 16:35, JCO ha scritto:
Using Excel 2007.
I need to be able to select a series of cells in a single column and
count the unique data (no duplicates). The existing cell format is
"General". Currently each cell is a hyperlink in the format shown below:
1-2323456
1-4345356
1-5454232
1-2323456

If these cells are selected, the answer should be 3 (because top and
bottom are the same).
Thanks


=SUMPRODUCT(1/COUNTIF(A1:A4,A1:A4))

I don't really get how this works?

John Black

It sum 1 for every single string, 1/2 for every double string, 1/3 for every triple string, and so on.
So that the sum report the total number of different strings.

E.

Remove Duplicates (Macro)
 

In article , says...

John Black;1606387 Wrote:
In article ,
says...-


=SUMPRODUCT(1/COUNTIF(A1:A4,A1:A4))-

I don't really get how this works?

John Black

It sum 1 for every single string, 1/2 for every double string, 1/3 for
every triple string, and so on.
So that the sum report the total number of different strings.

Thanks, that's clever. But I found many descriptions of COUNTIF on the web but I still can't
figure out what it means to have the condition field be a range of cells?

John Black

Remove Duplicates (Macro)
 

John,

In the case of SUMPRODUCT, the arguments are arrays. So even though COUNTIF only looks at one condition at a time, SUMPRODUCT will go through each cell in the array. You could think about it as though the formula was written as an array formula this way:

{=SUM(1/COUNTIF(A1:A4, A1:A4))}

Excel would look at the array above and translate it as:

1/COUNTIF(A1:A4, A1) +
1/COUNTIF(A1:A4, A2) +
1/COUNTIF(A1:A4, A3) +
1/COUNTIF(A1:A4, A4)

It might be easiest to see how the function works by using the "Evaluate Formula" button on the Formulas tab to step through the calculations one at a time.

Ben

That formula reports the matrix formed by every single result obtained using every cell of the range.

(1/COUNTIF(A1:A4,A1))+(1/COUNTIF(A1:A4,A2))+(1/COUNTIF(A1:A4,A3))+(1/COUNTIF(A1:A4,A4))

E.

Remove Duplicates (Macro)
 

Thanks for your answer. Sorry it took so long for my reply. My newsgroup
was not in Conversation mode. I did not realized people responded. Thanks
again. Works great.


"plinius" wrote in message ...

Il 13/10/2012 16:35, JCO ha scritto:
Using Excel 2007.
I need to be able to select a series of cells in a single column and
count the unique data (no duplicates). The existing cell format is
"General". Currently each cell is a hyperlink in the format shown below:
1-2323456
1-4345356
1-5454232
1-2323456

If these cells are selected, the answer should be 3 (because top and
bottom are the same).
Thanks


=SUMPRODUCT(1/COUNTIF(A1:A4,A1:A4))

E.