# Thread: Amalgamate Data Formula

1. ## Amalgamate Data Formula

I am looking for ideas on how to split my data in the attached spreadsheet.

Capture.JPG

Thank you in advance.

Kerry

2. You've removed data in the amalgamation, is the intention to transfer to the new format or to keep the existing but present the data differently?
How have you come up with the 3rd line in the new format?

cheers, Paul

3. ## Amalgamate Data Formula

My apology for the lack of information.
What I am trying to do is to create rows from the region columns. So where SA2_DESC has a multiple Regions each one will become a row. The intention is to transfer to the new format. I have removed the total column as it unnecessary for the purposes of this exercise. I have attached another example that hopefully is more meaningful.

4. Here's some untested code to convert the sheet - I don't have Excel to test, but it looks OK.
Don't forget to test it on a copy of your data.

cheers, Paul
Code:
```Public Sub ReformatSheet()
Dim LastRow As Long, I As Long, J As Long

SourceRange = "A2" 'Change this to the destination column
DestRange = "AA1" 'Change this to the destination column
NumCols = 7 'Change this if more than 7 columns of data

SourceRow = Row(SourceRange)
SourceCol = Column(SourceRange)
DestRow = Row(DestRange)
DestCol = Column(DestRange)

LastRow = ActiveSheet.Cells(Rows.Count, SourceCol).End(xlUp).Row
For I = SourceRow To LastRow
For J = 0 to NumCols - 3 'Ignoring first 2 columns as they are constant
If Cells(I, J + 3) > 0 Then
DestRow = DestRow + J
Cells(J, DestCol) = Cells(I, SourceCol)
Cells(J, DestCol + 1) = Cells(I, SourceCol + 1)
Cells(J, J + 3) = Cells(SourceRow, SourceCol + 2)
Cells(J, J + 3) = Cells(I, SourceCol + 2)
End If
Next J
DestRow = DestRow + 1
Next I
End Sub```

5. Thankyou Paul. Sadly it isnt working. Is this not possible with a formula?

22.jpg

6. Sorry, that should be:

SourceRow = Range(SourceRange).Row
SourceCol = Range(SourceRange).Column
DestRow = Range(DestRange).Row
DestCol = Range(DestRange).Column

cheers, Paul

7. ## The Following User Says Thank You to Paul T For This Useful Post:

kerryg (2016-01-19)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•