Excel crosstab into columns 2

[KellyK]

I am trying to convert data from a crosstab type format into columns. I believe I am on the right track and have found a previous Tek-Tips thread that got me about halfway there, but I'm stuck. Here is what I am trying to do:

Current format:
1 2 3 4 5 23
b 325 2 56 7 4 x
c 46346 5364563 4 4 3 d
d 2 3 3 3 3 s
e 2 3 3 3 3 h
f 2 3 3 3 3 d

Desired format:
b 1 325
b 2 2
b 3 56
...

or even

b 1 325
c 1 46346
...

The order of the data is not important, I can sort after the fact. I need three columns with all the data from the table.

Here is what I have so far:
colA colB
b 1
c 2
d 3
e 4
f 5

by using the formula "=INDEX($A:$A,(COLUMNS($A:A)-1)+1+ROWS($2:2))" in colA and "=INDEX($1:$1,(ROWS($1:1)-1)+1+COLUMNS($A:A))" in colB. I do not know how to begin to get a third column with the data, nor do I know how to get the colA and B to repeat for each value in the table. If anyone could guide me, I would appreciate it!





Kelly

 

[GlennUK]

Use the hints given in Skip's excellent FAQ:

NORMALIZE Your Table using the PivotTable Wizard faq68-5287


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[KellyK]

Thanks Glenn! Worked like a charm.

Kelly

 

[yogia]

Hi Kelly:

If you want a formula based solution, following is one way ...

   |A       B       C       D       E       F       G
---|----------------------------------------------------
 1 |	    1	   2	   3	   4	   5	   23
 2 |b	   325	 2	   56	  7	   4	   x
 3 |c	   46346   5364563 4	   4	   3	   d
 4 |d	   2	   3	   3	   3	   3	   s
 5 |e	   2	   3	   3	   3	   3	   h
 6 |f	   2	   3	   3	   3	   3	   d
 7 |						
 8 |						
 9 |						
10 |rearranged in A11:G15 using formula based solution	
11 |b	   1	   325	46346	2	   2	   2
12 |c	   2	   2	  5364563  3	   3	   3
13 |d	   3	   56	 4	    3	   3	   3
14 |e	   4	   7	  4	    3	   3	   3
15 |f	   5	   4	  3	    3	   3	   3

formula in cell A11 is ... =INDEX($A:$A,ROWS($1:2))
this is then copied to cells A11:A15

formula in cell B11 is ... =INDEX($A$1:$G$6,COLUMNS($A:A),ROWS($1:2))
this is then copied to cells B11:G15

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[mscallisto]

Hmmm To convert rows to columns and columns to rows:

highlight and copy the table
select a cell to place your new table
Edit > Paste Special > and choose transpose

 

[yogia]

Hi Kelly:

The solution proposed by Glenn using Skip's Pivot Table solution is the way to go ... that is a Beauty.

I may have misinterpreted what you were trying to accomplish ... so the formula based solution that I posted may not be what you were looking for.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[KellyK]

Yogi, no problem. In fact, it was your response to the other thread that got me as far as I got in the first place, so kudos to you as well for schooling me on the "index" function

mscalisto, that is not what I'm trying to do but I appreciate your response.



Kelly

 

[mscallisto]

Excellent post/referral GlennUK

that will most likely be very useful!

sam