Glenn,

Sorry for the delay in responding. Just as I was attempting to get back to
work in the spreadsheet yesterday to include the new array you provided, I
encountered technical difficulties and have not been able to get back into
the document safely. As soon as the issue is resolved, I will try it and let
you know how it turns out.

Thanks!
--
heyredone


"Glenn" wrote:

heyredone wrote:
Glenn,
Thank you for your quick response. Okay, I tried the array string you
provided. I get $NUM in K2. What did I do wrong? Also, why am I entering
$C$2 or $C2 ... is that the cell C2? My little chart of data may not have
looked exactly right -- the in & out time punches are located starting in G2
and downward. Just trying to understand.

Thanks so much!

Not sure why you would get $NUM. The formula will expand to the right as far as
you want. If there is text in the CONVERTED column you get #VALUE!. If you use
exactly the data in your original post, what results do you get in H2:K2?

Yes, I am referencing column C in the formula. I assumed that the Badge number
would be unique for each employee. If that's not right, let me know.

Also, I forgot to include the date, assuming the same badge number will be in
your data for more than one date. Correction as follows (array-entered):

=IF(AND(SUMPRODUCT(($C$2:$C2=$C2)*($E$2:$E2=$E2))= 1,
SUMPRODUCT(($C$2:$C999=$C2)*($E$2:$E999=$E2))COLU MN()-8),
LARGE(($C$2:$C$999=$C2)*($E$2:$E$999=$E2)*$G$2:$G$ 999,
8+SUMPRODUCT(($C$2:$C999=$C2)*($E$2:$E999=$E2))-COLUMN()),"")

I have data starting in row 2 with column headings in row 1 as follows:

A - FirstName
B - LastName
C - Badge
D - Store
E - ScanDttm
F - RAW
G - CONVERTED

Columns H, I, J and K should be the new columns for your punch times. If that's
not right, let me know and I'll adjust the formula.