How do I Cross tab/Pivot with two columns?

[ajr2183]

I'm trying to create a pivot table from a sales table I have. The sales table has invoices, and there are several invoice lines per invoice. There are also several "types" of different "Amounts" per invoice line. I am trying to create a pivot table that maintains 3 columns worth of data (Type, Amount and Description). 

I mocked up some sample data below that looks kind of like my dataset:

Invoice_Nbr	Invoice_Line	Customer	Type	Amount	Description
1	1	Bob	Cost	50	This is a note for this
1	1	Bob	Tax	5	Here we are
1	1	Bob	Surcharge	3	Ok, this is it
1	2	Bob	Cost	75	And this is a note for this
1	2	Bob	Surcharge	40	But here is another note
1	3	Bob	Cost	50	Yup, another
1	3	Bob	Tax	7.5	Ok, this is last
1	3	Bob	Surcharge	3	Nope, this is
2	1	Phil	Tax	33	And yet another
3	1	Joe	Surcharge	20	Can't stop the notes

Is it possible to create something like this:

Invoice_Nbr	Invoice_Line		Sum	Description
1	1	Cost	50	This is a note for this
		Surcharge	3	Here we are
		Tax	5	Ok, this is it
	2	Cost	75	And this is a note for this
		Surcharge	40	But here is another note
		Tax	0	Ok, this is last
	3	Cost	50	Yup, another
		Surcharge	7.5
		Tax	3	Nope, this is

[ajr2183]

I made a workflow (attached), but clearly doing something wrong.

[kat · Accepted solution]

Hi @ajr2183

Here's my go at it. I find it a lot easier to do with the basic table. You then need to apply some column rules to get the repeating numbers to disappear. Not the most intuitive, but it works.

cheers

Kat

[ajr2183]

Wow, that's awesome! Thank you! I'm going to need to examine how those multi-row formula tools work a bit more, since this is exactly what I was trying to achieve. I really appreciate it.

[ajr2183]

Hi Kat,

Thank you again so much for your help.

Would you know how to add a row at the top of each new Invoice_Nbr sequence, which I could Highlight in a dark gray, and which is basically like the Top-level summary row... For example, it would have the Invoice_Nbr, nothing for Invoice_Line, nothing for Type, then it would have the Sum of the Amounts, and nothing for Description. I would then use that as kind of a visual break between Invoice_Nbrs, but also use it as a summary row for that invoice that I could Make a darker gray and bold the text. I tried doing this with a summarize tool, and a formula tool to make Invoice_Line = 0, then sorted ascending on Invoice_Line, but when I get to the Multi-Row Formula tool, I just cannot figure out how to make it work.

Maybe you have an even more efficient way to achieve this, but any guidance in general would be amazingly appreciated.