Table structure reformatting (1 Viewer)

[dark_magus]

Hi there,

I have a problem with the handling of table data. I'd like to change the following table

PrimaryKey ForeignKey specy

1 1 Human
2 2 Human
3 2 Mouse
4 2 Rat
5 3 Rat
6 4 Rat
7 5 Mouse
8 5 Rat
9 6 Rat
10 7 Human
11 7 Rabbit


to this format:

PrimaryKey ForeignKey specy1 specy2 specy3
1 1 Human
2 2 Human Mouse Rat
5 3 Rat
6 4 Rat
7 5 Mouse Rat
9 6 Rat
10 7 Human Rabbit

I think I knew how to make it in SQL, but how would you do it in an Excel spreadsheet?

Any help is apreciated.

 

[HaHoBe]

Hi, dark_magus,

right now can only think of VBA to solve the problem (my formula approach didn´t fit ). I used the following code to transform the data putting it to the Range E1:I8:

Option Explicit

Sub magus()
Dim intCol As Integer
Dim lngPlace As Long
Dim lngCounter As Long

Application.ScreenUpdating = False
Range("E1").CurrentRegion.ClearContents
' starting row for output will be row 2
lngPlace = 1
' data starts off in row 2 as well
For lngCounter = 2 To Range("B1").End(xlDown).Row
  If Cells(lngCounter, 2).Value <> Cells(lngCounter - 1, 2).Value Then
    lngPlace = lngPlace + 1
    ' place Primary and Foreign Key
    Range(Cells(lngPlace, 5), Cells(lngPlace, 6)).Value = _
      Range(Cells(lngCounter, 1), Cells(lngCounter, 2)).Value
    intCol = 7
    ' write first specy - there´s at least one
    Cells(lngPlace, intCol).Value = Cells(lngCounter, 3).Value
  Else
    ' Foreign key states another specy for the next column
    intCol = intCol + 1
    Cells(lngPlace, intCol).Value = Cells(lngCounter, 3).Value
  End If
Next lngCounter
' put headers
Range(Cells(1, 5), Cells(1, 6)).Value = Range(Cells(1, 1), Cells(1, 2)).Value
For intCol = 3 To Range("E2").CurrentRegion.Columns.Count
  Cells(1, intCol + 4).Value = "specy" & intCol - 2
Next intCol
Application.ScreenUpdating = True
End Sub

Amendments have to be made in the code concerning the amount of data involved.

Ciao,
Holger

 

[dark_magus]

Great! It worked

Thank you very much, HaHoBe.

Wish I was such a guru in Excel, it would simplify things so much.

But hey, at least I have this forum and you guys...

Thank you again.

Cheers,
dark_magus