formulas
 

Is there a formula to find previous similar entries in a column?

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?

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.

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.

formulas
 

Haven't done this before. Have two sheets want to combine if possible and
discard duplicates. Possible?
I don't know.
Thanks


"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?