Thursday, October 22, 2009

Turn 3 dimensional (pivot) table to junction table

Scenario: You have a "three dimensional table" in Excel where the rows are articles and the columns are suppliers, and each cell in the table are marked with an x (or number indicating something) indicated which articles and suppliers that are related. You have decided to create a database instead with one table with articles and another with suppliers. Let's say one article can have multiple suppliers and one supplier can deliver multiple articles, i.e. you need an junction table. How do you create a junction table from this three dimensional table?

My Solution: Search thorugh each row in the table fro a value larger than zero (anything but zero or empty) , for each hit write a line in a textfile with the supplier, article and cell value. Then use the text file to create your junction table.

The code
Sub ConvertTable()
Dim str, strForb As String
Dim col As Integer


Dim objFSO, objFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("Rows.txt")

For i = 2 To 5 'enter which row that contains data
strArticle = Range("A" & i).Value
For Each Cell In Range("B" & i & ":E" & i)
If Cell.Value > 0 Then
col = Cell.Column
str = strForb & "," & Range(ColNo2ColRef(col) & 1) & "," & Cell.Value
objFile.WriteLine (str & cbrlf)
End If
Next Cell
Next i

End Sub

Function ColNo2ColRef(ColNo As Integer) As String
If ColNo <> 256 Then
ColNo2ColRef = "#VALUE!"
Exit Function
End If
ColNo2ColRef = Cells(1, ColNo).Address(True, False, xlA1)
ColNo2ColRef = Left(ColNo2ColRef, InStr(1, ColNo2ColRef, "$") - 1)
End Function