Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Duplicates (Macro)
|
#5
|
|||
|
|||
Quote:
So that the sum report the total number of different strings. E. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
|
|||
|
|||
Quote:
(1/COUNTIF(A1:A4,A1))+(1/COUNTIF(A1:A4,A2))+(1/COUNTIF(A1:A4,A3))+(1/COUNTIF(A1:A4,A4)) E. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove duplicates | Excel Programming | |||
How to remove row duplicates | Excel Discussion (Misc queries) | |||
remove duplicates macro ending sub | Excel Programming | |||
remove duplicates | Excel Programming |