Convert table into matrix

[#1]

Hi all,

Appreciate your help here.

My file, source1.csv contains the following fields

A	B	Pair	Price
1	1	1-1	$3.00
1	2	1-2	$3.00
1	3	1-3	$4.00
1	4	1-4	$4.00
2	1	2-1	$2.50
2	2	2-2	$2.50
2	3	2-3	$5.00
2	4	2-4	$5.00
3	1	3-1	$3.00
3	2	3-2	$3.00
3	3	3-3	$2.00
3	4	3-4	$2.00
4	1	4-1	$6.00
4	2	4-2	$6.00
4	3	4-3	$2.00
4	4	4-4	$2.00

Pair = A- B

I would like to display the output in the following format, which is a matrix or grid form. 

The content here is sum of the values.

B A	1	2	3	4
1	$3.00	$3.00	$4.00	$4.00
2	$2.50	$2.50	$5.00	$5.00
3	$3.00	$3.00	$2.00	$2.00
4	$6.00	$6.00	$2.00	$2.00

Could i also get a count of the values?

B A	1	2	3	4
1	1	1	1	1
2	1	1	1	1
3	1	1	1	1
4	1	1	1	1

THanks in advance.

[AbhijitBansode]

Can be achieved using simple pivot table.

See attached.

Regards,

Abhijit

[#3]

WHen i use pivot table, i got the result in compact form, i,e,

A	B	Price
1	1	$3.00
1	2	$3.00
1	3	$4.00
1	4	$4.00
2	1	$2.50
2	2	$2.50
2	3	$5.00
2	4	$5.00
3	1	$3.00
3	2	$3.00
3	3	$2.00
3	4	$2.00
4	1	$6.00
4	2	$6.00
4	3	$2.00
4	4	$2.00

Is there any settings i can do to make it into the matrix form?

Your reply is much appreciated.

Thanks!

[AbhijitBansode · Accepted solution]

for 1st O/P:

Dim: A, B

Measure: Price

for 2nd O/P:

Dim: A, B

Measure: count(Price)

In both pivot tables, drag 2nd dimension B to the top.

Regards,

Abhijit

[#5]

Exactly what I need! thanks!