Microsoft excel query

TrickyDicky101

Follow these instructions exactly:

1. With this sheet containing the values you want to 'transpose' visible in Excel, select the cell that is at the top left of your data (for example, in your original example, if Item1 was in A1 then select A1, if it was in C4 you would select C4 etc).

2. Open up the Visual Basic Editor (VBE) with Alt+F11 (hold down the Alt key and tap the F11 key)

3. Insert a standard module by going to the menu at the top of the VBE and selecting Insert and then Module

4. In the module that will open up on the right hand side of the screen, copy and paste in the following code (include everything from the "Sub Normalise_Data() line to the "End Sub" line):

Sub Normalise_Data()
Dim vIn As Variant, vOut As Variant
Dim i As Long, j As Long, cnt As Long
Dim rLastRow As Range, rLastColumn As Range
Set rLastRow = Cells.Find(What:="*", After:=[A1], LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlPrevious)
Set rLastColumn = Cells.Find(What:="*", After:=[A1], LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlPrevious)

vIn = Range(ActiveCell, Cells(rLastRow.Row, rLastColumn.Column)).Value2

ReDim vOut(1 To UBound(vIn, 1) * (UBound(vIn, 2) - 1), 1 To 2)
cnt = 0
For i = 1 To UBound(vIn, 1)
    For j = UBound(vIn, 2) To 2 Step -1
        If Not IsEmpty(vIn(i, j)) Then
            cnt = cnt + 1
            vOut(cnt, 1) = vIn(i, 1)
            vOut(cnt, 2) = vIn(i, j)
        End If
    Next j
Next i
Worksheets.Add
[A1].Resize(cnt, 2).Value = vOut
End Sub

5. Run the code by clicking the cursor anywhere in the routine you have just pasted in and then hitting the F5 key

This should, assuming you have followed the steps above, generate a new worksheet with the data in the format you need.

heppy23

Thanks, that is brilliant.