Hi and good evening, can anybody help me with their best approach to the following scenario.
I have a shared workbook (4 people) with 9 worksheets named "Dept1" - "Dept8" (Column Headings Identical) and 1 named "Summary". 25 rows of data approx. will be added to the Dept. worksheets on a daily basis and the Summary worksheet will hold the extracted data.I would like to extract a unique date/time value from the "TimeStamp" Column from Dept1 - Dept8 in oldest to newest order to the Summary worksheet like the expected results on the data set below.I can do this in Power Query (Super Add-In) no problem thanks to Sandy666 from my last post but this time it has to be in a formula method.
I'm using Microsoft Office Home And Business 2013.Timestamp column is formatted to "ddd-dd-mmm-y h:mm am/pm.
Your help would be greatly appreciated.
Sample data Set.
<tbody>
</tbody>
Expected Results.
<tbody>
</tbody>
Thanking You, Martin.
I have a shared workbook (4 people) with 9 worksheets named "Dept1" - "Dept8" (Column Headings Identical) and 1 named "Summary". 25 rows of data approx. will be added to the Dept. worksheets on a daily basis and the Summary worksheet will hold the extracted data.I would like to extract a unique date/time value from the "TimeStamp" Column from Dept1 - Dept8 in oldest to newest order to the Summary worksheet like the expected results on the data set below.I can do this in Power Query (Super Add-In) no problem thanks to Sandy666 from my last post but this time it has to be in a formula method.
I'm using Microsoft Office Home And Business 2013.Timestamp column is formatted to "ddd-dd-mmm-y h:mm am/pm.
Your help would be greatly appreciated.
Sample data Set.
System | Size | Production | Material | Batch | TimeStamp |
Machine 1 | Small | AX-1001 | AMX-25879 | ZXP-0001260 | Sat-26-Oct-19 2:09 AM |
Machine 2 | Large | AX-1007 | AMX-25885 | ZXP-0001268 | Sat-26-Oct-19 2:38 PM |
Machine 1 | Large | AX-1012 | AMX-25890 | ZXP-0001261 | Sat-26-Oct-19 8:52 PM |
Dept1 | |||||
Machine 3 | Large | AX-1002 | AMX-25880 | ZXP-0001258 | Sun-27-Oct-19 7:40 AM |
Machine 2 | X-Large | AX-1015 | AMX-25893 | ZXP-0001271 | Sun-27-Oct-19 1:55 AM |
Machine 3 | X-Large | AX-1016 | AMX-25894 | ZXP-0001272 | Sat-26-Oct-19 2:52 AM |
Dept2 | |||||
Machine 5 | Large | AX-1008 | AMX-25886 | ZXP-0001264 | Sat-26-Oct-19 6:00 PM |
Machine 6 | Medium | AX-1032 | AMX-25910 | ZXP-0001288 | Sat-26-Oct-19 6:43 AM |
Machine 6 | X-Large | AX-1031 | AMX-25909 | ZXP-0001287 | Sun-27-Oct-19 7:55 PM |
Dept3 | |||||
Machine 7 | Large | AX-1023 | AMX-25901 | ZXP-0001279 | Sun-27-Oct-19 3:07 PM |
Machine 7 | Small | AX-1030 | AMX-25908 | ZXP-0001286 | Sun-27-Oct-19 2:09 AM |
Machine 8 | Small | AX-1026 | AMX-25904 | ZXP-0001282 | Sat-26-Oct-19 7:40 PM |
Dept4 | |||||
Machine 9 | Large | AX-1017 | AMX-25895 | ZXP-0001273 | Sun-27-Oct-19 6:57 PM |
Machine 9 | Medium | AX-1028 | AMX-25906 | ZXP-0001284 | Sat-26-Oct-19 8:38 AM |
Machine 9 | Large | AX-1009 | AMX-25887 | ZXP-0001265 | Sat-26-Oct-19 3:36 AM |
Dept5 | |||||
Machine 11 | Medium | AX-1027 | AMX-25905 | ZXP-0001283 | Sat-26-Oct-19 2:09 PM |
Machine 11 | Medium | AX-1005 | AMX-25883 | ZXP-0001261 | Sat-26-Oct-19 11:31 AM |
Machine 12 | Medium | AX-1019 | AMX-25897 | ZXP-0001275 | Sun-27-Oct-19 2:38 AM |
Dept6 | |||||
Machine 14 | Small | AX-1003 | AMX-25881 | ZXP-0001259 | Sun-27-Oct-19 6:00 PM |
Machine 14 | Medium | AX-1022 | AMX-25900 | ZXP-0001278 | Sat-26-Oct-19 11:02 PM |
Machine 14 | Large | AX-1024 | AMX-25902 | ZXP-0001280 | Sun-27-Oct-19 3:36 AM |
Dept7 | |||||
Machine 15 | Large | AX-1020 | AMX-25898 | ZXP-0001276 | Sun-27-Oct-19 10:48 AM |
Machine 15 | Medium | AX-1010 | AMX-25888 | ZXP-0001266 | Sun-27-Oct-19 12:28 PM |
Machine 16 | X-Large | AX-1014 | AMX-25892 | ZXP-0001270 | Sat-26-Oct-19 4:48 AM |
Dept8 |
<tbody>
</tbody>
Expected Results.
System | Size | Production | Material | Batch | TimeStamp |
Machine 1 | Small | AX-1001 | AMX-25879 | ZXP-0001260 | Sat-26-Oct-19 2:09 AM |
Machine 3 | X-Large | AX-1016 | AMX-25894 | ZXP-0001272 | Sat-26-Oct-19 2:52 AM |
Machine 9 | Large | AX-1009 | AMX-25887 | ZXP-0001265 | Sat-26-Oct-19 3:36 AM |
Machine 16 | X-Large | AX-1014 | AMX-25892 | ZXP-0001270 | Sat-26-Oct-19 4:48 AM |
Machine 6 | Medium | AX-1032 | AMX-25910 | ZXP-0001288 | Sat-26-Oct-19 6:43 AM |
Machine 9 | Medium | AX-1028 | AMX-25906 | ZXP-0001284 | Sat-26-Oct-19 8:38 AM |
Machine 11 | Medium | AX-1005 | AMX-25883 | ZXP-0001261 | Sat-26-Oct-19 11:31 AM |
Machine 11 | Medium | AX-1027 | AMX-25905 | ZXP-0001283 | Sat-26-Oct-19 2:09 PM |
Machine 2 | Large | AX-1007 | AMX-25885 | ZXP-0001268 | Sat-26-Oct-19 2:38 PM |
Machine 5 | Large | AX-1008 | AMX-25886 | ZXP-0001264 | Sat-26-Oct-19 6:00 PM |
Machine 8 | Small | AX-1026 | AMX-25904 | ZXP-0001282 | Sat-26-Oct-19 7:40 PM |
Machine 1 | Large | AX-1012 | AMX-25890 | ZXP-0001261 | Sat-26-Oct-19 8:52 PM |
Machine 14 | Medium | AX-1022 | AMX-25900 | ZXP-0001278 | Sat-26-Oct-19 11:02 PM |
Machine 2 | X-Large | AX-1015 | AMX-25893 | ZXP-0001271 | Sun-27-Oct-19 1:55 AM |
Machine 7 | Small | AX-1030 | AMX-25908 | ZXP-0001286 | Sun-27-Oct-19 2:09 AM |
Machine 12 | Medium | AX-1019 | AMX-25897 | ZXP-0001275 | Sun-27-Oct-19 2:38 AM |
Machine 14 | Large | AX-1024 | AMX-25902 | ZXP-0001280 | Sun-27-Oct-19 3:36 AM |
Machine 3 | Large | AX-1002 | AMX-25880 | ZXP-0001258 | Sun-27-Oct-19 7:40 AM |
Machine 15 | Large | AX-1020 | AMX-25898 | ZXP-0001276 | Sun-27-Oct-19 10:48 AM |
Machine 15 | Medium | AX-1010 | AMX-25888 | ZXP-0001266 | Sun-27-Oct-19 12:28 PM |
Machine 7 | Large | AX-1023 | AMX-25901 | ZXP-0001279 | Sun-27-Oct-19 3:07 PM |
Machine 14 | Small | AX-1003 | AMX-25881 | ZXP-0001259 | Sun-27-Oct-19 6:00 PM |
Machine 9 | Large | AX-1017 | AMX-25895 | ZXP-0001273 | Sun-27-Oct-19 6:57 PM |
Machine 6 | X-Large | AX-1031 | AMX-25909 | ZXP-0001287 | Sun-27-Oct-19 7:55 PM |
Summary |
<tbody>
</tbody>
Thanking You, Martin.