Search code examples
excelrowstranspose

Excel - Transposing Data into Rows when Condition Met


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,


Solution

  • 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