Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
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. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

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

HTH

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.

4. I agreed to these rules
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

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

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