Return Each First and Last Instance of a Value in a Range

[Lance1227]

I'm trying to return the first and last instances of a value, but the issue I'm having is that there may be multiple instances of the value in the range and I need each occurrence results displayed separately. The range is for a monthly timecard and the value is time off for vacation. I'm trying to return the first and last day of each occurrence of consecutive days. So in the attached file, the first three days tagged "VL" would return the dates 4 Jan 23 in one cell and 6 Jan 23 in the next cell. Then it would go to the next occurrence and again return first and last dates. I'm seeing how to get the first and last occurrence, but it's the multiple occurrences that has me stumped. Any assistance would be most appreciated.

 

[Peter_SSs]

See if you can adapt this to be any use for your ranges as we cannot see them.
Ensure that there is a blank column before the first date. That is column C for me. You can hide this blank column.
Ensure that there is a blank column after the last date. That is column AI for me. You can hide this blank column.

I have hidden some columns below to make the mini sheet a bit smaller for the forum.

23 05 30.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P					U	V	W	X										AH	AI
1
2		1/01/23	2/01/23	3/01/23	4/01/23	5/01/23	6/01/23	7/01/23	8/01/23	9/01/23	10/01/23	11/01/23	12/01/23	13/01/23					18/01/23	19/01/23	20/01/23	21/01/23										31/01/23
3
4					8VL	8VL	8VL					8VL									8VL
5
6
7		Start	End
8		4/01/23	6/01/23
9		11/01/23	11/01/23
10		20/01/23	20/01/23
11
First Last

Cell Formulas
Range	Formula
D8:D11	D8	=IFERROR(INDEX($2:$2,AGGREGATE(15,6,COLUMN(D$4:AH$4)/((D$4:AH$4<>"")*(C$4:AG$4="")),ROWS(D$8:D8))),"")
E8:E11	E8	=IF(D8="","",INDEX($2:$2,AGGREGATE(15,6,COLUMN(D$4:AH$4)/((D$4:AH$4<>"")*(E$4:AI$4="")),ROWS(D$8:D8))))

 

[Lance1227]

Good evening,

Sorry for the late reply. When I posted my question, I was rushing and forgot something, so I might post a follow-up question. I'd like to see if I can figure it out by myself first though. I had to tell you how much I appreciate your response. I'm still trying to unpack it and see exactly how it works (doing this in my spare time, which I don't have a lot of), but I know I could have never come up with this. This is actually to assist another person. A new payroll system will start in a couple of months, so I am trying to automate the system as much as I can. As it stands now, she would have to manually type this information in for about 250 records each month so I will be sure to tell her how much you assisted me. She may want to sacrifice something in your honor, but since I'm against cruelty it may only be a carrot or a potato! Seriously, I really appreciate your assistance.

 

[Peter_SSs]

I had to tell you how much I appreciate your response.

You're welcome.

I'd like to see if I can figure it out by myself first though.

Good plan - but post back if it turns out you need more assistance.

 

[Lance1227]

Honestly, I'm guessing I'll need your assistance again, but if I can figure it out, then it's due to you. Really appreciate you and the others giving your time and expertise to help us mere mortals.

 

[Peter_SSs]

Thanks for your kind words.

Good luck with your figuring!!

 

[Lance1227]

Good afternoon,

I'm wondering if I can a bit more assistance. I need to take the above one step further and link the the dates to the total number of hours and the type of exception. In the spreadsheet example below (sorry with our IT, I can't upload a mini sheet):

• Rows 6-7 are the days of the month
• Rows 8-10 are individual employee records (three rows for each employee)
  • Row 8 is scheduled work hours
  • Rows 9-10 are exceptions to schedule
    • Exceptions are things like vacation leave, sick leave, etc.
    • The number is the hours of exception (4SL = 4 hours Sick Leave)
    • There are approximately 30 exception codes
    • Exception codes can be between 2 and 7 letters
    • Occasionally, there are more than once exception per day, hence the two rows (9-10)

I would like the output to be the second attachment. With that, we can import the data into Access a save numerous hours manually typing the data.

Any assistance anyone can provide would be greatly appreciated.

 

[RudRud]

Good afternoon,

I'm wondering if I can a bit more assistance. I need to take the above one step further and link the the dates to the total number of hours and the type of exception. In the spreadsheet example below (sorry with our IT, I can't upload a mini sheet):

• Rows 6-7 are the days of the month
• Rows 8-10 are individual employee records (three rows for each employee)
  • Row 8 is scheduled work hours
  • Rows 9-10 are exceptions to schedule
    • Exceptions are things like vacation leave, sick leave, etc.
    • The number is the hours of exception (4SL = 4 hours Sick Leave)
    • There are approximately 30 exception codes
    • Exception codes can be between 2 and 7 letters
    • Occasionally, there are more than once exception per day, hence the two rows (9-10)

I would like the output to be the second attachment. With that, we can import the data into Access a save numerous hours manually typing the data.

Any assistance anyone can provide would be greatly appreciated.

Hi @Lance1227,

1. Type AL Hours 10 ( Why 10 not 2? - 2AL)
2. 16 - 18 Merge Cells 2CMCL only count start 17-jan-23 stop 17-jan-23 ? Can you elaborate more for further clarification? Thank you

 

[Lance1227]

Good afternoon,

1. Type AL Hours 10 ( Why 10 not 2? - 2AL)
I'm trying to combine as many days as possible. If the employee takes a week of sick leave, I'm hoping that could be done with one line vs one line for each day. In the previous example, I'm hoping to combine 2-3 Jan (E9:F9) into one line instead of two lines. Sometimes an employee is gone for the entire month, so would prefer that we didn't end up with 30 lines. See attached example

2. 16 - 18 Merge Cells 2CMCL only count start 17-jan-23 stop 17-jan-23 ? Can you elaborate more for further clarification?
Very sorry! 16-18 are not merged. I wasn't paying attention. The data is just too large to fit into cell 17 (T9). Only 17 has data.


I'm really sorry I wasn't clear enough. I'm working on a US military installation and the Japanese government wants to automate the Japanese payroll system for employees working on the base (believe it or not we are handwriting their payroll on ledger sized sheets now). So what I'm trying to do is automate the process as it seems pointless to go from handwriting the information to typing the information.

Again I appreciate any and all assistance

 

[RudRud]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1				UNIT DESIGNATION								pREPARED BY							CONTACT TEL #				MANAGER'S SIGNATURE								CERTIFY THAT THE TIME AND ATTEN[
2	2	JAPANESE PERSONNEL TIME
3	3	AND ATTENDANCE RECORD (IHA)		EX#				BR#																							AND THAT OVERTIME SHOWN WAS
4	4	LN EMP #
5	5	Contract Hours		TIME AND ATTENDANCE RECORD																																SWH
6	6	Employee's Name		Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue
7	7	LAST NAME - FIRST NAME		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
8	8	30000001		0	8	8	8	8	8	0	8	8	8	8	8	8	0	0	8	8	8	8	8	0	0	8	8	8	8	8	0	0	8	8
9	9	40			2AL	8AL			8SLM		3SL			8hp			8SML			2CMCL
10	10	MOUSE, Mickey												1ND																						176
11		123		0	8	8	8	8	8	0	8	8	8	8	8	8	0	0	8	8	8	8	8	0	0	8	8	8	8	8	0	0	8	8
12		232		8AL					8SLM				8hp				8SML
13		Rudrud			2AL				5sl				1ND				2CMCL
Sheet1

Sheet2 Output (Output will be in sheet2, so need to add new sheet with name sheet2 before running the code)

Book2
	A	B	C	D	E
1
2	MOUSE, Mickey	AL	2	2-Jan-23	3-Jan-23
3		AL	8	3-Jan-23	4-Jan-23
4		SLM	8	6-Jan-23	7-Jan-23
5		SL	3	8-Jan-23	9-Jan-23
6		hp	8	11-Jan-23	12-Jan-23
7		ND	1	11-Jan-23	12-Jan-23
8		SML	8	14-Jan-23	15-Jan-23
9		CMCL	2	17-Jan-23	18-Jan-23
10	Rudrud	AL	8	1-Jan-23	2-Jan-23
11		AL	2	2-Jan-23	3-Jan-23
12		SLM	8	6-Jan-23	7-Jan-23
13		sl	5	6-Jan-23	7-Jan-23
14		hp	8	10-Jan-23	11-Jan-23
15		ND	1	10-Jan-23	11-Jan-23
16		SML	8	14-Jan-23	15-Jan-23
17		CMCL	2	14-Jan-23	15-Jan-23
Sheet2

First attempt, I will try to adjust as your desire output, Meanwhile you may try first either this's close what you're looking for or not.

1. Mickey 8SLM Fri 6, Why Output Start 5 Stop 6? A little bit unclear regarding start and stop. how can we define it?

Option Explicit
Option Compare Text
Sub test()
Dim a As Variant
Dim rowforname%, i%, m%, j%, stringlength%, k%, result%, lr%
Dim txt As String


ReDim b(1 To 1000, 1 To 5)
a = Range("b6:ah" & Cells(Rows.Count, "b").End(xlUp).Row).Value 'Current Value Array
rowforname = 1


For i = 4 To UBound(a, 1) Step 3 'Step 3 From First
        
        For j = 3 To UBound(a, 2) 'Loop through Column
            For m = 1 To 2 'Check 2nd employee row if got existing record
            If IsNumeric(Left(a(i + m - 1, j), 1)) Then
            
                If a(i + m - 1, j) <> "" Then
          
                   stringlength = Len(a(i + m - 1, j))
                  For k = 1 To stringlength
                      If IsNumeric(Mid(a(i + m - 1, j), k, 1)) Then
                        result = result & Mid(a(i + m - 1, j), k, 1)
                      Else
                          txt = txt & Mid(a(i + m - 1, j), k, 1)
                      End If
                  Next k
              lr = lr + 1
              b(lr, 2) = txt
              b(lr, 3) = result
              b(lr, 4) = Format(CDate(a(2, j) + 44926), "DD-MMM-YY") 'Start from exact date
              b(lr, 5) = Format(CDate(a(2, j + 1) + 44926), "DD-MMM-YY") 'Stop till next date
              result = 0
              txt = ""

            End If
           End If
        Next m
    Next j
    
b(rowforname, 1) = a(i + 1, 1)
rowforname = lr + 1
Next i

Sheets("sheet2").[a2].Resize(UBound(b, 1), UBound(b, 2)).Value = b

End Sub