VBA Program to Count and populate a table

[Dohko]

Hi,

I need to write a program that does the following
Assuming the sample data here.

studyID Reviewr ID Color
A Normal 1233 Blue
R Special 1233 Blue
A Normal 1234 Red
B Normal 2222 Red
R Special 2222 Red
B Normal 2228 Yellow
C Normal 3859 Yellow
R Special 3859 Black
C Normal 3477 Black
D Normal 4447 Green
D Normal 4448 Green
R Special 4448 Green
E Normal 7777 Yellow
R Special 7777 Green

And output

Normal Special
1 1
2 2
5 5
3 5

I need to return a number depending on which color is selected.
Let's say Blue=1, Red=2, Yellow=3,Black=4, Green=5
I need to check a whole and this will change so has to be done dynamically.
So to get the output I would need to do the following.
If ID=ID, and Reviewr=Normal and Color=Blue then populate the first entry with a 1.
If ID=ID, and Reviewr=Special and Color=Blue then populate the first entry with a 1.
If ID=ID, and Reviewr=Normal and Color=Yellow then populate the first entry with a 3.
If ID=ID, and Reviewr=Special and Color=Green then populate the first entry with a 5.

So the program should check all ID's in the column but only compare the ones that are equal.
What would be the best way of doing this?
I tried a formula but I'm thinking VBA might be better.
Any insights?

Thanks!



Thanks for any insights.

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Dohko]

Here's a sample file.

Thanks

[DonkeyOte]

Dohko, if you opt to cross post the same question on multiple boards please have the courtesy to disclose all relevant links

http://www.mrexcel.com/forum/showthread.php?t=502514

Why ? http://www.excelguru.ca/node/7

With 79 posts to your name you should be familiar with the Forum Rules - if not please read them.