Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Lookup Transposed Table

  1. #1
    Join Date
    28th November 2006
    Posts
    2

    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 at 13:10. Reason: wrong description

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    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

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    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.

  4. #4
    Join Date
    28th November 2006
    Posts
    2

    Re: Lookup Transposed Table

    Thank you Krishnakumar and Dave Hawley for the help.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Create New Unique Transposed List
    By sooner_ou in forum Excel General
    Replies: 6
    Last Post: March 19th, 2008, 00:02
  2. Dates Being Transposed In Query
    By Fabbyfil in forum Excel and/or Access Help
    Replies: 3
    Last Post: December 4th, 2007, 00:15
  3. DSUM equivalent for Transposed data
    By thiagarr in forum Excel General
    Replies: 2
    Last Post: November 5th, 2005, 10:31
  4. Making Access Reports based on Transposed Data
    By benwu in forum Excel and/or Access Help
    Replies: 7
    Last Post: June 22nd, 2005, 01:21
  5. transposed lookup
    By jbilkes in forum Excel General
    Replies: 2
    Last Post: April 28th, 2005, 14:37

Bookmarks

Posting Permissions

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