Looking for help with hlookup and concatenate or index match (newbie)

[amyangel]

I have a data sheet like the below and i want to for each row find the corresponding X and put it into the the below.
I'm not savy with VB and my work computer has Excel 2016.

Thanks for the help.

Name	PD1	PD2	PS3	MM5
A99	X	X
A05		X	X
A30	X	X	X
			Name	Code
	Formula to say	A99	PD1, PD2
			A05	PD2, PS3
			A30	PD1, PD2, PD3

<colgroup><col style="width:48pt" width="64" span="6"> </colgroup><tbody>
</tbody>

 

[sandy666]

try PowerQuery (Get&Transform)

Name	PD1	PD2	PS3	MM5		Name	Code
A99	X	X				A99	PD1, PD2
A05		X	X			A05	PD2, PS3
A30	X	X	X			A30	PD1, PD2, PS3

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Code", each Table.Column([Count],"Attribute")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Code", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    #"Extracted Values"[/SIZE]

 

[amyangel]

try PowerQuery (Get&Transform)

[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] ]PD1[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]PD2[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]PS3[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]MM5[/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] ]Code[/COLOR]
A99	X	X				A99	PD1, PD2
A05		X	X			A05	PD2, PS3
A30	X	X	X			A30	PD1, PD2, PS3

<tbody>
</tbody>

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Code", each Table.Column([Count],"Attribute")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Code", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    #"Extracted Values"[/SIZE]

Hi thanks so much for the quick reply, do you know if there's another way? They administrator lock our computers at work so I am unable to install anything.

 

[sandy666]

my work computer has Excel 2016.

you don't need install anything because Excel 2016 has PowerQuery (Get&Transform) built-in

edit:
and don't quote whole post, usually this is not necessary, please
use Reply instead of Reply With Quote

 

[amyangel]

Thank you it worked!! I appreciate the help so much and it has saved me a lot of time.

 

[sandy666]

You are welcome

Have a nice day