Cross Tab, Concatenate, and Creating a Total Column and Row Help

[JacobGFMR]

Hello,

I am trying to create a pivot-like table, and am having quite a few issues. 

I have a couple questions, and am hoping someone can help me design the table as I need. 

I've gotten a pretty good ways, but have hit a wall. 

I'm looking to create a table like below, but I need the following:

1. I would like the associate name to be concatenated with the their ID (A396271, for example), and the name is provided in my input document. 

2. I would like to remove all Nulls, and the Data Cleansing tool isn't removing them as I assume because they are not strings. 

3. I would like to a new row and a new column to total the columns and rows. 

4. And finally, I would like to have all the information in a table format.

I know this is a lot, but I'm hoping someone can point me in the right direction. 

Thank you. 

[Qiu]

@JacobGFMR 
It should not be diffucult, but to help us, can you provide the sample input and output in data format, not a picture.

[JacobGFMR]

Here is a sample input. 

Queue_ID	Queue_Name	Manager	Work Item	Item_Type	Sub_Type
A111111	John Doe	Smith	W123456-02APR21	WITHD	FULLPO
A111115	Jane Roe	Smith	W123457-02APR21	WITHD	PROCES
A111222	James Dee	Smith	W123458-02APR21	WITHD	FULLPO
A111333	John John	Rogers	W123459-02APR21	WITHD	SSSSSS

I need to only show Manager Smith's team's info, so I don't need Manager Rogers' team's info, and I would like to have a table showing something similar to the below. 

Item_Type	Sub_Type	John Doe - A111111	Jane Roe - A111115	James Dee - A111222	Total
WITHD	FULLPO	1		1	2
WITHD	PROCES		1		1
Total		1	1	1	3

[marcusblackhill]

Hey @JacobGFMR !

Thanks for share a example data, like @Qiu said, that helps to understand how we can help you. I saw you didn't considered the sub type "SSSSS", don't know if was a mistake.

You can see my example attached to solve your question, hope that helps!

[AngelosPachis]

Maybe you can try this @JacobGFMR ?

[Qiu]

@AngelosPachis @marcusblackhill 
Good ones. and I can sit back now. 😁

[JacobGFMR]

@AngelosPachis

Thank you for the provided example work flow. 

I have used it and am very close to what I am needed, but I still have an issue. 

For example, when I have the following information, I am not getting the type of output I want. 

Provided is an example of my input, what I'm getting as my output, and what I am needing as my output. 

INPUT

Queue_ID	Queue_Name	Manager	Work Item	Item_Type	Sub_Type
A111111	John Doe	Smith	W123456-04APR21	WITHD	FULLPO
A111111	John Doe	Smith	W123457-04APR21	WITHD	FULLPO
A111111	John Doe	Smith	W123458-04APR21	WITHD	FULLPO
A111111	John Doe	Smith	W123459-04APR21	WITHD	INSERV

OUTPUT

Item_Type	Sub_Type	John Doe - A111111	John Doe - A111111	John Doe - A111111	John Doe - A111111	Total
WITHD	FULLPO	1	1	1	[Null]	3
WITHD	INSERV	[Null]	[Null	[Null]	1	1
Total	Total	1	1	1	1	4

NEEDED OUTPUT

Item_Type	Sub_Type	John Doe - A111111	Total
WITHD	FULLPO	3	3
WITHD	INSERV	1	1
Total	Total	4	4

How can I get the output to do a sum of all Sub_Type for a given Queue_ID where it doesn't show a different column for each/same Sub_Type? 

I need to know the total amount of the different Sub_Types for the given Queue_ID. 

Please let me know if this makes sense. 

Thank you. 

[afv2688]

Hello @JacobGFMR ,

would this work for you?

Regards

[echuong1 · Accepted solution]

To add the totals, try using the Add Totals Crew macro. This will add row and column totals easily.