How do I convert list to table in Excel 2003

-1

I have a list:

    date  item name    quantity
1.7.2014    A              5
1.7.2014    B              2
1.7.2014    C              10
2.7.2014    B              5
3.7.2014    A              1
4.7.2014    A              7

I want this list as below table in excel

ITEM NAME    1.7.2014     2.7.2014     3.7.2014       4.7.2014
A               5                        1               7
B               2            5     
C               10

How do I do this?

srinivas

Posted 2014-09-13T12:32:34.737

Reputation: 21

1use Pivot tables – Sathyajith Bhat – 2014-09-13T12:35:32.813

no because pivot tables aggregate data and this is not what is being asked for – adolf garlic – 2015-12-22T07:47:15.483

Answers

As suggestes by @Sathya, use pivot tables as explained by Microsoft in this article.

Long story short:

Select your list
Insert a pivot table (Insert --> Pivot table or Data --> Pivot table, depending on your Excel version).
With your cursor inside the pivot table, put the Item name in the Column labels, Date in Row labels and Quantity in Values (select Sum to sum all quantities for the same date/item name combination).

agtoever

Posted 2014-09-13T12:32:34.737

Reputation: 5 490

Pivot tables aggregate data, this is not what is being asked for. The question actually wants a crosstab from the list and not a pivot table – adolf garlic – 2015-12-21T10:20:58.597

I would use the Power Query Add-In for this. It has a Pivot command that should achieve what you want:

http://www.microsoft.com/en-us/powerBI/bi-blog.aspx?blog=91d46819-8472-40ad-a661-2c78acb4018c%3A10553536&WT.mc_id=OO_PowerQuery_Update#pivot

I've built a solution to a similar problem which you can view or download - its "Power Query demo - Pivot rows into columns.xlsx" in my One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

Mike Honey

Posted 2014-09-13T12:32:34.737

Reputation: 2 119