Results 1 to 5 of 5

Thread: extracting data from table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular neilp's Avatar
    Join Date
    Jul 2004
    Location
    Wirral
    Posts
    499
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default extracting data from table

    Hi

    I have a long series of data that contains names in column A, Products in column B and quantities in column C
    eg:




































    A B C
    John Apples 7
    Steve Apples 4
    Pete Bananas 2
    John Melon 1
    Pete Apples 6
    Dave Bananas 3




    I need to be able to move the quantites into a pre built table that has a list of the names down in 1 column and a list of the products across the top row.
    eg































    Name Apples Bananas Melon
    John
    Steve
    Pete
    Dave



    Anyone able to help?

    thanks

    Neil

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,812
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: extracting data from table

    using PowerQuery (Get&Transform)

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table31"]}[Content],
        #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Fruit]), "Fruit", "Qty", List.Sum)
    in
        #"Pivoted Column"
    Name Fruit Qty Name Apples Bananas Melon
    John Apples
    7
    Dave
    3
    Steve Apples
    4
    John
    7
    1
    Pete Bananas
    2
    Pete
    6
    2
    John Melon
    1
    Steve
    4
    Pete Apples
    6
    Dave Bananas
    3
    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

  3. #3
    Board Regular neilp's Avatar
    Join Date
    Jul 2004
    Location
    Wirral
    Posts
    499
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: extracting data from table

    Hi Sandy

    Thanks for the reply, but you lost me at "PowerQuery".

    How do i use that code?

    thanks

    Neil

  4. #4
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: extracting data from table

    Hi, here is a formula option you could try.

    Excel 2013/2016
    ABCDEFGH
    1JohnApples7NameApplesBananasMelon
    2SteveApples4John701
    3PeteBananas2Steve400
    4JohnMelon1Pete620
    5PeteApples6Dave030
    6DaveBananas3

    Sheet1



    Worksheet Formulas
    CellFormula
    F2=SUMIFS($C:$C,$A:$A,$E2,$B:$B,F$1)

    [code]your code[/code]

  5. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,812
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: extracting data from table

    Quote Originally Posted by neilp View Post
    Thanks for the reply, but you lost me at "PowerQuery".
    How do i use that code?
    1. what is your excel version, less than 2010 or higher?
    2. what is PowerQuery
    Last edited by sandy666; Mar 15th, 2019 at 07:33 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
  •