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	Code
	A99	PD1, PD2
			A05	PD2, PS3
			A30

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

[TD="class: xl63"]Name[/TD]
[TD="class: xl63"]PD1[/TD]
[TD="class: xl63"]PD2[/TD]
[TD="class: xl63"]PS3[/TD]
[TD="class: xl63"]MM5[/TD]

[TD="class: xl63"]A99[/TD]
[TD="class: xl63"]X[/TD]
[TD="class: xl63"]X[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]
[/TD]

[TD="class: xl63"]A05[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]X[/TD]
[TD="class: xl63"]X[/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63"]A30[/TD]
[TD="class: xl63"]X[/TD]
[TD="class: xl63"]X[/TD]
[TD="class: xl63"]X[/TD]
[TD="class: xl63"] [/TD]

[TD="colspan: 2"]Formula to say [/TD]

[TD="colspan: 2"]PD1, PD2, PD3[/TD]

</tbody>

 

[sandy666]

try PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]PD1[/td][td=bgcolor:#5B9BD5]PD2[/td][td=bgcolor:#5B9BD5]PS3[/td][td=bgcolor:#5B9BD5]MM5[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Code[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A99[/td][td=bgcolor:#DDEBF7]X[/td][td=bgcolor:#DDEBF7]X[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]A99[/td][td=bgcolor:#E2EFDA]PD1, PD2[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A05[/td][td][/td][td]X[/td][td]X[/td][td][/td][td][/td][td]A05[/td][td]PD2, PS3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A30[/td][td=bgcolor:#DDEBF7]X[/td][td=bgcolor:#DDEBF7]X[/td][td=bgcolor:#DDEBF7]X[/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]A30[/td][td=bgcolor:#E2EFDA]PD1, PD2, PS3[/td][/tr]
[/table]

[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)

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]PD1[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]PD2[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]PS3[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]MM5[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Code[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]A99[/TD]
[TD="bgcolor: #DDEBF7"]X[/TD]
[TD="bgcolor: #DDEBF7"]X[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]A99[/TD]
[TD="bgcolor: #E2EFDA"]PD1, PD2[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]A05[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]A05[/TD]
[TD]PD2, PS3[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]A30[/TD]
[TD="bgcolor: #DDEBF7"]X[/TD]
[TD="bgcolor: #DDEBF7"]X[/TD]
[TD="bgcolor: #DDEBF7"]X[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]A30[/TD]
[TD="bgcolor: #E2EFDA"]PD1, PD2, PS3[/TD]
[/TR]
</tbody>[/TABLE]

[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