Something like this:

Sub TransposeColumns()
'Start row of destination
i = 2
'Range of Departments
For Each cell In Range("C2:F10")

If cell.Value = "X" Then
xRow = cell.Row
xColumn = cell.Column

'Change first part of formula to destination
'columns as desired
Cells(i, "G").Value = Cells(xRow, "A")
Cells(i, "H").Value = Cells(xRow, "B")
Cells(i, "I").Value = Cells(1, xColumn)
i = i + 1
End If
Next

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jason" wrote:

Hi there

I have a spreadsheet that contains approx 584 rows and 163 columns. Column A
= Account, Column B = Description, Columns C+ = Department.

Something like this

ACCT DESCRIPTION 1101 2140 3125 6179
12345 Example 1 X
22588 Example 1 X X X
33244 Example 1 X X
78544 Example 1 X X X
78545 Example 1 X X

Is it possible to use VBA to achieve the following:

ACCT DESCRIPTION DEPT
12345 Example 1 1101
22588 Example 1 1101
22588 Example 1 2140
22588 Example 1 3125
33244 Example 1 2140
33244 Example 1 3125
78544 Example 1 2140
78544 Example 1 3125
78544 Example 1 6179
78545 Example 1 3125
78545 Example 1 6179