Match, VLookup... who knows?

OK, from Access, I have a table that is exported into an Excel Workbook.
This workbook, an attendance log, is formatted as follows:

Child1 TimeIn TimeOut Date
Child2 TimeIn TimeOut Date
Child3 TimeIn TimeOut Date
Child1 " " "
Child2 " " "
Child3 " " "

I have linked a completely different Excel Workbook to this workbook, and
want to change the format to:

Child1 Child2 Child 3
Date TimeIn TimeOut TimeIn TimeOut TimeIn TimeOut
Date TimeIn TimeOut TimeIn TimeOut TimeIn TimeOut

How do I search the export from access to match BOTH the Date and the Child
to find the TimeIn and TimeOut?

How do I search ... to match BOTH the Date and the Child
to find the TimeIn and TimeOut?

At the core of it, a multiple criteria index/match will serve your intents.
A one-time prep effort is required to conform it to the desired results
layout, but you can use EditReplace to help you on this

Assume the source attendance data is in sheet: x, cols A to D (as you posted)
Assume the dates in col D are real dates recognized by Excel

In your results sheet,
you have real dates in A2 down, matching names in B1 across (as you posted)
place this in B2, normal ENTER will do:
=INDEX(x!B$2:B$1000,MATCH(1,INDEX((x!$A$2:$A$1000= $B$1)*(x!$D$2:$D$1000=$A2),),0))
Modify the ranges to suit the extents of your source data. Copy B2 to C2.
B2:C2 returns the TimeIn/TimeOut for the childname specified in B1, for the
date in A2. Copy B2:C2, paste into D2:E2, F2:G2, and so on. Then use
EditReplace to change the point to the childname ie: $B$1 to $D$1, $F$1, and
so on (a one-time 5 minutes effort). Upon completion, just select the entire
row from B2 across (eg select B2:IV2) and just drag to copy down all the way.
Success? hit the YES below
--
Max
Singapore
---
"coconut78" wrote:
OK, from Access, I have a table that is exported into an Excel Workbook.
This workbook, an attendance log, is formatted as follows:

Child1 TimeIn TimeOut Date
Child2 TimeIn TimeOut Date
Child3 TimeIn TimeOut Date
Child1 " " "
Child2 " " "
Child3 " " "

I have linked a completely different Excel Workbook to this workbook, and
want to change the format to:

Child1 Child2 Child 3
Date TimeIn TimeOut TimeIn TimeOut TimeIn TimeOut
Date TimeIn TimeOut TimeIn TimeOut TimeIn TimeOut

How do I search the export from access to match BOTH the Date and the Child
to find the TimeIn and TimeOut?

Dude, you so totally rock!

One thing for others to note: when typing the formula... actually use the
A1:A1000 method instead of A:A.... for some reason, the A:A method doesn't
work... found this out by not taking the free advice given to me by Max.

Thanks Max!

"Max" wrote:

How do I search ... to match BOTH the Date and the Child
to find the TimeIn and TimeOut?

At the core of it, a multiple criteria index/match will serve your intents.
A one-time prep effort is required to conform it to the desired results
layout, but you can use EditReplace to help you on this

Assume the source attendance data is in sheet: x, cols A to D (as you posted)
Assume the dates in col D are real dates recognized by Excel

In your results sheet,
you have real dates in A2 down, matching names in B1 across (as you posted)
place this in B2, normal ENTER will do:
=INDEX(x!B$2:B$1000,MATCH(1,INDEX((x!$A$2:$A$1000= $B$1)*(x!$D$2:$D$1000=$A2),),0))
Modify the ranges to suit the extents of your source data. Copy B2 to C2.
B2:C2 returns the TimeIn/TimeOut for the childname specified in B1, for the
date in A2. Copy B2:C2, paste into D2:E2, F2:G2, and so on. Then use
EditReplace to change the point to the childname ie: $B$1 to $D$1, $F$1, and
so on (a one-time 5 minutes effort). Upon completion, just select the entire
row from B2 across (eg select B2:IV2) and just drag to copy down all the way.
Success? hit the YES below
--
Max
Singapore
---
"coconut78" wrote:
OK, from Access, I have a table that is exported into an Excel Workbook.
This workbook, an attendance log, is formatted as follows:

Child1 TimeIn TimeOut Date
Child2 TimeIn TimeOut Date
Child3 TimeIn TimeOut Date
Child1 " " "
Child2 " " "
Child3 " " "

I have linked a completely different Excel Workbook to this workbook, and
want to change the format to:

Child1 Child2 Child 3
Date TimeIn TimeOut TimeIn TimeOut TimeIn TimeOut
Date TimeIn TimeOut TimeIn TimeOut TimeIn TimeOut

How do I search the export from access to match BOTH the Date and the Child
to find the TimeIn and TimeOut?

Welcome, glad you got it up over there

As for your comment ...
.. actually use the A1:A1000 method instead of A:A ...
it was implicit in the response that simpler entire cols (A:A) cannot be
used, otherwise I would have used it (entire cols)
--
Max
Singapore
-----
"coconut78" wrote in message
...
Dude, you so totally rock!

One thing for others to note: when typing the formula... actually use the
A1:A1000 method instead of A:A.... for some reason, the A:A method doesn't
work... found this out by not taking the free advice given to me by Max.

Thanks Max!