to make a matrix

[sajancr]

hello experts,

i have a question, i never used advanced formulas in excel. Hope you guys will help me.
I want to make a matrix like table (below) from the raw data table

my answer table is this

ABC XYZ JKD ASD ABD james 1 1 johnson 1 david rahul 1 1 1 andrews 1 2 jacob 1 1 davinci 1 <colgroup><col><col span="5"></colgroup><tbody> </tbody>

<tbody>
</tbody>

and my raw data table is this given below. i want to count the column B and duplicates. and to make a table like matrix. Hope you understand my question.

my raw data table

ColA ColB​

james	ABC
johnson	XYZ
david	JKD
rahul	ABC
andrews	ASD
jacob	XYZ
david	ASD
rahul	ASD
andrews	JKD
davinci	JKD
jacob	ABC
james	ABD
johnson	ASD

<colgroup><col style="text-align: left; "><col style="text-align: left; "></colgroup><tbody>
</tbody>

thanks in advance....

sajan raju

 

[Andrew Poulsom]

Welcome to MrExcel.

If your data has headings you can create a pivot table with column Aame in the row area, column B in the column area and count of column B in the data area. Example:

Excel Workbook
	D	E	F	G	H	I
1	Count of ColB	ColB	*	*	*	*
2	ColA	ABC	ABD	ASD	JKD	XYZ
3	andrews	*	*	1	1	*
4	david	*	*	1	1	*
5	davinci	*	*	*	1	*
6	jacob	1	*	*	*	1
7	james	1	1	*	*	*
8	johnson	*	*	1	*	1
9	rahul	1	*	1	*	*
Sheet1

 

[sajancr]

hi...

Thanks for your quick reply.. it works for me...

thanks again....

sajan raju