Amalgamate Data Formula

Topic

New Reply

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

43295-Capture

Thank you in advance.

Kerry

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

43299-22

Reply | Quote

Sorry, that should be:

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

cheers, Paul

Reply | Quote