Matrix Table - Add Column to the end of the Matrix (not on every column)

bolwahnn

New Member
Joined
Apr 29, 2011
Messages
40
Hey Guys,
Looking for some help here - I havent been able to find a solution anywhere.

I have a matrix table that provides a view of aging inventory. For each client they have a number of days to work the ticket before it is outside of SLA. I want to add a column that shows when those tickets are outside of SLA. Currently when I try to add that data point, it shows up on every column, but I just want it to show at the end of the matrix (the last column).

Example: This is what I want to see.....
Client:
0 Days1 Day2 Days3 days4 days5 daysSLA # of Days
A30 tickets20 tickets16 Tickets10 Tickets6 Tickets3 Tickets4 days
B25 Tickets18 tickets12 tickets8 Tickets002 days

<tbody>
</tbody>

Example: This is what I get currently get when trying to build the matrix (not what I want)
Client0 DaysSLA # of Days1 DaySLA # of Days2 DaySLA # of Days3 DaysSLA # of Days4 Days SLA # of Days5 Days SLA # of Days
A30 Tickets4 days20 tickets4 days16 Tickets4 days10 Tickets4 days6 Tickets4 days3 Tickets4 days
B25 Tickets2 days18 Tickets2 days12 tickets2 days8 Tickets2 days02 days02 days

<tbody>
</tbody>

Does anyone know of a way to build the matrix as I'm intending it to look?

Thanks!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Can you provide a sample of your source data?
 
Upvote 0
Can you provide a sample of your source data?

I have 2 tables:
1) A custom date table that counts the aging starting with todays date as day 0, yesterday as day 1, day before yesterday Day 2, etc. etc.
2) Ticket data containing the: client Name, Ticket date opened, # of tickets, SLA threshold
So in the example provided the data would look like similar to this.....
ClientTicket Date# of ticketsSLA Threshold
A9/24/2019304
A9/23/2019204
A9/22/2019164
A9/21/2019104
A9/20/201964
A9/19/201934
B9/24/2019252
B9/23/2019182
B9/22/2019122
B9/21/201982

<tbody>
</tbody>

This is a stripped down version of the data, but if I could get assistance with how not to duplicate the "SLA Threshold" on every column, I can then apply it to the real data/matrix visual I'm trying to create.

Thanks!
 
Upvote 0
Hi,

Maybe this might suit, the dates in the first row will always show today's date minus 5 days, I added the array to populate a unique client list for the matrix sheet;

The index & match for SLA # of days will always pickup the first match value for that client.

Change sheet names and copy or drag these formulas as needed.


Book1
ABCD
1ClientTicket Date# of ticketsSLA Threshold
2A25/09/2019304
3A24/09/2019204
4A23/09/2019164
5A22/09/2019104
6A21/09/201964
7A20/09/201934
8B25/09/2019252
9B24/09/2019182
10B23/09/2019122
11B22/09/201982
Ticket Data



Book1
ABCDEFGH
125-09-1924-09-1923-09-1922-09-1921-09-1920-09-19
2Client:0 Days1 Day2 Days3 days4 days5 daysSLA # of Days
3A30 Tickets20 Tickets16 Tickets10 Tickets6 Tickets3 Tickets4
4B25 Tickets18 Tickets12 Tickets8 Tickets2
Matrix
Cell Formulas
RangeFormula
B1=TODAY()
B3=IFNA(INDEX('Ticket Data'!$C$2:$C$11,MATCH(Matrix!$A3&Matrix!B$1,INDEX('Ticket Data'!$A$2:$A$11&'Ticket Data'!$B$2:$B$11,),0))&" Tickets","")
C1=B1-1
H3=IF(A3="","",INDEX('Ticket Data'!$D$2:$D$11,MATCH(Matrix!$A3,'Ticket Data'!$A$2:$A$11,0)))
A3{=IFNA(INDEX('Ticket Data'!$A$2:$A$11,MATCH(0,COUNTIF(Matrix!$A$2:A2,'Ticket Data'!$A$2:$A$11),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Looks to me like simple pivot operation. See if this works
Highlight your source range
Data ... From Table/Range ... [x] my table has headers
Change the text of the PowerQuery generated 2nd step to read {"Ticket Date", type date} instead of {"Ticket Date", type datetime} or {"Ticket Date", Int64.Type}
Click to select Ticket Date column then Transform...Pivot Column... and for value column, choose # of tickets

Code:
let  Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Ticket Date", type date}, {"# of tickets", Int64.Type}, {"SLA Threshold", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Ticket Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"Ticket Date", type text}}, "en-US")[#"Ticket Date"]), "Ticket Date", "# of tickets", List.Sum)
in #"Pivoted Column"


Fancy version that rearranges columns and sets neutral column headers

Code:
let  Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Ticket Date", type date}, {"# of tickets", Int64.Type}, {"SLA Threshold", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Ticket Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Ticket Date", type text}}, "en-US")[#"Ticket Date"]), "Ticket Date", "# of tickets", List.Sum),
#"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
#"Renamed Columns" = Table.RenameColumns(#"Demoted Headers",{{"Column1", "Client"}, {"Column2", "SLA Threshold"}, {"Column3", "0 Days"}, {"Column4", "1 Day"}, {"Column5", "2 Day"}, {"Column6", "3 Day"}, {"Column7", "4 Day"}, {"Column8", "5 Day"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Client", "0 Days", "1 Day", "2 Day", "3 Day", "4 Day", "5 Day", "SLA Threshold"}),
#"Removed Top Rows" = Table.Skip(#"Reordered Columns",1)
in  #"Removed Top Rows"
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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
Back
Top