How can i get all dates on which employee was on shift A from attendance sheet

[Sakshine]

A-SHIFT A	B-SHIFT B	MONTH-JAN
EMPLOYEE	1	2	3	4	5	6	7	8	DATES OF SHIFT A
EMP01	A	A	B	A	A	B	B	B
EMP02	B	B	A	A	B	B	B	A
EMP03	A	A	A	A	B	B	B	B

I Have this sample worksheet of attendance sheet. Can anybody help me to get the specific dates on which the employee was on shift A? Which formula or function is useful for this task?

 

[alansidman]

Is this what you are looking for? If not, show us a mock up of what your result should look like.

Book21
	A	B
1	EMPLOYEE	Dates.Attribute
2	EMP01	1
3	EMP01	2
4	EMP01	4
5	EMP01	5
6	EMP02	3
7	EMP02	4
8	EMP02	8
9	EMP03	1
10	EMP03	2
11	EMP03	3
12	EMP03	4
Sheet2

 

[Sakshine]

Is this what you are looking for? If not, show us a mock up of what your result should look like.

Book21
	A	B
1	EMPLOYEE	Dates.Attribute
2	EMP01	1
3	EMP01	2
4	EMP01	4
5	EMP01	5
6	EMP02	3
7	EMP02	4
8	EMP02	8
9	EMP03	1
10	EMP03	2
11	EMP03	3
12	EMP03	4
Sheet2

A-SHIFT A	B-SHIFT B	MONTH-JAN
EMPLOYEE	1	2	3	4	5	6	7	8	DATES OF SHIFT A
EMP01	A	A	B	A	A	B	B	B	1,2,4
EMP02	B	B	A	A	B	B	B	A
EMP03	A	A	A	A	B	B	B	B

Result should look like this. In the last column all the dates should get for shift A.
In the 2nd row that numbers 1 2 3 4 upto 8 are dates.

 

[MARK858]

Try the function below (usage in column J), if you ever get Excel 2019 (or later) or Office 365 then you can use the formula in column K (please note if using Excel 2019 then you have to enter the formula with Ctrl + Shift + Enter and not just Enter).

Function myTextJoin(Delimiter As String, myLetter As String, text_range As Range) As String

    Application.Volatile
    Dim myCell As Range, myCounter As Long
    myCounter = 0


    For Each myCell In text_range
        If myCell = myLetter Then
 
                If myCounter = 0 Then
                    myTextJoin = Cells(3, myCell.Column).Value ' change the 3 to your row number of "dates"
                Else
                    myTextJoin = myTextJoin & Delimiter & Cells(3, myCell.Column).Value ' change the 3 to your row number of "dates"
                End If
 
                myCounter = myCounter + 1
            End If

    Next

End Function

Book1
	A	B	C	D	E	F	G	H	I	J	K
2		A-SHIFT A	B-SHIFT B	MONTH-JAN						DATES OF SHIFT A	2019/Office365
3	EMPLOYEE	1	2	3	4	5	6	7	8
4	EMP01	A	A	B	A	A	B	B	B	1,2,4,5	1,2,4,5
5	EMP02	B	B	A	A	B	B	B	A	3,4,8	3,4,8
6	EMP03	A	A	A	A	B	B	B	B	1,2,3,4	1,2,3,4
Sheet1

Cell Formulas
Range	Formula
J4:J6	J4	=myTextJoin(",","A",$B4:$I4)
K4:K6	K4	=TEXTJOIN(",",TRUE,IF($B4:$I4="A",$B$3:$I$3,""))

 

[Sakshine]

Try the function below (usage in column J), if you ever get Excel 2019 (or later) or Office 365 then you can use the formula in column K (please note if using Excel 2019 then you have to enter the formula with Ctrl + Shift + Enter and not just Enter).

Function myTextJoin(Delimiter As String, myLetter As String, text_range As Range) As String

    Application.Volatile
    Dim myCell As Range, myCounter As Long
    myCounter = 0


    For Each myCell In text_range
        If myCell = myLetter Then

                If myCounter = 0 Then
                    myTextJoin = Cells(3, myCell.Column).Value ' change the 3 to your row number of "dates"
                Else
                    myTextJoin = myTextJoin & Delimiter & Cells(3, myCell.Column).Value ' change the 3 to your row number of "dates"
                End If

                myCounter = myCounter + 1
            End If

    Next

End Function

Book1
	A	B	C	D	E	F	G	H	I	J	K
2		A-SHIFT A	B-SHIFT B	MONTH-JAN						DATES OF SHIFT A	2019/Office365
3	EMPLOYEE	1	2	3	4	5	6	7	8
4	EMP01	A	A	B	A	A	B	B	B	1,2,4,5	1,2,4,5
5	EMP02	B	B	A	A	B	B	B	A	3,4,8	3,4,8
6	EMP03	A	A	A	A	B	B	B	B	1,2,3,4	1,2,3,4
Sheet1

Cell Formulas
Range	Formula
J4:J6	J4	=myTextJoin(",","A",$B4:$I4)
K4:K6	K4	=TEXTJOIN(",",TRUE,IF($B4:$I4="A",$B$3:$I$3,""))

Thank you very very much for this useful information. This is working for me. ?

 

[MARK858]

Happy it helped and welcome to the forum.

Btw, the answer to your question (sort of along the same lines) in the the other thread is you don't have the Morefunc addin.

I am unable to understand this formula on my excel sheet. Can you please help me out?

 

[alansidman]

FYI, After creating the list in post#2 by unpivoting the data in Power Query, I then put the data into Power Pivot and created the following Pivot Table. Just an alternative in case you are at all interested in other means.

Book22
	A	B	C	D	E
1	EMPLOYEE	Dates.Attribute
2	EMP01	1		EMPLOYEE	AShift
3	EMP01	2		EMP01	1,2,4,5
4	EMP01	4		EMP02	4,3,8
5	EMP01	5		EMP03	1,2,4,3
6	EMP02	3
7	EMP02	4
8	EMP02	8
9	EMP03	1
10	EMP03	2
11	EMP03	3
12	EMP03	4
13
Sheet1