Priorities in Excel

[samlim1014]

Hi everyone,

I have a data set like the following.
Unfortunately, my data is a lot more complex than this so a simple Pivot won't let me analyse it

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name/Year[/TD]
[TD]Apples?[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD]Apples[/TD]
[TD]Oranges[/TD]
[/TR]
[TR]
[TD]Emma[/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD]Apples[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD][/TD]
[TD]Oranges[/TD]
[TD]Apples[/TD]
[TD]Oranges[/TD]
[/TR]
</tbody>[/TABLE]

How can I show in the first Column that John liked Apples in 2016 and 2017. I don't mind concatenations eg 2016Apples2017Apples; just can't seem to figure out how. :/

Hope this makes sense. Thank you.

 

[Finalfight40]

Would something like this work for you?

This will return the years which have Apples.

=TEXTJOIN(", ", TRUE, IF(C2:E2="Apples", $C$1:$E$1, ""))

This is an array so enter with Ctrl, Shift and Enter.

 

[sandy666]

or PowerQuery with PivotTable

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name/Year[/td][td=bgcolor:#5B9BD5]2016[/td][td=bgcolor:#5B9BD5]2017[/td][td=bgcolor:#5B9BD5]2018[/td][td][/td][td=bgcolor:#DDEBF7]Name/Year[/td][td=bgcolor:#DDEBF7]Value[/td][td=bgcolor:#DDEBF7]Attribute[/td][td][/td][td]or[/td][td=bgcolor:#DDEBF7]Name/Year[/td][td=bgcolor:#DDEBF7]John[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]John[/td][td=bgcolor:#DDEBF7]Apples[/td][td=bgcolor:#DDEBF7]Apples[/td][td=bgcolor:#DDEBF7]Oranges[/td][td][/td][td]Dave[/td][td]Apples[/td][td]2017[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Emma[/td][td]Apples[/td][td]Apples[/td][td]Apples[/td][td][/td][td][/td][td]Oranges[/td][td]2016[/td][td][/td][td][/td][td=bgcolor:#DDEBF7]Value[/td][td=bgcolor:#DDEBF7]Attribute[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Dave[/td][td=bgcolor:#DDEBF7]Oranges[/td][td=bgcolor:#DDEBF7]Apples[/td][td=bgcolor:#DDEBF7]Oranges[/td][td][/td][td][/td][td][/td][td]2018[/td][td][/td][td][/td][td]Apples[/td][td]2016[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]Emma[/td][td]Apples[/td][td]2016[/td][td][/td][td][/td][td][/td][td]2017[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]2017[/td][td][/td][td][/td][td]Oranges[/td][td]2018[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]2018[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]John[/td][td]Apples[/td][td]2016[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]2017[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Oranges[/td][td]2018[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name/Year"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]