Transpose Data with fixed first column

[lisa]

I am trying to transpose data, I thought of using a pivot table, but my data
would exceed the 256 columns going across, so is there another way to the
following:

Existing Data looks like this:
A B
1 Product Type
2 Icecream Cholocate
3 Icecream Strawberry
4 Icecream Vanilla
5 Coffee Hazelnut
6 Coffee Pumpkin
7 Tea Green
8 Tea Black
9 Tea Chai
10 Tea White


I want to transpose data to looks like this:

Product Type Type Type Type
Icecream Cholocate Strawberry Vanilla
Coffee Hazelnut Pumpkin
Tea Green Black Chai White

Any help will be great.

 

[Suleman Peerzade]

Hi Lisa,

You can do it this way.
suppose your data heading is in A1 and B1
as required you can paste the headers on new row or column then you can
further paste the product items and number of types. Now we are ready to use
transpose you can enter the formula as mentioned below. i have also given an
example for that.
{=TRANSPOSE(B2:B5)} (this is a direct formula)

to go step wise highlight the rows you want the transposed data in and then
enter the formula =transpose(B2:B5) then press Ctrl+Shit+enter and the data
would be transposed and the formula will look like i have pasted above for
you.



Product Type
Icream chocholate
Icream strawberry
Icream pineapple
Icream mango
Tea lemon
Tea cardimon
Tea ginger
Coffee Bru
Coffee classic
Coffee chocholate


Product Type Type Type Type Type
Icream chocholate strawberry pineapple mango
Tea lemon cardimon ginger #N/A
Coffee Bru classic chocholate

Please press the useful button if i have helped you out.
Ver. Excel 2003

 

[Max]

Here's another play which delivers the exact results that you seek ..

Illustrated in this sample:
http://freefilehosting.net/download/443ci
Transpose n Summarize.xls

Construct
Assume source data as posted is in sheet: x, cols A & B, in row2 down

In x,
In D2: =IF(A2="","",IF(COUNTIF(A$2:A2,A2)>1,"",ROW()))
Copy down to cover max expected extent of data in col A
Leave D1 empty

In E1:
=INDEX($A:$A,SMALL($D:$D,COLUMNS($A:A)))
Copy to G1

In E2: =IF($A2="","",IF($A2=E$1,ROW(),""))
Copy across/fill down

Then in sheet: Transposed Summary
In A2, copied down to A4:
=INDEX(x!$E$1:$G$1,ROWS($1:1))

In B2, copied across/filled down
=IF(COLUMNS($A:A)>COUNT(OFFSET(x!$D:$D,,MATCH($A2,x!$E$1:$G$1,0))),"",INDEX(x!$B:$B,SMALL(OFFSET(x!$D:$D,,MATCH($A2,x!$E$1:$G$1,0)),COLUMNS($A:A))))

Above will return the exact results that you seek ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Hardeep_kanwar]

Hey Suleman

I have facing the Same problem. i try your Function but it is not Working
even with the same example give by you. it Look like this:

Product Type chocholate strawberry pineapple mango
Icream chocholate strawberry pineapple mango lemon
Icream strawberry pineapple mango lemon cardimon
Icream pineapple mango lemon cardimon ginger
Icream mango lemon cardimon ginger Bru
Tea lemon cardimon ginger Bru classic
Tea cardimon ginger Bru classic chocholate
Tea ginger Bru classic chocholate 0
Coffee Bru classic chocholate 0 0
Coffee classic chocholate 0 0 0
Coffee chocholate 0 0 0 0


Hardeep kanwar