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

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 567"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: center"] ABC[/TD]
[TD="align: center"]XYZ[/TD]
[TD="align: center"]JKD[/TD]
[TD="align: center"]ASD[/TD]
[TD="align: center"]ABD[/TD]
[/TR]
[TR]
[TD="align: center"]james[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]johnson[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]david[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]rahul[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]andrews[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]jacob[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]davinci[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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​

[TABLE="width: 311"]
<colgroup><col style="text-align: left; "><col style="text-align: left; "></colgroup><tbody>[TR]
[TD="align: left"]james[/TD]
[TD="align: left"]ABC[/TD]
[/TR]
[TR]
[TD="align: left"]johnson[/TD]
[TD="align: left"]XYZ[/TD]
[/TR]
[TR]
[TD="align: left"]david[/TD]
[TD="align: left"]JKD[/TD]
[/TR]
[TR]
[TD="align: left"]rahul[/TD]
[TD="align: left"]ABC[/TD]
[/TR]
[TR]
[TD="align: left"]andrews[/TD]
[TD="align: left"]ASD[/TD]
[/TR]
[TR]
[TD="align: left"]jacob[/TD]
[TD="align: left"]XYZ[/TD]
[/TR]
[TR]
[TD="align: left"]david[/TD]
[TD="align: left"]ASD[/TD]
[/TR]
[TR]
[TD="align: left"]rahul[/TD]
[TD="align: left"]ASD[/TD]
[/TR]
[TR]
[TD="align: left"]andrews[/TD]
[TD="align: left"]JKD[/TD]
[/TR]
[TR]
[TD="align: left"]davinci[/TD]
[TD="align: left"]JKD[/TD]
[/TR]
[TR]
[TD="align: left"]jacob[/TD]
[TD="align: left"]ABC[/TD]
[/TR]
[TR]
[TD="align: left"]james[/TD]
[TD="align: left"]ABD[/TD]
[/TR]
[TR]
[TD="align: left"]johnson[/TD]
[TD]ASD




[/TD]
[/TR]
</tbody>[/TABLE]
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