How to convert columns data into row?

A

azii

Board Regular
Joined
May 26, 2011
Messages
80
Please help me solving this issue

This is the data I have in my excel sheet.
Ware HouseProductToday Sale
APENP50
CAN30
CAN-C80
UREA90
BWP-1NP100
CAN250
CAN-C330
UREA150
BWP-2NP200
CAN210
CAN-C320
UREA400

<tbody>
</tbody>
I want to change this data in other sheet like............
Date 06-03-2013
Ware HouseNPCANCAN-CUREA
APE50308090
BWP-1100250330150
BWP-2200210320400

<tbody>
</tbody>

there is no way using transpose
my excel data has 100s of columns and rows.
Please help me resolve this query.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
You threw me off with the "hundreds of columns" bit for your data. The number of rows dows not matter, and I suspect that the number of columns will not matter either, but it would be significant to know what we are dealing with. So, you show 3 columns, with headers Warehouse, Product, and Today. Are the other hundreds of columns going back in time by a day, maybe showing Sales for a year, 365 ("100s") columns? Please clarify what your original data looks like.
 
Upvote 0
Dear Tom Urtis
I am glad to have your response.
here is first table specification
1 2 3 4 ........ are the dates (e.g. 01-03-2013, 02-03-2013 and so on)
this fill data is comming from another sheet date wise.
Now Ahmedpur East 1 (Warehouse) has today sale as on 01-03-2013 is 50 for NP, 100 for CAN, 150 for CAN-C and 200 for UREA.
I want to put these values of Ahmedpur East 1 in Table 2 in front of Ahmedpur East 1 (only for date 01-03-2013)
this is my 1 of 10 regions. Each region has different number of Warehouses.

Once again thanks for your kind interest. Please help resolve this query.

Table 1
Warehouse Prod 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 TOTAL=
Ahmedpur East 1NP 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00 1,550.00
CAN 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 3,100.00
CAN-C 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 4,650.00
WH codeUREA 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 6,200.00
Bahawal Pur 1NP 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 111.00 3,441.00
CAN - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN-C - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UREA - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Bahawal Pur 2NP - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN-C - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UREA - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Chishtian 1NP - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN-C - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UREA - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Kehror Pacca 1NP - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN-C - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UREA - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Adda ZakheeraNP - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN-C - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UREA - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Bahawalnagar 1NP - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN-C - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UREA - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Bahawalnagar 2NP - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN-C - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UREA - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
DunyapurNP - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN-C - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UREA - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Hasil Pur NP - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN-C - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UREA - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
LodhranNP - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN-C - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UREA - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
MadrissaNP - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN-C - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UREA - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
YazmanNP - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN-C - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UREA - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Yazman-2NP - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CAN-C - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UREA - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

<colgroup><col><col span="32"><col></colgroup><tbody>
</tbody>



result sheet (data should be in this format)

Table 2
TODAY SALE
NP CAN CAN-C UREA
Ahmedpur East 1
Bahawal Pur 1
Bahawal Pur 2
Chishtian 1
Kehror Pacca 1
Adda Zakheera
Bahawalnagar 1
Bahawalnagar 2
Dunyapur
Hasil Pur
Lodhran
Madrissa
Yazman
Yazman-2

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
Hello Azii,

Perhaps pivot tables will solve the problem?

I've looked at your original table which I suspect was not aligned as expected. To prepare the data to be converted to pivot tables I've filled the first column with the warehouse names like this:-

<code>Warehouse Prod 01/03/2013 02/03/2013 03/03/2013
Ahmedpur East 1 NP 50 50 50
Ahmedpur East 1 CAN 100 100 100
Ahmedpur East 1 CAN-C 150 150 150
Ahmedpur East 1 UREA 200 200 200
Bahawal Pur 1 NP 111 111 111
Bahawal Pur 1 CAN - - -
Bahawal Pur 1 CAN-C - - -
Bahawal Pur 1 UREA - - -
Bahawal Pur 2 NP - - -
Bahawal Pur 2 CAN - - -
Bahawal Pur 2 CAN-C - - -
Bahawal Pur 2 UREA - - -
</code>

Now you should be able to convert into a pivot table like this:- (extract shown)


<code> Prod
Data Warehouse CAN CAN-C NP UREA
01/03/2013 Adda Zakheera 0 0 0 0
Ahmedpur East 1 100 150 50 200
Bahawal Pur 1 0 0 111 0
Bahawal Pur 2 0 0 0 0
Bahawalnagar 1 0 0 0 0
Bahawalnagar 2 0 0 0 0
Chishtian 1 0 0 0 0
Dunyapur 0 0 0 0
Hasil Pur 0 0 0 0
Kehror Pacca 1 0 0 0 0
Lodhran 0 0 0 0
Madrissa 0 0 0 0
Yazman 0 0 0 0
Yazman-2 0 0 0 0

02/03/2013 Adda Zakheera 0 0 0 0
Ahmedpur East 1 100 150 50 200
Bahawal Pur 1 0 0 111 0
Bahawal Pur 2 0 0 0 0
Bahawalnagar 1 0 0 0 0
Bahawalnagar 2 0 0 0 0
Chishtian 1 0 0 0 0
Dunyapur 0 0 0 0
Hasil Pur 0 0 0 0
Kehror Pacca 1 0 0 0 0
Lodhran 0 0 0 0
Madrissa 0 0 0 0
Yazman 0 0 0 0
Yazman-2 0 0 0 0</code>

All the fields with the date columns are added to the "data" section, which afterwards can be dragged to the leftmost part of the pivot table to order the information by date.

Apologies in advance for the state of the table; I'm at work right now (bad I know!) and I'm not able to post the tables properly.

Any questions please let me know.
 
Upvote 0
Hopefully this will be easier to read.
Extract of original data table:
Code: 
Warehouse             Prod   01/03/2013            02/03/2013         03/03/2013
Ahmedpur East 1	NP	50	             50	             50
Ahmedpur East 1	CAN	100	             100	             100
Ahmedpur East 1	CAN-C	150	             150	             150
Ahmedpur East 1	UREA	200	             200	             200
Bahawal Pur 1	NP	111	             111	             111
Bahawal Pur 1	CAN	-	             -	             -
Bahawal Pur 1	CAN-C	-	             -	             -
Bahawal Pur 1	UREA	-	             -	             -
Bahawal Pur 2	NP	-	             -	             -
Bahawal Pur 2	CAN	-	             -	             -
Bahawal Pur 2	CAN-C	-	             -	             -
Bahawal Pur 2	UREA	-	             -	             -


Pivot table:

Code: 
		Prod			
Data	      Warehouse	             CAN	CAN-C	NP	UREA
01/03/2013    Adda Zakheera              0	0	0	0
                   Ahmedpur East 1      100	150	50	200
                   Bahawal Pur 1	        0	0	111	0
                   Bahawal Pur 2	        0	0	0	0
                   Bahawalnagar 1        0	0	0           0
                   Bahawalnagar 2        0	0	0	0
                   Chishtian 1                0	0	0	0
                   Dunyapur	             0	0	0	0
                   Hasil Pur	           0	0	0	0
                   Kehror Pacca 1           0	0	0	0
                   Lodhran	             0	0	0	0
                   Madrissa	             0	0	0	0
                   Yazman	             0	0	0	0
                   Yazman-2                0	0	0	0

 02/03/2013   Adda Zakheera              0	0	0	0
                   Ahmedpur East 1      100	150	50	200
                   Bahawal Pur 1	        0	0	111	0
                   Bahawal Pur 2	        0	0	0	0
                   Bahawalnagar 1        0	0	0           0
                   Bahawalnagar 2        0	0	0	0
                   Chishtian 1                0	0	0	0
                   Dunyapur	             0	0	0	0
                   Hasil Pur	           0	0	0	0
                   Kehror Pacca 1           0	0	0	0
                   Lodhran	             0	0	0	0
                   Madrissa	             0	0	0	0
                   Yazman	             0	0	0	0
                   Yazman-2                0	0	0	0
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Inventory disposal plan
Replies
3
Views
85
Spaceballs_the_fanboy
S
T
  • Solved
Excel formula: how to sum multiple columns on a single criteria
Replies
3
Views
80
TT123
T
M
  • Solved
valid data input
Replies
3
Views
120
mwvirk
M
S
  • Question
Calculate product percentage impact on total percentage
Replies
1
Views
107
etaf
etaf
U
  • Solved
Index formula with matching - Can it look at rows and columns?
Replies
2
Views
166
usnapoleon
U

Forum statistics

Threads
1,216,583
Messages
6,131,557
Members
449,655
Latest member
Anil K Sonawane

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top