breakdown of start and end dates from a list of machines with multiple products

G

GexcelG

New Member
Joined
May 15, 2018
Messages
7
Hi,
I have a list of products produced by machines per day. each machine can produce multiple products and a product can be produced by multiple machines.
example:
[TABLE="width: 235"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Machine[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD]4/2/2018 6:30[/TD]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4/3/2018 6:30[/TD]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4/4/2018 6:30[/TD]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4/5/2018 6:30[/TD]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4/8/2018 6:30[/TD]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4/9/2018 6:30[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]4/10/2018 6:30[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]4/11/2018 6:30[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]4/12/2018 6:30[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]4/13/2018 6:30[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]4/14/2018 6:30[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]4/15/2018 6:30[/TD]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4/16/2018 6:30[/TD]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4/16/2018 16:48[/TD]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4/17/2018 6:30[/TD]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4/18/2018 6:30[/TD]
[TD]2[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]4/19/2018 6:30[/TD]
[TD]2[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]4/20/2018 6:30[/TD]
[TD]2[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]4/2/2018 6:30[/TD]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]4/3/2018 6:30[/TD]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]4/4/2018 6:30[/TD]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]4/5/2018 6:30[/TD]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]4/8/2018 6:30[/TD]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]4/9/2018 6:30[/TD]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4/10/2018 6:30[/TD]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4/11/2018 6:30[/TD]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4/12/2018 6:30[/TD]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4/13/2018 6:30[/TD]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4/14/2018 6:30[/TD]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4/15/2018 6:30[/TD]
[TD]1[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]4/16/2018 6:30[/TD]
[TD]1[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]4/16/2018 16:48[/TD]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]4/17/2018 6:30[/TD]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]4/18/2018 6:30[/TD]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]4/19/2018 6:30[/TD]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]4/20/2018 6:30[/TD]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]
I am trying for hours using Pivot table to create a "summary" table, something like this:

[TABLE="width: 270"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]2[/TD]
[TD="align: left"]Start[/TD]
[TD="align: left"]End[/TD]
[/TR]
[TR]
[TD="align: left"]A[/TD]
[TD="align: right"]4/2/2018 6:30[/TD]
[TD="align: right"]4/8/2018 6:30[/TD]
[/TR]
[TR]
[TD="align: left"]B[/TD]
[TD="align: right"]4/9/2018 6:30[/TD]
[TD="align: right"]4/14/2018 6:30[/TD]
[/TR]
[TR]
[TD="align: left"]A[/TD]
[TD="align: right"]4/15/2018 6:30[/TD]
[TD="align: right"]4/17/2018 6:30[/TD]
[/TR]
[TR]
[TD="align: left"]C[/TD]
[TD="align: right"]4/18/2018 6:30[/TD]
[TD="align: right"]4/20/2018 6:30[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: left"]Start[/TD]
[TD="align: left"]End[/TD]
[/TR]
[TR]
[TD="align: left"]D[/TD]
[TD="align: right"]4/2/2018 6:30[/TD]
[TD="align: right"]4/8/2018 6:30[/TD]
[/TR]
[TR]
[TD="align: left"]A[/TD]
[TD="align: right"]4/9/2018 6:30[/TD]
[TD="align: right"]4/14/2018 6:30[/TD]
[/TR]
[TR]
[TD="align: left"]E[/TD]
[TD="align: right"]4/15/2018 6:30[/TD]
[TD="align: right"]4/16/2018 6:30[/TD]
[/TR]
[TR]
[TD="align: left"]D[/TD]
[TD="align: right"]4/16/2018 16:48[/TD]
[TD="align: right"]4/20/2018 6:30[/TD]
[/TR]
</tbody>[/TABLE]


Thanks!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Try this

With Data on Sheet1 in columns A B and C and data has a header

Insert Pivot Table

Drag Machine to Rows section
Drag Product to Rows section
Drag Date into Values
Click on Count of Date in Values
Select Value Field Settings and choose Min
Drag Date into Values
Click on Count of Date in Values
Select Value Field Settings and choose Max

In a blank cell enter a date and time, any date and time
Select that cell
Click on Format Painter and paste over the Pivot Table so all the dates change to dates and times
Widen the column so you can see the output.

This will list the min/max dates per machine per product.

NOTE: You have multiple product groups for each machine, ie Machine 1 has product D occurring twice.
You'll need to isolate these products to have them appear separately on the Pivot Table otherwise it will just group them under one D product.
So you'll need an extra column to isolate each product.
 
Upvote 0
Try this for results starting"E1".
Code: 
[COLOR=navy]Sub[/COLOR] MG15May38
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] R [COLOR=navy]As[/COLOR] Range, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Q [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Dim[/COLOR] K [COLOR=navy]As[/COLOR] Variant, Temp [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] dic [COLOR=navy]As[/COLOR] Object, Kk [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Set[/COLOR] Rng = Range("C2", Range("C" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]Set[/COLOR] dic = CreateObject("scripting.dictionary")
dic.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    dic(Dn.Offset(, -1).Value) = Empty
    [COLOR=navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=navy]Then[/COLOR]
        .Add Dn.Value, Array(Dn.Offset(, -1).Value, Dn)
    [COLOR=navy]Else[/COLOR]
        Q = .Item(Dn.Value)
          [COLOR=navy]Set[/COLOR] Q(1) = Union(Q(1), Dn)
       .Item(Dn.Value) = Q
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
  ReDim Ray(1 To Rng.Count, 1 To 3)
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Kk [COLOR=navy]In[/COLOR] dic.keys
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] .keys
         [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] R [COLOR=navy]In[/COLOR] .Item(K)(1).Areas
                [COLOR=navy]If[/COLOR] R(1).Offset(, -1) = Kk [COLOR=navy]Then[/COLOR]
                    [COLOR=navy]If[/COLOR] Not R(1).Offset(, -1) = Temp [COLOR=navy]Then[/COLOR]
                        c = c + 1
                        Ray(c, 2) = "Start": Ray(c, 3) = "End"
                        Ray(c, 1) = R(1).Offset(, -1)
                        Temp = R(1).Offset(, -1)
                    [COLOR=navy]End[/COLOR] If
                     c = c + 1
                     Ray(c, 1) = K:
                     Ray(c, 2) = CDate(R(1).Offset(, -2))
                     Ray(c, 3) = CDate(R(R.Count).Offset(, -2))
                [COLOR=navy]End[/COLOR] If
            [COLOR=navy]Next[/COLOR] R
        [COLOR=navy]Next[/COLOR] K
[COLOR=navy]Next[/COLOR] Kk
[COLOR=navy]End[/COLOR] With
Range("E1").Resize(c, 3).Value = Ray
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


This may Help!!!

To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11" :- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.

On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.


Regards Mick
 
Last edited:
Upvote 0
Hi,
It only shows:
[TABLE="width: 257"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"]12:00:00 AM[/TD]
[TD="align: right"]12:00:00 AM[/TD]
[/TR]
[TR]
[TD="align: left"]Date[/TD]
[TD="align: left"]Start[/TD]
[TD="align: left"]End[/TD]
[/TR]
[TR]
[TD="align: left"]Machine[/TD]
[TD="align: right"]12:00:00 AM[/TD]
[TD="align: right"]12:00:00 AM[/TD]
[/TR]
[TR]
[TD="align: right"]4/2/2018 6:30[/TD]
[TD="align: left"]Start[/TD]
[TD="align: left"]End[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]12:00:00 AM[/TD]
[TD="align: right"]12:00:00 AM[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]12:00:00 AM[/TD]
[TD="align: right"]12:00:00 AM

[/TD]
[/TR]
</tbody>[/TABLE]
Maybe the input data is not located at the right place?
MickG said:
Try this for results starting"E1".
Code: 
[COLOR=navy]Sub[/COLOR] MG15May38
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] R [COLOR=navy]As[/COLOR] Range, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Q [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Dim[/COLOR] K [COLOR=navy]As[/COLOR] Variant, Temp [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] dic [COLOR=navy]As[/COLOR] Object, Kk [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Set[/COLOR] Rng = Range("C2", Range("C" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]Set[/COLOR] dic = CreateObject("scripting.dictionary")
dic.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    dic(Dn.Offset(, -1).Value) = Empty
    [COLOR=navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=navy]Then[/COLOR]
        .Add Dn.Value, Array(Dn.Offset(, -1).Value, Dn)
    [COLOR=navy]Else[/COLOR]
        Q = .Item(Dn.Value)
          [COLOR=navy]Set[/COLOR] Q(1) = Union(Q(1), Dn)
       .Item(Dn.Value) = Q
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
  ReDim Ray(1 To Rng.Count, 1 To 3)
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Kk [COLOR=navy]In[/COLOR] dic.keys
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] .keys
         [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] R [COLOR=navy]In[/COLOR] .Item(K)(1).Areas
                [COLOR=navy]If[/COLOR] R(1).Offset(, -1) = Kk [COLOR=navy]Then[/COLOR]
                    [COLOR=navy]If[/COLOR] Not R(1).Offset(, -1) = Temp [COLOR=navy]Then[/COLOR]
                        c = c + 1
                        Ray(c, 2) = "Start": Ray(c, 3) = "End"
                        Ray(c, 1) = R(1).Offset(, -1)
                        Temp = R(1).Offset(, -1)
                    [COLOR=navy]End[/COLOR] If
                     c = c + 1
                     Ray(c, 1) = K:
                     Ray(c, 2) = CDate(R(1).Offset(, -2))
                     Ray(c, 3) = CDate(R(R.Count).Offset(, -2))
                [COLOR=navy]End[/COLOR] If
            [COLOR=navy]Next[/COLOR] R
        [COLOR=navy]Next[/COLOR] K
[COLOR=navy]Next[/COLOR] Kk
[COLOR=navy]End[/COLOR] With
Range("E1").Resize(c, 3).Value = Ray
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


This may Help!!!

To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11" :- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.

On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.


Regards Mick
Click to expand...
 
Upvote 0
Hi,
Thanks a lot!
Anyone can think of a way to split the repeated parts automatically? the example i gave was a short and simple one, the original data has alot more repeats...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Yearly Interest Calculation
Replies
1
Views
339
signup
S
E
  • Solved
Duplicate row based on cell with text separated by semicolon
Replies
5
Views
99
JEC
JEC
L
  • Question Question
INDEX MATCH
Replies
1
Views
341
Fluff
Fluff
L
  • Solved
Combinations of 3 from 16 numbers pool with unique product
2
Replies
14
Views
659
libelouli
L
S
  • Question Question
Formatting Midnight in a date to 24 instead of 0:00
Replies
4
Views
613
shepcs
S

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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