Find and Return Multiple Values Across Row and Column Data

[monicamarie]

I have a workbook that tracks time off for employees. There are 3 types of time off: V, P and NP. (Vacation, Personal and No Pay) The worksheet lists the days of the week across the top as column headers and the names are entered in Columns A (Last Name) and B (First Name) going down. Say an employee wants a list of all the days they took off for the year; I want to find a way to pull the data together in a separate worksheet so that it looks like the result below. I can't seem to find a solution to this but I know it must be possible. Any help would be greatly appreciated.

Sample section of worksheet

Last Name	First Name	PTO	Personal	No Pay	1/1	1/2	1/3	1/4	1/5
Avery	Michelle	0	0	0
Brown	Alyssa	0	0	0
Chase	Linda	0	0.5	0			P (4)
Lett	Andrew	0	0	0
Marino	Daniel	0	0	0
Matthews	Ken	0.5	0.5	0			P(4)&V(4)
Michaelson	Melinda	2	1	0		V	V	NP	P
Smith	Mike	1	0	1	V			NP

Say for example I want to give Melinda a list of days that she did not work. This is the desired result:

Date	Time Off Type
1/2	V
1/3	V
1/4	NP
1/5	P

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[alansidman]

Power Query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Last Name", "First Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] <> "No Pay" and [Attribute] <> "Personal" and [Attribute] <> "PTO") and ([Last Name] = "Michaelson"))
in
    #"Filtered Rows"

Book1
	A	B	C	D	E	F	G	H	I	J
1	Last Name	First Name	PTO	Personal	No Pay	1-Jan	2-Jan	3-Jan	4-Jan	5-Jan
2	Avery	Michelle	0	0	0
3	Brown	Alyssa	0	0	0
4	Chase	Linda	0	0.5	0			P (4)
5	Lett	Andrew	0	0	0
6	Marino	Daniel	0	0	0
7	Matthews	Ken	0.5	0.5	0			P(4)&V(4)
8	Michaelson	Melinda	2	1	0		V	V	NP	P
9	Smith	Mike	1	0	1	V			NP
10
11
12	Last Name	First Name	Attribute	Value
13	Michaelson	Melinda	2-Jan	V
14	Michaelson	Melinda	3-Jan	V
15	Michaelson	Melinda	4-Jan	NP
16	Michaelson	Melinda	5-Jan	P
17
Sheet1

 

[monicamarie]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sorry. I should have mentioned that. I am using Excel 360. I updated that in my account details. Thanks for the reminder.

 

[Fluff]

Thanks for that.
How about

++Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Last Name	First Name	PTO	Personal	No Pay	01-Jan	01-Feb	01-Mar	01-Apr	01-May
2	Avery	Michelle	0	0	0							Melinda	01/02/2022	V
3	Brown	Alyssa	0	0	0							Michaelson	01/03/2022	V
4	Chase	Linda	0	0.5	0			P (4)					01/04/2022	NP
5	Lett	Andrew	0	0	0								01/05/2022	P
6	Marino	Daniel	0	0	0
7	Matthews	Ken	0.5	0.5	0			P(4)&V(4)
8	Michaelson	Melinda	2	1	0		V	V	NP	P
9	Smith	Mike	1	0	1	V			NP
10
Data

Cell Formulas
Range	Formula
M2:N5	M2	=LET(f,TRANSPOSE(FILTER(F1:J9,(B1:B9=L2)*(A1:A9=L3)+(ROW(A1:A9)=1))),FILTER(f,INDEX(f,,2)<>0))
Dynamic array formulas.

 

[monicamarie]

Thanks for that.
How about

++Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Last Name	First Name	PTO	Personal	No Pay	01-Jan	01-Feb	01-Mar	01-Apr	01-May
2	Avery	Michelle	0	0	0							Melinda	01/02/2022	V
3	Brown	Alyssa	0	0	0							Michaelson	01/03/2022	V
4	Chase	Linda	0	0.5	0			P (4)					01/04/2022	NP
5	Lett	Andrew	0	0	0								01/05/2022	P
6	Marino	Daniel	0	0	0
7	Matthews	Ken	0.5	0.5	0			P(4)&V(4)
8	Michaelson	Melinda	2	1	0		V	V	NP	P
9	Smith	Mike	1	0	1	V			NP
10
Data

Cell Formulas
Range	Formula
M2:N5	M2	=LET(f,TRANSPOSE(FILTER(F1:J9,(B1:B9=L2)*(A1:A9=L3)+(ROW(A1:A9)=1))),FILTER(f,INDEX(f,,2)<>0))
Dynamic array formulas.

Wow, this works beautifully! I would not have been able to come up with such a complex formula so thank you very much for your help.

 

[monicamarie]

Power Query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Last Name", "First Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] <> "No Pay" and [Attribute] <> "Personal" and [Attribute] <> "PTO") and ([Last Name] = "Michaelson"))
in
    #"Filtered Rows"

Book1
	A	B	C	D	E	F	G	H	I	J
1	Last Name	First Name	PTO	Personal	No Pay	1-Jan	2-Jan	3-Jan	4-Jan	5-Jan
2	Avery	Michelle	0	0	0
3	Brown	Alyssa	0	0	0
4	Chase	Linda	0	0.5	0			P (4)
5	Lett	Andrew	0	0	0
6	Marino	Daniel	0	0	0
7	Matthews	Ken	0.5	0.5	0			P(4)&V(4)
8	Michaelson	Melinda	2	1	0		V	V	NP	P
9	Smith	Mike	1	0	1	V			NP
10
11
12	Last Name	First Name	Attribute	Value
13	Michaelson	Melinda	2-Jan	V
14	Michaelson	Melinda	3-Jan	V
15	Michaelson	Melinda	4-Jan	NP
16	Michaelson	Melinda	5-Jan	P
17
Sheet1

This looks great. I just need to train myself on power queries. Thank you so much.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[alansidman]

Thanks for the feedback. This might help you to get started Master Your Data with Power Query in Excel and Power BI