Index Small Across 8 Worksheets

M

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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

T
  • Question Question
INDEX and MATCH formula for rainfall cessation
Replies
6
Views
300
Eric W
Eric W
M
  • Question Question
Index Match function List based on date
Replies
7
Views
326
Fluff
Fluff
R
  • Solved
vlookup, INDEX, or SUMPRODUCT???
2
Replies
10
Views
846
KRice
K
F
  • Question Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
398
freeb1893
F
JenniferMurphy
  • Solved
Adding a new time causes wrong average
Replies
3
Views
281
awoohaw
awoohaw

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

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