Transposing Data By Matching Criteria

BjayZay

New Member
Joined
May 5, 2020
Messages
2
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi All, I am attempting to take a table like this:

1588701154166.png


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:
1588701247308.png


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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
311
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
May 5, 2020
Messages
2
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Active Member
Joined
Dec 30, 2018
Messages
311
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
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.

VBA 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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,783
Messages
5,626,844
Members
416,205
Latest member
rondon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top