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!
--
heyredone


"Glenn" wrote:

Glenn wrote:
heyredone wrote:
Hello,

I have a very large amount of data (workday time punch data) that I
need to alter from multiple rows to multiple columns on one row. See
small example of data as follows where FirstName, LastName, Badge,
Store, ScanDttm, RAW, CONVERTED are column headings (A1 thru G1):

FirstName LastName Badge Store ScanDttm RAW CONVERTED
ROBERT H. CASAVAN 600265 1211 1/2/2004 28:00.0
9:28:00 AM
ROBERT H. CASAVAN 600265 1211 1/2/2004 57:00.0
2:57:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 24:00.0
3:24:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 03:00.0
5:03:00 PM
BRIAN BAUM 600760 1211 1/2/2004 36:00.0 9:36:00 AM
BRIAN BAUM 600760 1211 1/2/2004 05:00.0 3:05:00 PM
MICHAEL ANTOSIK 600820 1439 1/2/2004 52:00.0 9:52:00 AM
MICHAEL ANTOSIK 600820 1439 1/2/2004 06:00.0 1:06:00 PM

I want to take the CONVERTED data (time punch in & out time) for
ROBERT H. CASAVAN on 1/2/04 (ScanDttm) and spread those 4 in & out
time entries into 1 row and multiple columns. Then the same for BRIAN
BAUM, and the same for MICHAEL ANTOSIK, etc. Each individual will
have from 1 to 4 in & out entries on a given day.

Is there a way to accomplish this without copying and transposing each
set of in & out time entries for each person and each day separately?
If so, how do I do that?

Thank you!


Array enter (CTRL+SHIFT+ENTER) the following in H2 (adjusting the 9999
to the correct number of rows in your data):

=IF(AND(COUNTIF($C$2:$C2,$C2)=1,COUNTIF($C$2:$C$99 99,$C2)COLUMN()-8),
LARGE(TRANSPOSE(($C$2:$C$9999=$C2)*$G$2:$G$9999),8 +COUNTIF($C$2:$C$9999,$C2)-COLUMN()),"")


Copy across to K2 and down as needed.


Looks like the TRANSPOSE wasn't needed:

=IF(AND(COUNTIF($C$2:$C2,$C2)=1,COUNTIF($C$2:$C$99 9,$C2)COLUMN()-8),
LARGE(($C$2:$C$999=$C2)*$G$2:$G$999,8+COUNTIF($C$2 :$C$999,$C2)-COLUMN()),"")