Index Small Across 8 Worksheets

martinmc

New Member
Joined
Apr 21, 2019
Messages
16
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.


[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD="align: center"]System[/TD]
[TD="align: center"]Size[/TD]
[TD="align: center"]Production[/TD]
[TD="align: center"]Material[/TD]
[TD="align: center"]Batch [/TD]
[TD="align: center"]TimeStamp[/TD]
[/TR]
[TR]
[TD]Machine 1[/TD]
[TD]Small[/TD]
[TD]AX-1001[/TD]
[TD]AMX-25879[/TD]
[TD]ZXP-0001260[/TD]
[TD="align: right"]Sat-26-Oct-19 2:09 AM[/TD]
[/TR]
[TR]
[TD]Machine 2[/TD]
[TD]Large[/TD]
[TD]AX-1007[/TD]
[TD]AMX-25885[/TD]
[TD]ZXP-0001268[/TD]
[TD="align: right"]Sat-26-Oct-19 2:38 PM[/TD]
[/TR]
[TR]
[TD]Machine 1[/TD]
[TD]Large[/TD]
[TD]AX-1012[/TD]
[TD]AMX-25890[/TD]
[TD]ZXP-0001261[/TD]
[TD="align: right"]Sat-26-Oct-19 8:52 PM[/TD]
[/TR]
[TR]
[TD]Dept1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Machine 3[/TD]
[TD]Large[/TD]
[TD]AX-1002[/TD]
[TD]AMX-25880[/TD]
[TD]ZXP-0001258[/TD]
[TD="align: right"]Sun-27-Oct-19 7:40 AM[/TD]
[/TR]
[TR]
[TD]Machine 2[/TD]
[TD]X-Large[/TD]
[TD]AX-1015[/TD]
[TD]AMX-25893[/TD]
[TD]ZXP-0001271[/TD]
[TD="align: right"]Sun-27-Oct-19 1:55 AM[/TD]
[/TR]
[TR]
[TD]Machine 3[/TD]
[TD]X-Large[/TD]
[TD]AX-1016[/TD]
[TD]AMX-25894[/TD]
[TD]ZXP-0001272[/TD]
[TD="align: right"]Sat-26-Oct-19 2:52 AM[/TD]
[/TR]
[TR]
[TD]Dept2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Machine 5[/TD]
[TD]Large[/TD]
[TD]AX-1008[/TD]
[TD]AMX-25886[/TD]
[TD]ZXP-0001264[/TD]
[TD="align: right"]Sat-26-Oct-19 6:00 PM[/TD]
[/TR]
[TR]
[TD]Machine 6[/TD]
[TD]Medium[/TD]
[TD]AX-1032[/TD]
[TD]AMX-25910[/TD]
[TD]ZXP-0001288[/TD]
[TD="align: right"]Sat-26-Oct-19 6:43 AM[/TD]
[/TR]
[TR]
[TD]Machine 6[/TD]
[TD]X-Large[/TD]
[TD]AX-1031[/TD]
[TD]AMX-25909[/TD]
[TD]ZXP-0001287[/TD]
[TD="align: right"]Sun-27-Oct-19 7:55 PM[/TD]
[/TR]
[TR]
[TD]Dept3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Machine 7[/TD]
[TD]Large[/TD]
[TD]AX-1023[/TD]
[TD]AMX-25901[/TD]
[TD]ZXP-0001279[/TD]
[TD="align: right"]Sun-27-Oct-19 3:07 PM[/TD]
[/TR]
[TR]
[TD]Machine 7[/TD]
[TD]Small[/TD]
[TD]AX-1030[/TD]
[TD]AMX-25908[/TD]
[TD]ZXP-0001286[/TD]
[TD="align: right"]Sun-27-Oct-19 2:09 AM[/TD]
[/TR]
[TR]
[TD]Machine 8[/TD]
[TD]Small[/TD]
[TD]AX-1026[/TD]
[TD]AMX-25904[/TD]
[TD]ZXP-0001282[/TD]
[TD="align: right"]Sat-26-Oct-19 7:40 PM[/TD]
[/TR]
[TR]
[TD]Dept4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Machine 9[/TD]
[TD]Large[/TD]
[TD]AX-1017[/TD]
[TD]AMX-25895[/TD]
[TD]ZXP-0001273[/TD]
[TD="align: right"]Sun-27-Oct-19 6:57 PM[/TD]
[/TR]
[TR]
[TD]Machine 9[/TD]
[TD]Medium[/TD]
[TD]AX-1028[/TD]
[TD]AMX-25906[/TD]
[TD]ZXP-0001284[/TD]
[TD="align: right"]Sat-26-Oct-19 8:38 AM[/TD]
[/TR]
[TR]
[TD]Machine 9[/TD]
[TD]Large[/TD]
[TD]AX-1009[/TD]
[TD]AMX-25887[/TD]
[TD]ZXP-0001265[/TD]
[TD="align: right"]Sat-26-Oct-19 3:36 AM[/TD]
[/TR]
[TR]
[TD]Dept5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Machine 11[/TD]
[TD]Medium[/TD]
[TD]AX-1027[/TD]
[TD]AMX-25905[/TD]
[TD]ZXP-0001283[/TD]
[TD="align: right"]Sat-26-Oct-19 2:09 PM[/TD]
[/TR]
[TR]
[TD]Machine 11[/TD]
[TD]Medium[/TD]
[TD]AX-1005[/TD]
[TD]AMX-25883[/TD]
[TD]ZXP-0001261[/TD]
[TD="align: right"]Sat-26-Oct-19 11:31 AM[/TD]
[/TR]
[TR]
[TD]Machine 12[/TD]
[TD]Medium[/TD]
[TD]AX-1019[/TD]
[TD]AMX-25897[/TD]
[TD]ZXP-0001275[/TD]
[TD="align: right"]Sun-27-Oct-19 2:38 AM[/TD]
[/TR]
[TR]
[TD]Dept6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Machine 14[/TD]
[TD]Small[/TD]
[TD]AX-1003[/TD]
[TD]AMX-25881[/TD]
[TD]ZXP-0001259[/TD]
[TD="align: right"]Sun-27-Oct-19 6:00 PM[/TD]
[/TR]
[TR]
[TD]Machine 14[/TD]
[TD]Medium[/TD]
[TD]AX-1022[/TD]
[TD]AMX-25900[/TD]
[TD]ZXP-0001278[/TD]
[TD="align: right"]Sat-26-Oct-19 11:02 PM[/TD]
[/TR]
[TR]
[TD]Machine 14[/TD]
[TD]Large[/TD]
[TD]AX-1024[/TD]
[TD]AMX-25902[/TD]
[TD]ZXP-0001280[/TD]
[TD="align: right"]Sun-27-Oct-19 3:36 AM[/TD]
[/TR]
[TR]
[TD]Dept7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Machine 15[/TD]
[TD]Large[/TD]
[TD]AX-1020[/TD]
[TD]AMX-25898[/TD]
[TD]ZXP-0001276[/TD]
[TD="align: right"]Sun-27-Oct-19 10:48 AM[/TD]
[/TR]
[TR]
[TD]Machine 15[/TD]
[TD]Medium[/TD]
[TD]AX-1010[/TD]
[TD]AMX-25888[/TD]
[TD]ZXP-0001266[/TD]
[TD="align: right"]Sun-27-Oct-19 12:28 PM[/TD]
[/TR]
[TR]
[TD]Machine 16[/TD]
[TD]X-Large[/TD]
[TD]AX-1014[/TD]
[TD]AMX-25892[/TD]
[TD]ZXP-0001270[/TD]
[TD="align: right"]Sat-26-Oct-19 4:48 AM[/TD]
[/TR]
[TR]
[TD]Dept8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Expected Results.

[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD="align: center"]System[/TD]
[TD="align: center"]Size[/TD]
[TD="align: center"]Production[/TD]
[TD="align: center"]Material[/TD]
[TD="align: center"]Batch [/TD]
[TD="align: center"]TimeStamp[/TD]
[/TR]
[TR]
[TD]Machine 1[/TD]
[TD]Small[/TD]
[TD]AX-1001[/TD]
[TD]AMX-25879[/TD]
[TD]ZXP-0001260[/TD]
[TD="align: right"]Sat-26-Oct-19 2:09 AM[/TD]
[/TR]
[TR]
[TD]Machine 3[/TD]
[TD]X-Large[/TD]
[TD]AX-1016[/TD]
[TD]AMX-25894[/TD]
[TD]ZXP-0001272[/TD]
[TD="align: right"]Sat-26-Oct-19 2:52 AM[/TD]
[/TR]
[TR]
[TD]Machine 9[/TD]
[TD]Large[/TD]
[TD]AX-1009[/TD]
[TD]AMX-25887[/TD]
[TD]ZXP-0001265[/TD]
[TD="align: right"]Sat-26-Oct-19 3:36 AM[/TD]
[/TR]
[TR]
[TD]Machine 16[/TD]
[TD]X-Large[/TD]
[TD]AX-1014[/TD]
[TD]AMX-25892[/TD]
[TD]ZXP-0001270[/TD]
[TD="align: right"]Sat-26-Oct-19 4:48 AM[/TD]
[/TR]
[TR]
[TD]Machine 6[/TD]
[TD]Medium[/TD]
[TD]AX-1032[/TD]
[TD]AMX-25910[/TD]
[TD]ZXP-0001288[/TD]
[TD="align: right"]Sat-26-Oct-19 6:43 AM[/TD]
[/TR]
[TR]
[TD]Machine 9[/TD]
[TD]Medium[/TD]
[TD]AX-1028[/TD]
[TD]AMX-25906[/TD]
[TD]ZXP-0001284[/TD]
[TD="align: right"]Sat-26-Oct-19 8:38 AM[/TD]
[/TR]
[TR]
[TD]Machine 11[/TD]
[TD]Medium[/TD]
[TD]AX-1005[/TD]
[TD]AMX-25883[/TD]
[TD]ZXP-0001261[/TD]
[TD="align: right"]Sat-26-Oct-19 11:31 AM[/TD]
[/TR]
[TR]
[TD]Machine 11[/TD]
[TD]Medium[/TD]
[TD]AX-1027[/TD]
[TD]AMX-25905[/TD]
[TD]ZXP-0001283[/TD]
[TD="align: right"]Sat-26-Oct-19 2:09 PM[/TD]
[/TR]
[TR]
[TD]Machine 2[/TD]
[TD]Large[/TD]
[TD]AX-1007[/TD]
[TD]AMX-25885[/TD]
[TD]ZXP-0001268[/TD]
[TD="align: right"]Sat-26-Oct-19 2:38 PM[/TD]
[/TR]
[TR]
[TD]Machine 5[/TD]
[TD]Large[/TD]
[TD]AX-1008[/TD]
[TD]AMX-25886[/TD]
[TD]ZXP-0001264[/TD]
[TD="align: right"]Sat-26-Oct-19 6:00 PM[/TD]
[/TR]
[TR]
[TD]Machine 8[/TD]
[TD]Small[/TD]
[TD]AX-1026[/TD]
[TD]AMX-25904[/TD]
[TD]ZXP-0001282[/TD]
[TD="align: right"]Sat-26-Oct-19 7:40 PM[/TD]
[/TR]
[TR]
[TD]Machine 1[/TD]
[TD]Large[/TD]
[TD]AX-1012[/TD]
[TD]AMX-25890[/TD]
[TD]ZXP-0001261[/TD]
[TD="align: right"]Sat-26-Oct-19 8:52 PM[/TD]
[/TR]
[TR]
[TD]Machine 14[/TD]
[TD]Medium[/TD]
[TD]AX-1022[/TD]
[TD]AMX-25900[/TD]
[TD]ZXP-0001278[/TD]
[TD="align: right"]Sat-26-Oct-19 11:02 PM[/TD]
[/TR]
[TR]
[TD]Machine 2[/TD]
[TD]X-Large[/TD]
[TD]AX-1015[/TD]
[TD]AMX-25893[/TD]
[TD]ZXP-0001271[/TD]
[TD="align: right"]Sun-27-Oct-19 1:55 AM[/TD]
[/TR]
[TR]
[TD]Machine 7[/TD]
[TD]Small[/TD]
[TD]AX-1030[/TD]
[TD]AMX-25908[/TD]
[TD]ZXP-0001286[/TD]
[TD="align: right"]Sun-27-Oct-19 2:09 AM[/TD]
[/TR]
[TR]
[TD]Machine 12[/TD]
[TD]Medium[/TD]
[TD]AX-1019[/TD]
[TD]AMX-25897[/TD]
[TD]ZXP-0001275[/TD]
[TD="align: right"]Sun-27-Oct-19 2:38 AM[/TD]
[/TR]
[TR]
[TD]Machine 14[/TD]
[TD]Large[/TD]
[TD]AX-1024[/TD]
[TD]AMX-25902[/TD]
[TD]ZXP-0001280[/TD]
[TD="align: right"]Sun-27-Oct-19 3:36 AM[/TD]
[/TR]
[TR]
[TD]Machine 3[/TD]
[TD]Large[/TD]
[TD]AX-1002[/TD]
[TD]AMX-25880[/TD]
[TD]ZXP-0001258[/TD]
[TD="align: right"]Sun-27-Oct-19 7:40 AM[/TD]
[/TR]
[TR]
[TD]Machine 15[/TD]
[TD]Large[/TD]
[TD]AX-1020[/TD]
[TD]AMX-25898[/TD]
[TD]ZXP-0001276[/TD]
[TD="align: right"]Sun-27-Oct-19 10:48 AM[/TD]
[/TR]
[TR]
[TD]Machine 15[/TD]
[TD]Medium[/TD]
[TD]AX-1010[/TD]
[TD]AMX-25888[/TD]
[TD]ZXP-0001266[/TD]
[TD="align: right"]Sun-27-Oct-19 12:28 PM[/TD]
[/TR]
[TR]
[TD]Machine 7[/TD]
[TD]Large[/TD]
[TD]AX-1023[/TD]
[TD]AMX-25901[/TD]
[TD]ZXP-0001279[/TD]
[TD="align: right"]Sun-27-Oct-19 3:07 PM[/TD]
[/TR]
[TR]
[TD]Machine 14[/TD]
[TD]Small[/TD]
[TD]AX-1003[/TD]
[TD]AMX-25881[/TD]
[TD]ZXP-0001259[/TD]
[TD="align: right"]Sun-27-Oct-19 6:00 PM[/TD]
[/TR]
[TR]
[TD]Machine 9[/TD]
[TD]Large[/TD]
[TD]AX-1017[/TD]
[TD]AMX-25895[/TD]
[TD]ZXP-0001273[/TD]
[TD="align: right"]Sun-27-Oct-19 6:57 PM[/TD]
[/TR]
[TR]
[TD]Machine 6[/TD]
[TD]X-Large[/TD]
[TD]AX-1031[/TD]
[TD]AMX-25909[/TD]
[TD]ZXP-0001287[/TD]
[TD="align: right"]Sun-27-Oct-19 7:55 PM[/TD]
[/TR]
[TR]
[TD]Summary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanking You, Martin.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi Martin,

Maybe you could use this version... I tried many different ways of using formulas to consolidate 8 sheets but it was clunky and unreliable. I've made a mock-up that has a data entry sheet which contains all of the data which can be sorted by Timestamp. The timestamp is generated by formula against the Date & Time columns from user entry. Then I'm extracting each Dept to their own sheet for cross reference. By using a data entry table you only need to maintain one source sheet instead of 8.

The Summary sheet can be used to extract all entries between timestamps.

There is data validation to assist with Dates, Dept, Machine # & Size in the data entry table. Use the Summary sheet "Search" drop downs that will offer oldest or newest dates but you can also manually enter dates to extract from the data entry table.

As long as you sort the the data table by timestamp (oldest/newest), all of the other sheets will follow suit...:cool:


https://www.dropbox.com/s/otdiq7cc5gqg9lx/Index Small 8 Sheets_planB.xlsx?dl=0
 
Upvote 0
Hi RasGhul and thanks for your feed back and time. Unfortunately Excel tables don't work on a shared workbook. I was thinking of a similar approach you have on the Summary sheet using the Index/Aggregate function where i would extract each Dept. to a a individual range of lets say A2:F101, A102:F201, A202:F301, A302:F401 etc. and then do a full extraction from there to another worksheet. This works fine and thanks again, i thought this would be a breeze for the Excel Wizards here.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top