Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transposing
COL A has 165,000 cells that looks like this:
CONTROL-NBR 1 DATA1 DATA2 DATA3 CONTROL-NBR 2 DATA1 DATA2 DATA3 DATA4 DATA5 DATA6 CONTROL-NBR 3 DATA1 CONTROL-NBR 4 DATA1 DATA2 DATA3 DATA4 DATA5 How can I transpose the "DATA" cells for each "CONTROL-NBR" onto the same row as the "CONTROL-NBR" so the result looks like this:: CONTROL-NBR-1 DATA1 DATA2 DATA3 CONTROL-NBR-2 DATA1 DATA2 DATA3 DATA4 DATA5 DATA6 CONTROL-NBR-3 DATA1 CONTROL-NBR-4 DATA1 DATA2 DATA3 DATA4 DATA5 (Note: The number of "DATA" cells for each "CONTROL-NBR" varies from 1 to 30). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transposing
Do the CONTROL-NBR's start with "CONTROL"?
-- -SA "gary" wrote: COL A has 165,000 cells that looks like this: CONTROL-NBR 1 DATA1 DATA2 DATA3 CONTROL-NBR 2 DATA1 DATA2 DATA3 DATA4 DATA5 DATA6 CONTROL-NBR 3 DATA1 CONTROL-NBR 4 DATA1 DATA2 DATA3 DATA4 DATA5 How can I transpose the "DATA" cells for each "CONTROL-NBR" onto the same row as the "CONTROL-NBR" so the result looks like this:: CONTROL-NBR-1 DATA1 DATA2 DATA3 CONTROL-NBR-2 DATA1 DATA2 DATA3 DATA4 DATA5 DATA6 CONTROL-NBR-3 DATA1 CONTROL-NBR-4 DATA1 DATA2 DATA3 DATA4 DATA5 (Note: The number of "DATA" cells for each "CONTROL-NBR" varies from 1 to 30). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transposing
If CONTROL is at the begining of the data sets, then the following macro
should reorganize your data properly. I tested it on your example data set and it works. Hope this helps! Option Explicit Sub ColToRows() Dim curselection As Range Set curselection = Range("A1") 'or wherever you start Do If curselection.Offset(0, 1) = "" Then If Not curselection.Offset(1, 0).Value Like "CONTROL*" Then curselection.Offset(1, 0).Cut Destination:=curselection.Offset(0, 1) curselection.Offset(1, 0).EntireRow.Delete End If End If datafound: If Not curselection.Offset(1, 0).Value Like "CONTROL*" Then curselection.Offset(1, 0).Cut Destination:=curselection.End(xlToRight).Offset(0, 1) curselection.Offset(1, 0).EntireRow.Delete If curselection.Offset(1, 0) = "" Then Exit Do GoTo datafound End If Set curselection = curselection.Offset(1, 0) Loop End Sub -- -SA "gary" wrote: COL A has 165,000 cells that looks like this: CONTROL-NBR 1 DATA1 DATA2 DATA3 CONTROL-NBR 2 DATA1 DATA2 DATA3 DATA4 DATA5 DATA6 CONTROL-NBR 3 DATA1 CONTROL-NBR 4 DATA1 DATA2 DATA3 DATA4 DATA5 How can I transpose the "DATA" cells for each "CONTROL-NBR" onto the same row as the "CONTROL-NBR" so the result looks like this:: CONTROL-NBR-1 DATA1 DATA2 DATA3 CONTROL-NBR-2 DATA1 DATA2 DATA3 DATA4 DATA5 DATA6 CONTROL-NBR-3 DATA1 CONTROL-NBR-4 DATA1 DATA2 DATA3 DATA4 DATA5 (Note: The number of "DATA" cells for each "CONTROL-NBR" varies from 1 to 30). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help with transposing | Excel Discussion (Misc queries) | |||
Transposing | Excel Discussion (Misc queries) | |||
Transposing | Excel Worksheet Functions | |||
TRANSPOSING | Excel Programming | |||
TRANSPOSING | Excel Discussion (Misc queries) |