Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose Data with fixed first column
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose Data with fixed first column
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+****+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 -- Thanks Suleman Peerzade "lisa" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose Data with fixed first column
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 --- "lisa" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose Data with fixed first column
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 "Suleman Peerzade" wrote: 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+****+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 -- Thanks Suleman Peerzade "lisa" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose column into rows for use as mailmerge data | Excel Discussion (Misc queries) | |||
Transpose data from many horizontal rows into a single column | Excel Discussion (Misc queries) | |||
Transpose Data from a column to several rows | Excel Worksheet Functions | |||
I have a column of data and I'd like to transpose it to go across. | Excel Discussion (Misc queries) | |||
How do I transpose an Excel 2003 column of data? | New Users to Excel |