Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    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. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    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.
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    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. #5
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Thankyou Paul. Sadly it isnt working. Is this not possible with a formula?

    22.jpg

  6. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    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
  •