Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for transpose
I have a scenario in which I need a formula for transposing 2 columns in
which 1st column contains one category (value) and its sub categories are present in second column. The following will be the clear picture. Like the following N no of categories, so I need a formula to transpose based on the category. E1 25 26 27 28 29 30 E2 31 32 33 34 35 I want the result in the following pattern. E1 25 26 27 28 29 30 E2 31 32 33 34 35 Thanking you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for transpose
I'm not sure you'll be able to do this using formulas--especially since each
category can have a different number of subcategories. But you could use a macro. If you want to try, make sure you save your data first--or test against a copy of the data. This macro destroys the original data. Option Explicit Sub testme() Dim TopCell As Range Dim BotCell As Range Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long With Worksheets("Sheet1") FirstRow = 1 LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row Set TopCell = .Cells(FirstRow, "A") For iRow = FirstRow To LastRow If IsEmpty(TopCell.Offset(1, 0).Value) Then Set BotCell = TopCell.End(xlDown).Offset(-1, 0) Else Set BotCell = TopCell End If If BotCell.Row LastRow Then Set BotCell = .Cells(LastRow, "A") End If .Range(TopCell, BotCell).Offset(0, 1).Copy TopCell.Offset(0, 2).PasteSpecial Transpose:=True Set TopCell = BotCell.Offset(1, 0) If TopCell.Row LastRow Then Exit For End If Next iRow On Error Resume Next .Columns(1).Cells.SpecialCells(xlCellTypeBlanks).E ntireRow.Delete On Error GoTo 0 .Columns(2).Delete End With End Sub If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) surya and siva wrote: I have a scenario in which I need a formula for transposing 2 columns in which 1st column contains one category (value) and its sub categories are present in second column. The following will be the clear picture. Like the following N no of categories, so I need a formula to transpose based on the category. E1 25 26 27 28 29 30 E2 31 32 33 34 35 I want the result in the following pattern. E1 25 26 27 28 29 30 E2 31 32 33 34 35 Thanking you. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Transpose | Excel Discussion (Misc queries) | |||
formula to transpose | Excel Worksheet Functions | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions | |||
Formula Transpose | Excel Discussion (Misc queries) | |||
Transpose formula | Excel Worksheet Functions |