I am really hoping there is a neat way of doing this, as I am tearing my hair out at the moment.
My excel table looks like this :
Company | Department | Area | Project1_B | Project1_A | Project2_B | Project2_A | Project3_B | Project3_A
Company1 Department1 Area1 High Medium High Low
Company1 Department1 Area2 Low Medium
Company2 Department1 Area1 Medium Medium High Low
Company2 Department2 Area1 Low High
Essentially, for every row (representing an area in the organisation), there are before (b) and after (a) statuses for a number of projects (projects1-100)
Not all rows will have values for every project, as seen above, but each project will always have a 'pair' (i.e a before and after status).
The output I am trying to achieve looks like this :
Company | Department | Area | Project_Name | Before | After
Company1 Department1 Area1 Project1 High Medium
Company1 Department1 Area1 Project3 High Low
Company1 Department1 Area2 Project2 Low Medium
Company2 Department1 Area1 Project1 Medium Medium
Company2 Department1 Area1 Project2 High Low
Company2 Department1 Area1 Project2 High Low
Company2 Department2 Area1 Project3 Low High
Where only 'non blank' records are transposed. There should be a row for each project that an area has worked on.
I have tried various transpose methods and VLOOKUPS, to no avail. Its frustrating because I suspect its a pretty simple solution but any searches I've made arent quite what I am looking for!
Is what I am looking to do possible, either with formula or VB, or am I better dumping the data into a database and trying that?
Thanks for any help,
One way to to this is using VBA.
Option Explicit
Sub TransposeData()
Dim FromSheet As Worksheet
Dim ToSheet As Worksheet
Dim FromColumn As Integer
Dim FromRow As Integer
Dim ToRow As Integer
Set FromSheet = Sheets("Sheet1")
Set ToSheet = Sheets("Sheet2")
ToRow = 2
For FromRow = 2 To FromSheet.UsedRange.Rows.Count
For FromColumn = 4 To FromSheet.UsedRange.Columns.Count Step 2
If FromSheet.Cells(FromRow, FromColumn) <> "" Then
ToSheet.Cells(ToRow, 1) = FromSheet.Cells(FromRow, 1) ' Column A: Company
ToSheet.Cells(ToRow, 2) = FromSheet.Cells(FromRow, 2) ' Column B: Dept
ToSheet.Cells(ToRow, 3) = FromSheet.Cells(FromRow, 3) ' Column C: Area
ToSheet.Cells(ToRow, 4) = FromSheet.Cells(1, FromColumn) ' Column D: Project before (from heading)
ToSheet.Cells(ToRow, 5) = FromSheet.Cells(1, FromColumn + 1) ' Column E: Project after (from heading)
ToSheet.Cells(ToRow, 6) = FromSheet.Cells(FromRow, FromColumn) ' Column F: Data - Before
ToSheet.Cells(ToRow, 7) = FromSheet.Cells(FromRow, FromColumn + 1) ' Column G: Data - After
ToRow = ToRow + 1
End If
Next FromColumn
Next FromRow
End Sub