| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function for repeated cells
hi
I have a spreedsheet that has cells that are repeated amongst others I want to write a function that will identify these. if column A has this data in it how can I pick up the identical cell in Column B DMM9844 DMM9844 1 - this what I want in col B. DMM9756 DMM6456 1 DMM6747 DMM3463 DMM4574 i think it may be a sumif function but i can't get it to work. Can anybody help? Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function for repeated cells
Are the repeated values repeated only once?
Will you ever have this: DMM9844 DMM9844 DMM9844 DMM9844 DMM9756 DMM6747 What result would expect on the above data? Biff "Haz" wrote in message ... hi I have a spreedsheet that has cells that are repeated amongst others I want to write a function that will identify these. if column A has this data in it how can I pick up the identical cell in Column B DMM9844 DMM9844 1 - this what I want in col B. DMM9756 DMM6456 1 DMM6747 DMM3463 DMM4574 i think it may be a sumif function but i can't get it to work. Can anybody help? Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function for repeated cells
Hi, if b2 is the start of your data then use, from c2 =countif($b$2:$b$XXX,b2)-1 where xxx is the bottom row there are variants but this should tell you beside every value how many times it occurs elsewhere Cheers Dazza ------------------------------------------------------------ Please register if this has been helpful. "Haz" wrote: hi I have a spreedsheet that has cells that are repeated amongst others I want to write a function that will identify these. if column A has this data in it how can I pick up the identical cell in Column B DMM9844 DMM9844 1 - this what I want in col B. DMM9756 DMM6456 1 DMM6747 DMM3463 DMM4574 i think it may be a sumif function but i can't get it to work. Can anybody help? Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function for repeated cells
hi
yes it is possible that I have 2 of more cells that are the same, and want to be able to see all these repeated cells in column B only. Like the following: DMM9844 1 DMM9844 1 DMM9844 1 DMM9844 1 DMM9756 DMM6747 DMM5555 1 DMM5555 1 Is there a function that could return this? "T. Valko" wrote: Are the repeated values repeated only once? Will you ever have this: DMM9844 DMM9844 DMM9844 DMM9844 DMM9756 DMM6747 What result would expect on the above data? Biff "Haz" wrote in message ... hi I have a spreedsheet that has cells that are repeated amongst others I want to write a function that will identify these. if column A has this data in it how can I pick up the identical cell in Column B DMM9844 DMM9844 1 - this what I want in col B. DMM9756 DMM6456 1 DMM6747 DMM3463 DMM4574 i think it may be a sumif function but i can't get it to work. Can anybody help? Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function for repeated cells
Try this:
Assume range of data in column A is A1:A10. Enter this formula in B1 and copy down as needed: =IF(COUNTIF(A$1:A$10,A1)1,1,"") Biff "Haz" wrote in message ... hi yes it is possible that I have 2 of more cells that are the same, and want to be able to see all these repeated cells in column B only. Like the following: DMM9844 1 DMM9844 1 DMM9844 1 DMM9844 1 DMM9756 DMM6747 DMM5555 1 DMM5555 1 Is there a function that could return this? "T. Valko" wrote: Are the repeated values repeated only once? Will you ever have this: DMM9844 DMM9844 DMM9844 DMM9844 DMM9756 DMM6747 What result would expect on the above data? Biff "Haz" wrote in message ... hi I have a spreedsheet that has cells that are repeated amongst others I want to write a function that will identify these. if column A has this data in it how can I pick up the identical cell in Column B DMM9844 DMM9844 1 - this what I want in col B. DMM9756 DMM6456 1 DMM6747 DMM3463 DMM4574 i think it may be a sumif function but i can't get it to work. Can anybody help? Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function for repeated cells
That has worked perfectly
Many thanks for your help "T. Valko" wrote: Try this: Assume range of data in column A is A1:A10. Enter this formula in B1 and copy down as needed: =IF(COUNTIF(A$1:A$10,A1)1,1,"") Biff "Haz" wrote in message ... hi yes it is possible that I have 2 of more cells that are the same, and want to be able to see all these repeated cells in column B only. Like the following: DMM9844 1 DMM9844 1 DMM9844 1 DMM9844 1 DMM9756 DMM6747 DMM5555 1 DMM5555 1 Is there a function that could return this? "T. Valko" wrote: Are the repeated values repeated only once? Will you ever have this: DMM9844 DMM9844 DMM9844 DMM9844 DMM9756 DMM6747 What result would expect on the above data? Biff "Haz" wrote in message ... hi I have a spreedsheet that has cells that are repeated amongst others I want to write a function that will identify these. if column A has this data in it how can I pick up the identical cell in Column B DMM9844 DMM9844 1 - this what I want in col B. DMM9756 DMM6456 1 DMM6747 DMM3463 DMM4574 i think it may be a sumif function but i can't get it to work. Can anybody help? Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function for repeated cells
You're welcome. Thanks for the feedback!
Biff "Haz" wrote in message ... That has worked perfectly Many thanks for your help "T. Valko" wrote: Try this: Assume range of data in column A is A1:A10. Enter this formula in B1 and copy down as needed: =IF(COUNTIF(A$1:A$10,A1)1,1,"") Biff "Haz" wrote in message ... hi yes it is possible that I have 2 of more cells that are the same, and want to be able to see all these repeated cells in column B only. Like the following: DMM9844 1 DMM9844 1 DMM9844 1 DMM9844 1 DMM9756 DMM6747 DMM5555 1 DMM5555 1 Is there a function that could return this? "T. Valko" wrote: Are the repeated values repeated only once? Will you ever have this: DMM9844 DMM9844 DMM9844 DMM9844 DMM9756 DMM6747 What result would expect on the above data? Biff "Haz" wrote in message ... hi I have a spreedsheet that has cells that are repeated amongst others I want to write a function that will identify these. if column A has this data in it how can I pick up the identical cell in Column B DMM9844 DMM9844 1 - this what I want in col B. DMM9756 DMM6456 1 DMM6747 DMM3463 DMM4574 i think it may be a sumif function but i can't get it to work. Can anybody help? Thanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
function for repeated cells
Try this:
Assume range of data in column A is A1:A10. Enter this formula in B1 and copy down as needed: =IF(COUNTIF(A$1:A$10,A1)1,1,"") A minor change the OP may want to consider is this... =IF(COUNTIF(A$1:A$10,A1)1,COUNTIF(A$1:A$10,A1),"" ) which will show the total number of repeats (if there are 2 or more) instead of just a 1... having the count may make whatever the OP is doing with the duplicates easier as he will know how many need to be processed in total. Rick |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding repeated data in adjacent cells | Excel Discussion (Misc queries) | |||
| Printing text in a repeated cell/row that is longer than repeated | Excel Discussion (Misc queries) | |||
| Deleting cells with repeated data | Excel Worksheet Functions | |||
| Delete repeated Cells | Excel Worksheet Functions | |||
| Repeated row | Excel Discussion (Misc queries) | |||
Linear Mode
