converting input table to output table using formulas

[SRana]

Hi,

i have an input table which i want to convert to the output table using Dynamic Formulas/Functions.. Request help on this..

Input Table: (X1..X9 represent column headers)

[TABLE="width: 553"]
<colgroup><col><col span="3"><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]X1[/TD]
[TD]X2[/TD]
[TD]X3[/TD]
[TD]X4[/TD]
[TD]X5[/TD]
[TD]X6[/TD]
[TD]X7[/TD]
[TD]X8[/TD]
[TD]X9[/TD]
[/TR]
[TR]
[TD]NQ[/TD]
[TD]Selected L1[/TD]
[TD]NQ[/TD]
[TD]L2[/TD]
[TD]NQ[/TD]
[TD]L3[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[/TR]
[TR]
[TD]L5[/TD]
[TD]L2[/TD]
[TD]NQ[/TD]
[TD]Selected L1[/TD]
[TD]L4[/TD]
[TD]L6[/TD]
[TD]L3[/TD]
[TD]NQ[/TD]
[TD]L7[/TD]
[/TR]
[TR]
[TD]L3[/TD]
[TD]NQ[/TD]
[TD]L2[/TD]
[TD]L4[/TD]
[TD]NQ[/TD]
[TD]L5[/TD]
[TD]Selected L1[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[/TR]
[TR]
[TD]L3[/TD]
[TD]NQ[/TD]
[TD]Selected L1[/TD]
[TD]L2[/TD]
[TD]NQ[/TD]
[TD]L4[/TD]
[TD]NQ[/TD]
[TD]L5[/TD]
[TD]NQ[/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD]L7[/TD]
[TD]Selected L1[/TD]
[TD]L3[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]L4[/TD]
[TD]L5[/TD]
[TD]L6[/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD]NQ[/TD]
[TD]Selected L1[/TD]
[TD]L3[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[/TR]
[TR]
[TD]L4[/TD]
[TD]L3[/TD]
[TD]NQ[/TD]
[TD]L2[/TD]
[TD]L5[/TD]
[TD]Selected L1[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[/TR]
[TR]
[TD]Selected L1[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]L3[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]L2[/TD]
[/TR]
[TR]
[TD]L4[/TD]
[TD]NQ[/TD]
[TD]L2[/TD]
[TD]Selected L1[/TD]
[TD]L3[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD]NQ[/TD]
[TD]L5[/TD]
[TD]Selected L1[/TD]
[TD]L4[/TD]
[TD]L6[/TD]
[TD]NQ[/TD]
[TD]L3[/TD]
[TD]NQ[/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]Selected L1[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]L3[/TD]
[TD]NQ[/TD]
[TD]L4[/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]L3[/TD]
[TD]NQ[/TD]
[TD]Selected L1[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[TD]NQ[/TD]
[/TR]
</tbody>[/TABLE]


Output Table: Output table throws out results for count against each vendor for every field

[TABLE="width: 312"]
<colgroup><col><col><col span="6"><col></colgroup><tbody>[TR]
[TD]Vendor[/TD]
[TD]Selected L1[/TD]
[TD]L2[/TD]
[TD]L3[/TD]
[TD]L4[/TD]
[TD]L5[/TD]
[TD]L6[/TD]
[TD]L7[/TD]
[TD]NQ[/TD]
[/TR]
[TR]
[TD]X1[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]X2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]X3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]X4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]X5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]X6[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]X7[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]X8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]X9[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

Welcome to the forum.

As you may have noticed, COUNTIFS chokes on arrays. This will do it though. Copy the B16 formula across and down.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H	I
1	X1	X2	X3	X4	X5	X6	X7	X8	X9
2	NQ	Selected L1	NQ	L2	NQ	L3	NQ	NQ	NQ
3	L5	L2	NQ	Selected L1	L4	L6	L3	NQ	L7
4	L3	NQ	L2	L4	NQ	L5	Selected L1	NQ	NQ
5	L3	NQ	Selected L1	L2	NQ	L4	NQ	L5	NQ
6	L2	L7	Selected L1	L3	NQ	NQ	L4	L5	L6
7	L2	NQ	Selected L1	L3	NQ	NQ	NQ	NQ	NQ
8	L4	L3	NQ	L2	L5	Selected L1	NQ	NQ	NQ
9	Selected L1	NQ	NQ	L3	NQ	NQ	NQ	NQ	L2
10	L4	NQ	L2	Selected L1	L3	NQ	NQ	NQ	NQ
11	L2	NQ	L5	Selected L1	L4	L6	NQ	L3	NQ
12	L2	NQ	NQ	Selected L1	NQ	NQ	L3	NQ	L4
13	L2	NQ	NQ	L3	NQ	Selected L1	NQ	NQ	NQ
14
15	Vendor	Selected L1	L2	L3	L4	L5	L6	L7	NQ
16	X1	1	5	2	2	1	0	0	1
17	X2	1	1	1	0	0	0	1	8
18	X3	3	2	0	0	1	0	0	6
19	X4	4	3	4	1	0	0	0	0
20	X5	0	0	1	2	1	0	0	8
21	X6	2	0	1	1	1	2	0	5
22	X7	1	0	2	1	0	0	0	8
23	X8	0	0	1	0	2	0	0	9
24	X9	0	1	0	1	0	1	1	8
Sheet54

Cell Formulas
Range	Formula
B16	=SUMPRODUCT(($A16=$A$1:$I$1)*($A$2:$I$13=B$15))

[/FONT]

 

[SRana]

Wow!! Thanks very much DRSteele .. saved much time..

 

[DRSteele]

That's super. You're welcome.