Priorities in Excel

samlim1014

New Member
Joined
Feb 1, 2019
Messages
1
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 :(

Name/YearApples?201620172018
JohnApplesApplesOranges
EmmaApplesApplesApples
DaveOrangesApplesOranges

<tbody>
</tbody>

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

Active Member
Joined
Apr 24, 2018
Messages
265
Office Version
365
Platform
Windows
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.
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,314
or PowerQuery with PivotTable

Name/Year201620172018Name/YearValueAttributeorName/YearJohn
JohnApplesApplesOrangesDaveApples2017
EmmaApplesApplesApplesOranges2016ValueAttribute
DaveOrangesApplesOranges2018Apples2016
EmmaApples20162017
2017Oranges2018
2018
JohnApples2016
2017
Oranges2018

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

Forum statistics

Threads
1,086,093
Messages
5,387,793
Members
402,076
Latest member
MotoMoto

Some videos you may like

This Week's Hot Topics

Top