| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Duplicates in text values
I have 19 digit text values in column A from A2 to A501.
Col A Col B 8991151420000560945 1 8991151420000560721 1 8991151420000560770 1 8991151420000560770 2 8991151420000560861 1 8991151420000560929 1 8991151420000561125 1 and so on. I need to count duplicate text for each cell to be desplayed in adjecent cell (Col B). Need help with the formula as countif is not working in this situation. Regards, Madiya |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Duplicates in text values
Hi Madiya,
Put following formula in cell B2 and then drag =COUNTIF($A$2:A2,A2) H S Shastri ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++ "Madiya" wrote: I have 19 digit text values in column A from A2 to A501. Col A Col B 8991151420000560945 1 8991151420000560721 1 8991151420000560770 1 8991151420000560770 2 8991151420000560861 1 8991151420000560929 1 8991151420000561125 1 and so on. I need to count duplicate text for each cell to be desplayed in adjecent cell (Col B). Need help with the formula as countif is not working in this situation. Regards, Madiya |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Duplicates in text values
Madiya,
Try using an anchored range: =COUNTIF($A$2:A2,A2) copied down. HTH, Bernie MS Excel MVP "Madiya" wrote in message ... I have 19 digit text values in column A from A2 to A501. Col A Col B 8991151420000560945 1 8991151420000560721 1 8991151420000560770 1 8991151420000560770 2 8991151420000560861 1 8991151420000560929 1 8991151420000561125 1 and so on. I need to count duplicate text for each cell to be desplayed in adjecent cell (Col B). Need help with the formula as countif is not working in this situation. Regards, Madiya |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Duplicates in text values
Hi Madiya
Try this: =SUMPRODUCT(--($A$2:$A$501=A2)) HTH John "Madiya" wrote in message ... I have 19 digit text values in column A from A2 to A501. Col A Col B 8991151420000560945 1 8991151420000560721 1 8991151420000560770 1 8991151420000560770 2 8991151420000560861 1 8991151420000560929 1 8991151420000561125 1 and so on. I need to count duplicate text for each cell to be desplayed in adjecent cell (Col B). Need help with the formula as countif is not working in this situation. Regards, Madiya |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Duplicates in text values
Hi,
This depends on what you mean by duplicates, but I suspect this is what you want: =COUNTIF(A$2:A2,A2) but the other option is: =COUNTIF(A$2:A$501,A2) In 2007 you can conditionally format to mark duplicates of the formula II type and you can issue a new command to remove duplicates as defind in formula I above. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Madiya" wrote: I have 19 digit text values in column A from A2 to A501. Col A Col B 8991151420000560945 1 8991151420000560721 1 8991151420000560770 1 8991151420000560770 2 8991151420000560861 1 8991151420000560929 1 8991151420000561125 1 and so on. I need to count duplicate text for each cell to be desplayed in adjecent cell (Col B). Need help with the formula as countif is not working in this situation. Regards, Madiya |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Duplicates in text values
On Feb 16, 8:45*pm, Shane Devenshire
wrote: Hi, This depends on what you mean by duplicates, but I suspect this is what you want: =COUNTIF(A$2:A2,A2) but the other option is: =COUNTIF(A$2:A$501,A2) In 2007 you can conditionally format to mark duplicates of the formula II type and you can issue a new command to remove duplicates as defind in formula I above. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Madiya" wrote: I have 19 digit text values in column A from A2 to A501. Col A * * * * * * * * * * * * * * * * * * Col B 8991151420000560945 * * * * * * 1 8991151420000560721 * * * * * * 1 8991151420000560770 * * * * * * 1 8991151420000560770 * * * * * * 2 8991151420000560861 * * * * * * 1 8991151420000560929 * * * * * * 1 8991151420000561125 * * * * * * 1 and so on. I need to count duplicate text for each cell to be desplayed in adjecent cell (Col B). Need help with the formula as countif is not working in this situation. Regards, Madiya- Hide quoted text - - Show quoted text - Hi all, Thanks for your response. I have alrady tried =COUNTIF(A$2:A$501,A2) and other variance. Since the text is a 19 digit value, I think excel goofs up the calculations and shows wrong result. Thats the reason I have posted here to get help. Jhon, Your formula =SUMPRODUCT(--($A$2:$A$501=A2)) works fine and results are as expected. Thanks aagain for all of your help and time. Regards, Madiya |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Duplicates in text values
You're welcome
John "Madiya" wrote in message ... On Feb 16, 8:45 pm, Shane Devenshire wrote: Hi, This depends on what you mean by duplicates, but I suspect this is what you want: =COUNTIF(A$2:A2,A2) but the other option is: =COUNTIF(A$2:A$501,A2) In 2007 you can conditionally format to mark duplicates of the formula II type and you can issue a new command to remove duplicates as defind in formula I above. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Madiya" wrote: I have 19 digit text values in column A from A2 to A501. Col A Col B 8991151420000560945 1 8991151420000560721 1 8991151420000560770 1 8991151420000560770 2 8991151420000560861 1 8991151420000560929 1 8991151420000561125 1 and so on. I need to count duplicate text for each cell to be desplayed in adjecent cell (Col B). Need help with the formula as countif is not working in this situation. Regards, Madiya- Hide quoted text - - Show quoted text - Hi all, Thanks for your response. I have alrady tried =COUNTIF(A$2:A$501,A2) and other variance. Since the text is a 19 digit value, I think excel goofs up the calculations and shows wrong result. Thats the reason I have posted here to get help. Jhon, Your formula =SUMPRODUCT(--($A$2:$A$501=A2)) works fine and results are as expected. Thanks aagain for all of your help and time. Regards, Madiya |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Employee Work Time - Don't Count Duplicates | Excel Worksheet Functions | |||
| How can I count up values in a list that includes duplicates? | Excel Discussion (Misc queries) | |||
| Count Unique Values In A Filtered Row with Duplicates | Excel Discussion (Misc queries) | |||
| Count unique values among duplicates in a subtotal range | Excel Discussion (Misc queries) | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Linear Mode
