Results 1 to 3 of 3

Thread: Priorities in Excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2019
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Priorities in Excel

    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/Year Apples? 2016 2017 2018
    John Apples Apples Oranges
    Emma Apples Apples Apples
    Dave Oranges Apples Oranges

    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.

  2. #2
    Board Regular
    Join Date
    Apr 2018
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Re: Priorities in Excel

    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 by Finalfight40; Feb 1st, 2019 at 06:49 AM.
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

  3. #3
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Post Thanks / Like
    32 Post(s)
    1 Thread(s)

    Cool Re: Priorities in Excel

    or PowerQuery with PivotTable

    Name/Year 2016 2017 2018 Name/Year Value Attribute or Name/Year John
    John Apples Apples Oranges Dave Apples 2017
    Emma Apples Apples Apples Oranges 2016 Value Attribute
    Dave Oranges Apples Oranges 2018 Apples 2016
    Emma Apples 2016 2017
    2017 Oranges 2018
    John Apples 2016
    Oranges 2018

        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name/Year"}, "Attribute", "Value")
        #"Unpivoted Other Columns"
    Last edited by sandy666; Feb 1st, 2019 at 07:46 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know

    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts