Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formulas
Is there a formula to find previous similar entries in a column?
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formulas
Response would depend in part on how you want to use this, but what I usually
do is create a formula in another column. Let's say you are looking at cell B32 and you want to know if there are any other cells in column B that have the same value. So create a column C and in C32 you would type (as an example): =IF(COUNTIF($B:$B,$B32)1,"DUPLICATE","") This formula could be copied/pasted or autofilled for other rows. Another way that would show the "sequence number" for the current entry (i.e. how many times it appears up to that point): =COUNTIF($B$1:$B32,$B32) If you truly mean "similar" instead of "exactly equal" that is tougher: how would you determine what is similar? But here is a variation that counts how many of the entries start with the same character: =COUNTIF($B$1:$B32,LEFT($B32,1)&"*") You could modify this for checking more characters or other types of pattern matching; see Excel help on that. -- - K Dales "dune" wrote: Is there a formula to find previous similar entries in a column? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formulas
"K Dales" wrote: Response would depend in part on how you want to use this, but what I usually do is create a formula in another column. Let's say you are looking at cell B32 and you want to know if there are any other cells in column B that have the same value. So create a column C and in C32 you would type (as an example): =IF(COUNTIF($B:$B,$B32)1,"DUPLICATE","") This formula could be copied/pasted or autofilled for other rows. Another way that would show the "sequence number" for the current entry (i.e. how many times it appears up to that point): =COUNTIF($B$1:$B32,$B32) If you truly mean "similar" instead of "exactly equal" that is tougher: how would you determine what is similar? But here is a variation that counts how many of the entries start with the same character: =COUNTIF($B$1:$B32,LEFT($B32,1)&"*") You could modify this for checking more characters or other types of pattern matching; see Excel help on that. -- - K Dales "dune" wrote: Is there a formula to find previous similar entries in a column? Thank you for your help, I tried it but in all cases the answer comes up as 1, it does not show the range of similar data. The idea is that I get the closest range in the temperature and humidity columns displayed. I do thank you for your clear explanation and hope you will have another look for me. So for instance if the temperature today is 20c, and the humidity is 50%, I need to get a list of similar conditions on previous days and weeks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
formulas
"dune" wrote: "K Dales" wrote: Response would depend in part on how you want to use this, but what I usually do is create a formula in another column. Let's say you are looking at cell B32 and you want to know if there are any other cells in column B that have the same value. So create a column C and in C32 you would type (as an example): =IF(COUNTIF($B:$B,$B32)1,"DUPLICATE","") This formula could be copied/pasted or autofilled for other rows. Another way that would show the "sequence number" for the current entry (i.e. how many times it appears up to that point): =COUNTIF($B$1:$B32,$B32) If you truly mean "similar" instead of "exactly equal" that is tougher: how would you determine what is similar? But here is a variation that counts how many of the entries start with the same character: =COUNTIF($B$1:$B32,LEFT($B32,1)&"*") You could modify this for checking more characters or other types of pattern matching; see Excel help on that. -- - K Dales "dune" wrote: Is there a formula to find previous similar entries in a column? Thank you for your help, I tried it but in all cases the answer comes up as 1, it does not show the range of similar data. The idea is that I get the closest range in the temperature and humidity columns displayed. I do thank you for your clear explanation and hope you will have another look for me. So for instance if the temperature today is 20c, and the humidity is 50%, I need to get a list of similar conditions on previous days and weeks. The question is this: I take weather and humidity readings in the morning on the day of an event, (plus a host of other readings) is there a way to type those readings into excel and search the data-base for a match or near match? Other readings are also taken and put into excel at the end of the day. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
E2007 formulas display as formulas, not results | Excel Worksheet Functions | |||
Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
AdvancedFilter on cells with formulas, returning values and not formulas | Excel Programming |