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.


SystemSizeProductionMaterialBatch TimeStamp
Machine 1SmallAX-1001AMX-25879ZXP-0001260Sat-26-Oct-19 2:09 AM
Machine 2LargeAX-1007AMX-25885ZXP-0001268Sat-26-Oct-19 2:38 PM
Machine 1LargeAX-1012AMX-25890ZXP-0001261Sat-26-Oct-19 8:52 PM
Dept1
Machine 3LargeAX-1002AMX-25880ZXP-0001258Sun-27-Oct-19 7:40 AM
Machine 2X-LargeAX-1015AMX-25893ZXP-0001271Sun-27-Oct-19 1:55 AM
Machine 3X-LargeAX-1016AMX-25894ZXP-0001272Sat-26-Oct-19 2:52 AM
Dept2
Machine 5LargeAX-1008AMX-25886ZXP-0001264Sat-26-Oct-19 6:00 PM
Machine 6MediumAX-1032AMX-25910ZXP-0001288Sat-26-Oct-19 6:43 AM
Machine 6X-LargeAX-1031AMX-25909ZXP-0001287Sun-27-Oct-19 7:55 PM
Dept3
Machine 7LargeAX-1023AMX-25901ZXP-0001279Sun-27-Oct-19 3:07 PM
Machine 7SmallAX-1030AMX-25908ZXP-0001286Sun-27-Oct-19 2:09 AM
Machine 8SmallAX-1026AMX-25904ZXP-0001282Sat-26-Oct-19 7:40 PM
Dept4
Machine 9LargeAX-1017AMX-25895ZXP-0001273Sun-27-Oct-19 6:57 PM
Machine 9MediumAX-1028AMX-25906ZXP-0001284Sat-26-Oct-19 8:38 AM
Machine 9LargeAX-1009AMX-25887ZXP-0001265Sat-26-Oct-19 3:36 AM
Dept5
Machine 11MediumAX-1027AMX-25905ZXP-0001283Sat-26-Oct-19 2:09 PM
Machine 11MediumAX-1005AMX-25883ZXP-0001261Sat-26-Oct-19 11:31 AM
Machine 12MediumAX-1019AMX-25897ZXP-0001275Sun-27-Oct-19 2:38 AM
Dept6
Machine 14SmallAX-1003AMX-25881ZXP-0001259Sun-27-Oct-19 6:00 PM
Machine 14MediumAX-1022AMX-25900ZXP-0001278Sat-26-Oct-19 11:02 PM
Machine 14LargeAX-1024AMX-25902ZXP-0001280Sun-27-Oct-19 3:36 AM
Dept7
Machine 15LargeAX-1020AMX-25898ZXP-0001276Sun-27-Oct-19 10:48 AM
Machine 15MediumAX-1010AMX-25888ZXP-0001266Sun-27-Oct-19 12:28 PM
Machine 16X-LargeAX-1014AMX-25892ZXP-0001270Sat-26-Oct-19 4:48 AM
Dept8

<tbody>
</tbody>

Expected Results.

SystemSizeProductionMaterialBatch TimeStamp
Machine 1SmallAX-1001AMX-25879ZXP-0001260Sat-26-Oct-19 2:09 AM
Machine 3X-LargeAX-1016AMX-25894ZXP-0001272Sat-26-Oct-19 2:52 AM
Machine 9LargeAX-1009AMX-25887ZXP-0001265Sat-26-Oct-19 3:36 AM
Machine 16X-LargeAX-1014AMX-25892ZXP-0001270Sat-26-Oct-19 4:48 AM
Machine 6MediumAX-1032AMX-25910ZXP-0001288Sat-26-Oct-19 6:43 AM
Machine 9MediumAX-1028AMX-25906ZXP-0001284Sat-26-Oct-19 8:38 AM
Machine 11MediumAX-1005AMX-25883ZXP-0001261Sat-26-Oct-19 11:31 AM
Machine 11MediumAX-1027AMX-25905ZXP-0001283Sat-26-Oct-19 2:09 PM
Machine 2LargeAX-1007AMX-25885ZXP-0001268Sat-26-Oct-19 2:38 PM
Machine 5LargeAX-1008AMX-25886ZXP-0001264Sat-26-Oct-19 6:00 PM
Machine 8SmallAX-1026AMX-25904ZXP-0001282Sat-26-Oct-19 7:40 PM
Machine 1LargeAX-1012AMX-25890ZXP-0001261Sat-26-Oct-19 8:52 PM
Machine 14MediumAX-1022AMX-25900ZXP-0001278Sat-26-Oct-19 11:02 PM
Machine 2X-LargeAX-1015AMX-25893ZXP-0001271Sun-27-Oct-19 1:55 AM
Machine 7SmallAX-1030AMX-25908ZXP-0001286Sun-27-Oct-19 2:09 AM
Machine 12MediumAX-1019AMX-25897ZXP-0001275Sun-27-Oct-19 2:38 AM
Machine 14LargeAX-1024AMX-25902ZXP-0001280Sun-27-Oct-19 3:36 AM
Machine 3LargeAX-1002AMX-25880ZXP-0001258Sun-27-Oct-19 7:40 AM
Machine 15LargeAX-1020AMX-25898ZXP-0001276Sun-27-Oct-19 10:48 AM
Machine 15MediumAX-1010AMX-25888ZXP-0001266Sun-27-Oct-19 12:28 PM
Machine 7LargeAX-1023AMX-25901ZXP-0001279Sun-27-Oct-19 3:07 PM
Machine 14SmallAX-1003AMX-25881ZXP-0001259Sun-27-Oct-19 6:00 PM
Machine 9LargeAX-1017AMX-25895ZXP-0001273Sun-27-Oct-19 6:57 PM
Machine 6X-LargeAX-1031AMX-25909ZXP-0001287Sun-27-Oct-19 7:55 PM
Summary

<tbody>
</tbody>

Thanking You, Martin.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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,213,583
Messages
6,114,478
Members
448,574
Latest member
bestresearch

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