Just not getting it right

[swaink]

Hi All

I wonder someone could let me know where it is I am going wrong

I am conductiong analysis at present on quite a large sample of data and am trying to speed this up with the aid of the odd formula.

I have the following header in five columns
Date/Time Code wk ref staus

In these columns are
A (Data time values 06/07/2011 14:06:01)
B (alpha code and char A to Z)
C (Sector 1 to 100)
REF (always four chars)
STATUS (numberic values only ever a 1 or 2)

I am applying a formula to column F which looks like this
=IF(SUMPRODUCT(--(A2=$A$2:$A$16800),--(C2=$C$2:$C$16800)--(E2=$E$2:$E16800),"chk","")

The data is sorted by A,B, what I am attempting to achieve is that if the date/time and the sector and the status are the same then result is "chk" else leave blank.

In the sample below I would expect to see "chk" for every row that contains 21/06/2011 14:51:01 but all of the other rows would be blank

I just can't get the formula to work and eventually reached the conclusion I need some help with this one please

06/07/2011 14:06:01 A 1 AA05 01
08/07/2011 13:43:01 A 1 AA05 01
08/06/2011 14:06:01 A 61 AA06 01
21/06/2011 14:51:01 B 60 AA07 01
21/06/2011 14:51:01 B 60 AA07 01
21/06/2011 14:51:01 B 60 AA07 01
21/06/2011 14:51:01 B 60 AA07 01
21/06/2011 14:51:01 B 60 AA07 01
28/07/2011 12:05:01 C 1 AA09 01

All the best

Kevin

 

[kpark91]

Awesome. Except I don't want to read this long post and solve a riddle in what to do.

So, you want "chk" in every row that has 21/06/2011 14:51:01 (colA) in column F.
Is that correct?

 

[swaink]

Sorry point taken

No not correct I must see the same date/time and the same values in column C and the same values in column E to apply the "chk"

Col A = 21/06/2011 14:51:01
Col C = 60
Col E = 01

 

[kpark91]

Why don't you just go with something simple formatting everything as texts?

=IF(AND(A1 = "21/06/2011 14:51:01", C1 = "60", E1 = "01"), "chk", "")

 

[swaink]

kpark91

I don't want it hard coded, this was merely an example of what I was expecting to see

There will be many occasions where there will be other date times etc where there are multiple entries of the same information and I need the formula to identify these for me rather then picking my way through some 30 thousand rows of data

Kevin

 

[kpark91]

Not too sure how to do that in one go
but you could make a column with count of same information and sort it.