Transpose rows of varying length into a single column

[malenoir]

Hi guys,


Spending a lot of time doing this manually and would appreciate some help.


I have a set of data:

[pre]

A  B  C  D  E  F  G
1  #1 - ?1 ?2 ?3 ?4
2  #2 - ?5 ?6
3  #3 - ?7 ?8 ?9
4  #4 - ?10
Where:


# = ID

- = Irrelevant data

? = Relevant data


Which i want to transpose in to the following format:

...A...B
1  #1 ?1
2  #1 ?2
3  #1 ?3
4  #1 ?4
5  #2 ?5
6  #2 ?6
7  #3 ?7
8  #3 ?8
9  #3 ?9
10 #4 ?10

[/pre]
Thank you in advance.

 

[shrivallabha]

Hi malenoir,


Welcome to Chandorg forum.


Does '-' occur only once? Test this on a backup!

[pre]

Public Sub TransposeData()
Dim lLastRow As Long, lLastCol As Long
Application.ScreenUpdating = False

lLastRow = Range("A" & Rows.Count).End(xlUp).Row
lLastCol = Cells.Find("*", [A1], xlFormulas, xlPart, xlByColumns, xlPrevious, False).Column
For i = 1 To lLastRow
Range("A" & i).Copy Cells(Rows.Count, lLastCol + 2).End(xlUp).Offset(1, -1)
Range("C" & i, Cells(i, lLastCol)).SpecialCells(xlCellTypeConstants).Copy
Cells(Rows.Count, lLastCol + 2).End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
Next i

With Range(Cells(2, lLastCol + 1), Cells(Cells(Rows.Count, lLastCol + 2).End(xlUp).Row, lLastCol + 1)).SpecialCells(xlCellTypeBlanks)
.FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With

Application.ScreenUpdating = True
End Sub

[/pre]

 

[malenoir]

shrivallabha,


Managed to solve this issue using a Pivot Table. First assigned each column to the right of the irrelevant column a header of "C1" "C2" etc.


Then selected the data, inserted pivot table, placed the ID column in the 'value' field, and the "C1" "C2" etc. into the 'row labels' field.


Then copy and pasted the table into a separate sheet using 'paste values', and tidied it up using a filter to remove the 'blanks'.


Worked perfectly.