Moving a string of horizontal values to a vertical string

cillowjr

New Member
Joined
Aug 24, 2006
Messages
7
Hello.

I have a list of values. There are three columns and almost hundred rows. The columns are labeled simply 3, 5, and 7. There are random values on the first column; for simplicity, I used a,b,c,d... Also, there are prices within the table. It looks similar to this:
1 2 3
a 10 20 30
b 40 50 60
c 70 80 90
d 100 110 120
e 130 140 150
f 160 170 180
g 190 200 210

My problem is that I need to allign all these values in two columns. The first column would read a1, a2, a3, b1, b2, b3, c1,c2,c3... and the second column would read the respective value, i.e., 10, 20,30, etc. So, what I need looks like this:

a1 10
a2 20
a3 30
b1 40
b2 50
b3 60
c1 70
c2 80
c3 90
d1 100
d2 110
d3 120
e1 130
e2 140
e3 150
f1 160
f2 170
f3 180
g1 190
g2 200
g3 210



Can anybody help me?
Thank you for your help.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,934
Hi

you wan't believe but there is a way to do this using Pivot table:

Follow carefully the insructions:

Data
Pivot table
Multiple consolidation ranges
I will create the page field
Range: (enter the table range)
Add
Finish
Finish
(Now you'll get a new table in new sheet)
Go to the Grand total/Grand total cell and click on it twice
Miracle, miracle

See the result:

Now you can cncatenate the first 2 columns and that's it.

Eli
Book1
ABCD
1RowColumnValue
2a110
3a220
4a330
5b140
6b250
7b360
8c170
9c280
10c390
11d1100
12d2110
13d3120
14e1130
15e2140
16e3150
17f1160
18f2170
19f3180
20g1190
21g2200
22g3210
Sheet2
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
This is one way to achieve what you're after:
Book1
ABCDEFGH
1102030a110
2405060b120
3708090c130
4100110120a240
5130140150b250
6160170180c260
7190200210a370
8b380
9c390
Sheet1


Copy the last three lines down and it should do the trick.

Not very pretty but it works.
 

cillowjr

New Member
Joined
Aug 24, 2006
Messages
7
@Eli:
Unfortunately, my spreadsheet needs to be automated so I can only use macros or formulas.

@Dom:
Unfortunately, I cannot use indirect formula since the values in the rows are not letters like a,b,c etc. They are tickers of companies such as BMW, BBY,DCX,...
 

cillowjr

New Member
Joined
Aug 24, 2006
Messages
7
This is what the file looks like:
3Y 5Y 7Y 10Y
Ask Bid Ask Bid Ask Bid Ask Bid
BMW 7.0 11.0 14.0 16.0 20.0 24.0 27.0 31.0
CONTI 39.5 44.5 68.5 73.5 91.0 96.0 105.5 110.5
DCX* 37.5 40.5 59.5 61.5 72.5 76.5 88.0 91.0
GKNLN* 39.0 47.0 70.0 75.0 85.0 93.0 102.0 110.0
MICH 21.5 25.5 38.0 40.0 48.5 53.5 61.5 66.5
PEUGOT 16.5 20.5 30.5 32.5 41.0 45.0 52.5 56.5
RENAUL 20.0 25.0 37.5 39.5 49.0 54.0 63.0 68.0
VLOF* 46.5 51.5 84.0 89.0 107.5 112.5 127.5 132.5
VLVY* 18.5 23.5 34.5 37.5 44.5 49.5 56.0 61.0
VW 14.0 18.0 26.0 28.0 35.0 39.0 45.0 49.0


and this is what i want it to look like:
(remember only formulas or macros)
ASK BID
BMW 3Y 7 11
BMW 5Y 14 16
BMW 7Y 20 24
BMW 10Y 27 31
CONTI 3Y 39.5 44.5
CONTI 5Y 68.5 73.5
CONTI 7Y 91 96
CONTI 10Y 105.5 110.5
DCX* 3Y 37.5 40.5
DCX* 5Y 59.5 61.5
DCX* 7Y 72.5 76.5
DCX* 10Y 88 91
GKNLN* 3Y 39 47
GKNLN* 5Y 70 75
GKNLN* 7Y 85 93
GKNLN* 10Y 102 110
MICH 3Y 21.5 25.5
MICH 5Y 38 40
MICH 7Y 48.5 53.5
MICH 10Y 61.5 66.5
PEUGOT 3Y 16.5 20.5
PEUGOT 5Y 30.5 32.5
PEUGOT 7Y 41 45
PEUGOT 10Y 52.5 56.5
RENAUL 3Y 20 25
RENAUL 5Y 37.5 39.5
RENAUL 7Y 49 54
RENAUL 10Y 63 68
VLOF* 3Y 46.5 51.5
VLOF* 5Y 84 89
VLOF* 7Y 107.5 112.5
VLOF* 10Y 127.5 132.5
VLVY* 3Y 18.5 23.5
VLVY* 5Y 34.5 37.5
VLVY* 7Y 44.5 49.5
VLVY* 10Y 56 61
VW 3Y 14 18
VW 5Y 26 28
VW 7Y 35 39
VW 10Y 45 49
[/img][/list][/list][/code]
 

Forum statistics

Threads
1,141,718
Messages
5,708,072
Members
421,543
Latest member
SGM

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
Top