• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count unique data of a column

P

Pampos

Guest
Hello. I have a column with words (no numbers). Some words are the same. How can I find the number of the diferrent word I have on the column?

For example,

CAT
CAT
DOG
MOUSE
CAT
HORSE
HORSE

I want to find number 4, becauce I have 4 diferrent words: CAT, DOG, MOUSE, HORSE. I prefer to use formula than a filter if it is possible.
 
Dear Pampos

This array formula should do the trick (it needs to be entered using ctrl+shift+enter) assuming the words are in A1:A7

=SUM(1*((COUNTIF($A$1:$A$7,$A$1:$A$7))=1))
 
Thx both. The second solution works

=SUMPRODUCT(1/COUNTIF(A3:A7;A3:A7))

Is it possible to have something like
=SUMPRODUCT(1/COUNTIF(A:A;A:A)) ? Is not working... I don't want everytime I put something new to change A3:A7.
 
Hi ,

In general it is not good practice to use entire column references ; it is always better to create dynamic named ranges , so that you can use something like :

=SUMPRODUCT(1/COUNTIF(List,List))

It is better also to use fixed references , but make them large enough to accommodate present and future data , and modify the above formula to :

=SUMPRODUCT(IFERROR(1/COUNTIF($A1:$A1000,$A1:$A1000),0))

Because of the IFERROR inside , the formula will have to be entered as an array formula , using CTRL SHIFT ENTER. Of course , you can use the normal :

=SUM(IFERROR(1/COUNTIF($B1:$B1000,$B1:$B1000),0))

entered as an array formula , using CTRL SHIFT ENTER.

Narayan
 
Pampos said:
Thx both. The second solution works

=SUMPRODUCT(1/COUNTIF(A3:A7;A3:A7))

Is it possible to have something like
=SUMPRODUCT(1/COUNTIF(A:A;A:A)) ? Is not working... I don't want everytime I put something new to change A3:A7.
Click to expand...
Hi,

Using full columns is a bad idea because it can make the worksheet very slow to calculate. In addition the formula you posted will give an error because it doesn't ignore blanks. I would suggest something like this, if 5000 rows isn't enough then use more but avoid full columns.

Note I use comma(,) as a delimiter so change to your ;


=SUMPRODUCT((A1:A5000<>"")/COUNTIF(A1:A5000,A1:A5000&""))
 
Hi,

I would suggest you to try FREQUENCY formula inside SUM function as shown below and test both the formulas on your data set, I think using an array as criteria inside any IF or IFS function (SUMIF,COUNTIF,AVERAGEIF) slow down the calculation time.

=SUM(IF(FREQUENCY(MATCH($C$1:$C$7,$C$1:$C$7,0),MATCH($C$1:$C$7,$C$1:$C$7,0)),1,0))

Enter with Ctrl+Shift+Enter. Here your sample data is in C1:C7 which can be replaced with a dynamic list.

Regards,
 
No array formulas in this solution. I use them frequently but I like to avoid them when I can.
 

Attachments

  • Duplicates.xlsx
    10.5 KB · Views: 23
Hi XLPadawan,

Nice way.. :)

BTW.. did you tried.. by filling any of the word.. more than 8 times (your fixed count of any number in the attachment..)

and just for curiosity.. Which one you want to avoid more.. "Array Formula" or "Using lots of Helper Cell"
 
Debraj said:
Hi XLPadawan,

Nice way.. :)

BTW.. did you tried.. by filling any of the word.. more than 8 times (your fixed count of any number in the attachment..)

and just for curiosity.. Which one you want to avoid more.. "Array Formula" or "Using lots of Helper Cell"
Click to expand...

Debraj,
When helping someone else out I prefer to use lots of helper cells because the logic is easier for someone to follow. I could have used a MAX of the counts and extended the match counts row automatically to the right as far as I needed, but this would have needlessly added extra layers of difficulty for someone just needing the basic logic to my approach.

BTW, is sarcasm the best way to respond to someone who is trying to help someone else out? I doubt it because I get the feeling you Excel Ninjas really are here to help those of us who need it. Thank you for all you do.
 
NARAYANK991 said:
Hi ,

In general it is not good practice to use entire column references ; it is always better to create dynamic named ranges , so that you can use something like :

=SUMPRODUCT(1/COUNTIF(List,List))

It is better also to use fixed references , but make them large enough to accommodate present and future data , and modify the above formula to :

=SUMPRODUCT(IFERROR(1/COUNTIF($A1:$A1000,$A1:$A1000),0))

Because of the IFERROR inside , the formula will have to be entered as an array formula , using CTRL SHIFT ENTER. Of course , you can use the normal :

=SUM(IFERROR(1/COUNTIF($B1:$B1000,$B1:$B1000),0))

entered as an array formula , using CTRL SHIFT ENTER.

Narayan
Click to expand...

Hello Narayan Sir,

What is the formula to get unique list from
CAT
CAT
DOG
MOUSE
CAT
HORSE
HORSE

to CAT, DOG, MOUSE, HORSE.

Please help.
 
NARAYANK991 said:
Hi Zameer ,

The most popular blog on this is :

http://www.get-digital-help.com/200...-and-the-duplicates-in-excel-from-one-column/

You will get all the information you want on extracting unique items from lists.

Narayan
Click to expand...

Hello Narayan Sir,

Please see the attached file. Log in with the user Zameer Shaikh & Password is JHSHAIKH. My need is that in IssueControlRegister sheet i want to generate gate pass number automatically in Gate Pass No. colomn with the following condition.

01. Gate Pass Number generate will be same if Issue Voucher No. & Unit Code are same.

02. IF Unit Code are 01 & 02 Gate Pass No. does not generate.

It is possible sir,

Regards,

Zameer Shaikh
 

Attachments

  • SIMS.xlsm
    790.2 KB · Views: 15
NARAYANK991 said:
Hi Zameer ,

Sorry , but I have not yet found the time to look into your problem. Please give me some more time.

Narayan
Click to expand...
Hello Sir,

You not to be sorry Sir. I'll wait for your reply.

But please look into my problem.

Warm Regards,


Zameer Shaikh
 
Hi Zameer ,

See this file ; when you are sure the code works the way you want , you can transfer the code to your working file.

Narayan
 

Attachments

  • SIMS_To_Be_Checked.xlsm
    789.7 KB · Views: 6
NARAYANK991 said:
Hi Zameer ,

See this file ; when you are sure the code works the way you want , you can transfer the code to your working file.

Narayan
Click to expand...

Hello Narayan Sir,

I checked the file. That code is not working the way i want. It is not generating the number as per start number from T3. If i enter 100 in t3 then it should be generate the gate pass number from 100 to increased by tracing the IV Number & Unit code. For example:

If IV No.300 & Unit Code is 05 repeat upto 3 times gate pass number should be 100 up to row found [t3:t5]. Then if he found IV No.301 & Unit Code 01 or 02 up to 2 times gate pass should not be generate up to [t6:t7]. If he found IV No.302 & Unit Code 15 up to 2 time gate pass number should be 101 generate.

Remember IV No. & Unit Code whatever it may be, gate pass number increament from last gate pass number generate.

Warm Regards,


Zameer Shaikh
 
Hi Zameer ,

Can you say what will be the data in the Gatepass cell where the Unitcode is either 01 or 02 ? Will it be blank ?

Narayan
 
You must log in or register to reply here.
Back
Top