reorganiztion help needed

[juster21]

Location # 1...2...3...4
ID#
001a.........A
002b...................A
003c..............R........A
004d.........A........R
----------------------------------------------------------------------

ID#.........A...R
001a........2
002b........3
003c........4...2
004d........1...3

The top is what I have...the bottom is what I want. Is it possible to do this in Excel or is Access a better route?
Sorry I don't have the HTML Add-In.

 

[NBVC]

Book2
	A	B	C	D	E
1	Location	1	2	3	4
2	ID
3	001A		A
4	002B			A
5	003C		R		A
6	004D	A		R
7
8
9	ID	A	R
10	001A	2
11	002B	3
12	003C	4	2
13	004D	1	3
Sheet1

Formula in B10: =IF(ISNA(MATCH(B$9,B3:E3,0)),"",COLUMN(INDEX($A$3:$E$6,MATCH($A10,$A$3:$A$6,0),MATCH(B$9,$B3:$E3,0))))
copied over to C10 and then copied down.