Announcement

Collapse
No announcement yet.

Lookup Transposed Table

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Lookup Transposed Table

    Dear All,

    This is my first time to ask a question...
    I did my best to describe my situation
    Hopefully it will be clear enough to understand
    If not, I do apologize for any inconvenience.

    Problem:
    I have generated a report from my system that shows
    which customer bought which item # and its qty.
    There are approx. at least 500 item/customer to go though,
    is it possible for me to do vlookup or other formula into
    sheet 2 that will show the qty # for each customer?
    (pix #1 into pix #2- possible?)

    Pix #1:

    http://img176.imageshack.us/img176/2606/01km1.jpg

    into this table...

    Pix #2:

    http://img140.imageshack.us/img140/6250/02ah1.jpg

    - If I need to attach my working file, please let me know.

    Thanks for the help,
    K
    Last edited by Dave Hawley; November 28th, 2006, 13:10. Reason: wrong description

  • #2
    Re: Vlookup into a new table

    Hi,

    In the last column of pix #1 and copied down,

    =A2&"#"&B2

    where a2 houses customer # and b2 houses item #

    now in b2 on pix #2 and copied down & across,

    =SUMIF(Sheet1!$D$2:$D$7,B$1&"#"&$A2,Sheet1!$C$2:$C$7)

    where;

    Sheet1!$D$2:$D$7 - concatenated range

    B$1 - customer #

    $A2-item #

    Sheet1!$C$2:$C$7 - Qty range

    adjust the range to suit.

    HTH
    Kris

    ExcelFox

    Comment


    • #3
      Re: Vlookup into a new table

      Use Pivot Tables with the Customer # in the Column Field, Item # in the Row Field and Qty in the Data Field.

      Comment


      • #4
        Re: Lookup Transposed Table

        Thank you Krishnakumar and Dave Hawley for the help.

        Comment

        Working...
        X