Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Vlookup from Pivot Table (Excel 2002)

    Hi

    I would like to do a vlookup for example saleable Item 7509658 for each of the distributor codes based on the pivot table

    ie 7509658 R3 6x0.75L MWe 1334087 Bunzl Catering Supplies 10

    and 7509658 R3 6x0.75L MWe 1334128 Bcs Basingstoke 8.

    But the Item Code is only adjacent to 1334087

    Please see attached sheet , (I am sure you will tell me if this is not the best way to go about this) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup from Pivot Table (Excel 2002)

    What do you mean by VLOOKUP? How do you want to use it?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Vlookup from Pivot Table (Excel 2002)

    You could insert a new Col A and B in front of the pivot table to "fill in the gaps"
    In A5 enter:
    <pre>=IF(C5="",A4,C5)</pre>


    [Note C is Col A in your current file, it moved to "C" when I inserted 2 columns:

    In B5 enter:
    <pre>=A5&E5</pre>


    This is concatenating the "saleable Item" and the "Distributor code".

    Column B should be unique and you can do a vlookup to match in this column (you will match the concatenated value) and extract whatever other column you need. For example:
    <pre>=VLOOKUP("75096581334087",$B$5:$I$31,8,0)</pre>


    will give you the Grand total value in col I (G in your original) if the "saleable Item"& "Distributor code" = "75096581334087" ("saleable Item"="7509658" & "Distributor code" ="1334087")

    You can other info as needed by changing the vlookup...
    Steve

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Vlookup from Pivot Table (Excel 2002)

    Hi Hans

    What I need to do is sum the saleable item sold to each distributor By Migration Batch.

    For example how many of 409780 were sold to Distributor 1335003 and how many were sold to 1335004 etc. with a vlookup I can get
    the quantity by batch number

    I am just trying steve's suggestion at them moment.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Vlookup from Pivot Table (Excel 2002)

    If you are only looking at particular cases, you could do this with array formulas on the original data set and not need the pivot table at all.

    Another way (for individual cases) is to use the "filtering" you have set up with SUBTOTAL function. Subtotal works on giving stats on just the "filtered data"

    Steve

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Vlookup from Pivot Table (Excel 2002)

    Hi Steve

    I really need to see results for all Distributors because it is for forward stock planning based on previous sales.

    I am still working on your origional suggestion. It looks as if it will do what I am looking for, however I will try Subtotals, but I never seem to able to see all the data
    when I use Subtotals.

    Thanks for the suggestion.

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Vlookup from Pivot Table (Excel 2002)

    Just for clarification:
    I am not speaking about using the subtotals from the data menu. I am speaking of using the SUBTOTAL worksheet function to get stats on the items that are filtered with the autofilter. This is a means of summarizing the "visible data" on the data sheet when you select items to filter on

    Steve

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Vlookup from Pivot Table (Excel 2002)

    Hi Steve
    I used your origional suggestion to get my data, and I am them using as you suggest SUBTOTAL(9,E8:E3000) for example.

    Thanks for all your help and guidance.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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