# Transposing Data By Matching Criteria

#### BjayZay

##### New Member
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.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Mentor82

##### Active Member
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
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
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))

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``````

Replies
14
Views
440
Replies
4
Views
126
Replies
1
Views
364
Replies
2
Views
221
Replies
22
Views
377

1,126,906
Messages
5,621,581
Members
415,846
Latest member
nigeywigey

### 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.

### Which adblocker are you using?

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

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