Formula problem and a pivot does not help...please can you?

[dollyann]

Hi

I have an excel query and i can not figure it out:

I have a list of names in column A and Dates along in Row 1. And under the dates by the names are zeros or BH (Bank Holiday), W (worked), H (Hol) & HF (Half Day Hol)

I am trying to get results by looking at the name i want to know when they have holiday (H) or half day holiday (HF)and to give me the date

For Example:

My Table look like:

01/01/19 02/01/2019 03/01/2019 04/01/2019
Tony H W 0 H
John 0 0 0 HF
Sarah 0 0 HF 0
David H H H H
Steve 0 0 0 0

And i would like my results to look like:

Tony 01/01/19, 04/01/2019
John 04/01/19
Sarah 03/01/19
David 01/01/19,02/01/19, 03/01/19, 04/01/19
Steve

I have tried pivot table and just can't get it to work Does anybody have any ideas to help me

Really appriecate any help.... Please!!

 

[sandy666]

if you have PowerQuery (Get&Transform) you can try this

Name	01/01/2019	02/01/2019	03/01/2019	04/01/2019		Name	Date.1	Date.2	Date.3	Date.4
Tony	H	W	0​	H		Tony	01/01/2019​	04/01/2019​
John	0​	0​	0​	HF		John	04/01/2019​
Sarah	0​	0​	HF	0​		Sarah	03/01/2019​
David	H	H	H	H		David	01/01/2019​	02/01/2019​	03/01/2019​	04/01/2019​
Steve	0​	0​	0​	0​		Mark	01/01/2019​	04/01/2019​
Mark	H	W		H

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table20"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "H" or [Value] = "HF")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Date", each Table.Column([Count],"Attribute")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Date", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Date", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3", "Date.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", type date}, {"Date.2", type date}, {"Date.3", type date}, {"Date.4", type date}})
in
    #"Changed Type"[/SIZE]

 

[dollyann]

if you have PowerQuery (Get&Transform) you can try this

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]01/01/2019[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]02/01/2019[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]03/01/2019[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]04/01/2019[/COLOR]		[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date.1[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date.2[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date.3[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date.4[/COLOR]
Tony	H	W	0​	H		Tony	01/01/2019​	04/01/2019​
John	0​	0​	0​	HF		John	04/01/2019​
Sarah	0​	0​	HF	0​		Sarah	03/01/2019​
David	H	H	H	H		David	01/01/2019​	02/01/2019​	03/01/2019​	04/01/2019​
Steve	0​	0​	0​	0​		Mark	01/01/2019​	04/01/2019​
Mark	H	W		H

<tbody>
</tbody>

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table20"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "H" or [Value] = "HF")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Date", each Table.Column([Count],"Attribute")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Date", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Date", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3", "Date.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", type date}, {"Date.2", type date}, {"Date.3", type date}, {"Date.4", type date}})
in
    #"Changed Type"[/SIZE]

Hi, thank you for responding, I don't have Power Query, and sadly i don't know how to write code. I am just basic Excel Ay our thoughts which cold help me? or am I asking the impossible??

 

[sandy666]

Almost everything is possible in excel

PowerQuery add-in Excel 2010/2013
PowerQuery aka Get&Transform built-in Excel 2016 and higher

you need to wait for someone else for solution different than PQ

Have a nice day

 

[AlanY]

may be

Book1
	A	B	C	D	E
1		01/01/2019	02/01/2019	03/01/2019	04/01/2019
2	Tony	H	W	0	H
3	John	0	0	0	HF
4	Sarah	0	0	HF	0
5	David	H	H	H	H
6	Steve	0	0	0	0
7
8
9	Tony	01/01/2019	04/01/2019
10	John	04/01/2019
11	Sarah	03/01/2019
12	David	01/01/2019	02/01/2019	03/01/2019	04/01/2019
13	Steve
Sheet8

Cell Formulas
Range	Formula
B9	{=IFERROR(SMALL(IF((INDEX($B$2:$E$6,MATCH($A9,$A$2:$A$6,0),)="H")+(INDEX($B$2:$E$6,MATCH($A9,$A$2:$A$6,0),)="HF"),$B$1:$E$1),COLUMN(B$1)-COLUMN($B$1)+1),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AlanY]

Almost everything is possible in excel

black, no sugar please

 

[sandy666]

black, no sugar please

.

Almost everything

 

[AlanY]

.

ok, almost black and almost sugar please

 

[sandy666]

ok, almost black and almost sugar please

sure,

 

[AlanY]

cheers, that's what I needed