Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match, VLookup... who knows?
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match, VLookup... who knows?
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match, VLookup... who knows?
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
second or third match in vlookup() or Match() | Excel Worksheet Functions | |||
index Match, or Vlookup Match.. | Excel Worksheet Functions |