Consolidating Data - there has to be an easy way!

[MentalBlock45]

Hi all, hope you can help please!!

I have a s/sheet with many columns of categories (types of items sold) that have our stores (listed as store numbers) listed from high to low in each column. I want to create a summary tab for each store, listing their rank in each category. Top is what I have, bottom is what I am after. Thanks!!

[TABLE="width: 348"]
<tbody>[TR]
[TD][/TD]
[TD]Tires[/TD]
[TD]Brakes[/TD]
[TD]Oil Change[/TD]
[TD]Glass Repair[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]512[/TD]
[TD="align: right"]425[/TD]
[TD="align: right"]512[/TD]
[TD="align: right"]425[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]478[/TD]
[TD="align: right"]478[/TD]
[TD="align: right"]989[/TD]
[TD="align: right"]478[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]685[/TD]
[TD="align: right"]512[/TD]
[TD="align: right"]478[/TD]
[TD="align: right"]685[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]989[/TD]
[TD="align: right"]685[/TD]
[TD="align: right"]425[/TD]
[TD="align: right"]989[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]425[/TD]
[TD="align: right"]989[/TD]
[TD="align: right"]685[/TD]
[TD="align: right"]512[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Shop 425[/TD]
[TD] Rank[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Tires[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Brakes[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Oil Change[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Glass Repair[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

If I understand correctly this should work

Book1
	A	B	C	D
1	Tires	Brakes	Oil Change	Glass Repair
2	512	425	512	425
3	478	478	989	478
4	685	512	478	685
5	989	685	425	989
6	425	989	685	512
7
8
9
10
11		Shop 425	Rank
12		Tires	5
13		Brakes	1
14		Oil Change	4
15		Glass Repair	1
Sheet1

Cell Formulas
Range	Formula
C12	=MATCH(MID($B$11,SEARCH(" ",$B$11)+1,3)+0,$A$2:$A$6,0)
C13	=MATCH(MID($B$11,SEARCH(" ",$B$11)+1,3)+0,$B$2:$B$6,0)
C14	=MATCH(MID($B$11,SEARCH(" ",$B$11)+1,3)+0,$C$2:$C$6,0)
C15	=MATCH(MID($B$11,SEARCH(" ",$B$11)+1,3)+0,$D$2:$D$6,0)