Values from multiple columns- Excel 2003

[vannie25]

Hello all

I am trying to work out a table from another sheet preferably without using a macro unless it's a very simple one that I can manage as I only know how to record macros. I am just a beginner of IF and VLookUps and countifs so would be happy if we could use any of these.


Level Previous RAG Current RAG
1 R A
4 A G
1 R R
1 G A


I would like my new table to give me the x values. The values I am trying to obtain are the count of RAG items. e.g I had 18 Rs previously; what is the current distribution of the 18Rs.

Previous RAG Current


R A G
Count of RAG

18 R 10 6 2
16 A 3 3 10
10 G 1 8 1

I hope my message is clear and grateful if anyone could help.

Thanks

 

[vannie25]

I noticed no one is responding to my query, probably because the tables above did not come out right as I intended or it is not clear ops. I have done my best with the HTML code I was advised to use. Grateful if someone could assist me with the right formula to use to generate the result on my result table from the data provided on the live data table. Happy to clarify if you need more input from me.

Thanking you in advance for your support.




Live Data Table

	A	       B	                C	

Cell1	Level	       Previous RAG	Current RAG
	
2	1	       R	                A	
3	4	       A	                G	
4	1	       R	                R	
5	1	       G	                A	


Total count	       44	               44
         R             18	               14
         A             16	               17
         G             10	               13

Result Table should be

Previous RAG		Current RAG Counts
Counts		
		                  R	A	G

R	18	                 10	6	2
A	16	                 3	3	10
G	10	                 1	8	1

 

[vannie25]

Is anyone please help me this query or able to refer me to a specialist that may be able to assist. Happy to pay for a solution.

 

[GlennUK]

Have you tried simply creating a Pivottable:

Excel Workbook
	B	C	D	E	F	G	H	I
1	Prev RAG	Curr RAG		Count of Prev RAG	Curr RAG
2	R	G		Prev RAG	R	A	G	Grand Total
3	A	A		R	10	4	3	17
4	R	R		A	4	10	3	17
5	G	R		G	4	2	4	10
6	R	R		Grand Total	18	16	10	44
7	A	A
8	R	R
9	R	A
10	R	R
11	A	G
12	R	R
13	A	A
14	G	R
15	R	R
16	R	G
17	A	G
18	A	R
19	A	G
20	A	A
21	A	A
22	R	R
23	G	G
24	A	A
25	R	A
26	R	A
27	G	G
28	A	A
29	A	A
30	R	R
31	G	G
32	G	G
33	G	R
34	R	A
35	A	A
36	R	G
37	G	R
38	G	A
39	R	R
40	A	A
41	A	R
42	R	R
43	G	A
44	A	R
45	A	R
Sheet

?

 

[vannie25]

Thank you very much GlennUk. It worked! I wish I thought about this earlier!

 

[GlennUK]

My pleasure. I'm glad to have pointed you in the right direction