Extract only the information I need

[-emma-]

Hi,
I need to extract specific information from a data dump we get for work.
I am hoping that the more knowledgeable people out there can help me make this work to save me almost hours each day at work.

From the picture attached, the left is what I would be pasting into Tab 1, columns A-D and the information on the right is the outcome id like into Tab 2, again columns A-D.

I am attempting to gleam the start, end and lunch and breaks at a glance, which I can do with some manipulation but this takes up a lot of my time.

Some staff may have more than one lunch and break. There is always 2 blank spaces after data and there are 3 blank lines of data when someone doesn't work. I have tried to cover the majority of options that occur each day.

Any help on this would be appreciated

Thank you in advance

 

[jasonb75]

This will do it, I've used single digit numbers rather than retyping all of the times but it will work the same with either.

I'll have another look later to see if I can shorten the formula a bit by making better use of LET or by using some other functions without compromising the results.

Mr excel 16.01.23.xlsm
	A	B	C	D	E	F	G	H	I
1		Code	Start	End			Code	Start	End
2	Staff1			1		Staff1	Start	1
3	Staff1		1	2		Staff1	Break	2	3
4	Staff1	Break	2	3		Staff1	Lunch	4	5
5	Staff1		3	4		Staff1	Break	6	7
6	Staff1	Lunch	4	5		Staff1	End		8
7	Staff1		5	6		Staff2	Start	1
8	Staff1	Break	6	7		Staff2	Break	2	3
9	Staff1		7	8		Staff2	Lunch	6	7
10	Staff1		8			Staff2	End		8
11	Staff1					Staff3	Start	1
12	Staff1					Staff3	End		2
13	Staff2			1
14	Staff2		1	2
15	Staff2	Break	2	3
16	Staff2		3	4
17	Staff2		4	5
18	Staff2		5	6
19	Staff2	Lunch	6	7
20	Staff2		7	8
21	Staff2		8
22	Staff2
23	Staff2
24	Staff3			1
25	Staff3		1	2
26	Staff3		2
27	Staff4
28	Staff4
29	Staff4
30
Sheet7

Cell Formulas
Range	Formula
F2:I12	F2	=LET(a,IF((C2:C29="")*(D2:D29<>""),HSTACK(A2:A29,EXPAND("Start",ROWS(A2:A29),1,"Start"),D2:D29,EXPAND("",ROWS(A2:A29),1,"")),IF((C2:C29<>"")*(D2:D29=""),HSTACK(A2:A29,EXPAND("End",ROWS(A2:A29),1,"End"),EXPAND("",ROWS(A2:A29),1,""),C2:C29),A2:D29)),FILTER(a,INDEX(a,0,2)<>0))
Dynamic array formulas.

 

[DanteAmor]

Hi @-emma- ,

Fit in the macro, to the name of your sheets.
In tab2 sheet in columns C and D put the same time format that you have in tab1 sheet.

Try the following macro:

Sub Extract_information()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long
  Dim ant As String
  Dim endhour As Variant
  
  Set sh1 = Sheets("Tab1")    'Fit to your sheet name
  Set sh2 = Sheets("Tab2")    'Fit to your sheet name
  
  a = sh1.Range("A2:D" & sh1.Range("A" & Rows.Count).End(3).Row + 1).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  
  sh2.Cells.ClearContents
  ant = a(1, 1)
  For i = 1 To UBound(a, 1) - 1
    If ant = a(i, 1) Then
      If a(i, 2) <> "" Then
        j = j + 1
        b(j, 1) = a(i, 1)
        b(j, 2) = a(i, 2)
        b(j, 3) = a(i, 3)
        b(j, 4) = a(i, 4)
      End If
    Else
      j = j + 1
      b(j, 1) = ant
      b(j, 2) = "End"
      b(j, 3) = ""
      b(j, 4) = endhour
      endhour = ""
      If a(i, 4) <> "" Then endhour = a(i, 4)
    End If
    
    If a(i, 2) = "" And a(i, 3) = "" And a(i, 4) <> "" Then
      j = j + 1
      b(j, 1) = a(i, 1)
      b(j, 2) = "Start"
      b(j, 3) = a(i, 4)
    End If
    
    ant = a(i, 1)
    If a(i, 4) <> "" Then endhour = a(i, 4)

  Next
  sh2.Range("A2").Resize(j, 4).Value = b
End Sub

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


Example:

Dante Amor
	A	B	C	D
1		Code	Start	End
2	Staff1	Start	10:32
3	Staff1	Break	12:45	13:00
4	Staff1	Lunch	14:49	15:24
5	Staff1	Break	16:58	18:31
6	Staff1	End		18:31
7	Staff2	Start	07:23
8	Staff2	Break	09:04	09:17
9	Staff2	Lunch	13:04	13:24
10	Staff2	End		15:24
11	Staff3	Start	07:55
12	Staff3	End		15:49
Tab2