Transposing Data By Matching Criteria

[BjayZay]

Hi All, I am attempting to take a table like this:

and transposing the "Loss Used" amounts so they are transposed to give each "Loss Year" row a horizontal breakout of when these amounts were actually used. The output should look something like below:

Right Now those are static linked (ex. =J10). This is just test data but the real data has multiple "entities" within multiple states (i.e. Georgia will have Test001,Test002,Test003 and MI may have Test002,Test005). I need to figure out a way to allocate the "loss used" column to this horizontal breakout. I will mention I have very little knowledge in VBA. I would prefer to use a formula if possible.

 

[Mentor82]

Hi All, I am attempting to take a table like this:

View attachment 13144

and transposing the "Loss Used" amounts so they are transposed to give each "Loss Year" row a horizontal breakout of when these amounts were actually used. The output should look something like below:
View attachment 13145

Right Now those are static linked (ex. =J10). This is just test data but the real data has multiple "entities" within multiple states (i.e. Georgia will have Test001,Test002,Test003 and MI may have Test002,Test005). I need to figure out a way to allocate the "loss used" column to this horizontal breakout. I will mention I have very little knowledge in VBA. I would prefer to use a formula if possible.

Hi,
Would you mind advising exactly to which cells you want to relocate the values from column J, because I'm afraid I cannot find any logic in that, ex. J10 = 10,000 and it's assigned to date 12/31/2011 (C10) whereas the value is stated in P7. Why P7? I assume the column P is right, because it has date 12/31/2011 the same as in C10 of the considered record, but why the value is in row 7 in column P and not in row 10?
I'd appreciate if you'd advise me the logic based on which you want to relocate the values from column J, so I can help you with VBA

 

[BjayZay]

Hi,
Would you mind advising exactly to which cells you want to relocate the values from column J, because I'm afraid I cannot find any logic in that, ex. J10 = 10,000 and it's assigned to date 12/31/2011 (C10) whereas the value is stated in P7. Why P7? I assume the column P is right, because it has date 12/31/2011 the same as in C10 of the considered record, but why the value is in row 7 in column P and not in row 10?
I'd appreciate if you'd advise me the logic based on which you want to relocate the values from column J, so I can help you with VBA

Hi so the logic is as follows:

For any row that a "loss" is showing I would like the amounts of that loss used by year displayed in a horizontal fashion on that same row.

So for example:
Row 7 (12/31/2008) shows a 10,000 loss. If you see on row 10 (12/31/2011) used 10,000 of the loss from 12/31/2008. I would like that 10,000 to appear on row 7 under the "12/31/2011" column as 10,000 was used in 12/31/2011.

Also if you see row 9 (12/31/2010) has a loss of 15,000 which was used in both 2011 and 2013. The usage of these amounts are then "transposed" and put on row 9 and allocated accordingly to the years and amounts used.

Pretty much I want each row that has a loss to be able to break out what years that loss was used. You can tell these amounts are used by looking at the "Year Used" column. Years that have income will repeat to show what loss years they are utilizing and the amount of each year's loss that is being used can be found in the "Loss Used" column. I just need to reformat the data so years that present loss have a cleaner look as to when these losses are being used to offset income and how much of the loss is being used in each respective year.

I hope this helps! Thank you for the reply!

 

[Mentor82]

Hi,
Thanks for the explanation, however I'm not sure if I got you right.
I've prepared some code for you. Give it a try and have a look if the result matches your needs.
I do recommend you make a copy of the workbook and test the code on the copy to stave off any damage to your data in case of any unexpected behavior of the code.

Sub LossUsedTranspose()
    Dim i&, j&, k&
    Dim YearEnd As Date
    Dim YearUsed As Date
    Dim lossUsed#
    Dim lastRow&
    
    Const startRow& = 7
    Const mainColumn$ = "A"
    Const yearEndColumn$ = "C"
    Const yearUsedColumn$ = "D"
    Const lossUsedColumn$ = "J"
    Const yearsSearchFromColumn& = 13 'M Column
    
    lastRow = Range("A" & Cells.Rows.Count).End(xlUp).Row
    
    For i = startRow To lastRow
        If Cells(i, lossUsedColumn) <> "" Then
            lossUsed = CDbl(Cells(i, lossUsedColumn))
            YearEnd = CDate(Cells(i, yearEndColumn))
            YearUsed = CDate(Cells(i, yearUsedColumn))
            
            j = yearsSearchFromColumn
            
            Do While Cells(startRow - 1, j) <> ""
                If Cells(startRow - 1, j) = YearEnd Then
                    
                    For k = startRow To lastRow
                        If Cells(k, yearEndColumn) = YearUsed Then
                            Cells(k, j) = lossUsed
                            Exit For
                        End If
                    Next k
                    
                    Exit Do
                End If
                j = j + 1
            Loop
        End If
    Next i
    
    MsgBox "Done"
End Sub