Criteria based rank without skipping numbers

[mail4ann]

Hi -
I am trying to rank a set of data based on a separate criteria (brand) from the ranking list (assortment size). I need the resulting data to be able to have duplicates, but not skip any numbers as I am going to use the rank results in a vlookup column reference formula.

Each of the below formulas get me about half way there and I haven't been able to figure out how to combine them.

This formula ranks the data based on the brand criteria, but it skips numbers in the resulting rank.
=(COUNTIFS($A$1:$A$7,A1,$B$1:$B$7,"<"&B1)+1)

This formula ranks correctly and does not skip any numbers, but it doesn't allow me to use the brand list criteria.
=SUMPRODUCT((B1 > B$1:B$7)/COUNTIF(B$1:B$7,B$1:B$7))+1

Can anyone help me combine these 2 formulas?

Data Example:

A B C
Brand Assortment Size Rank
Casio 20
Casio 20
Casio 40
Casio 40
Fossil 20
Fossil 20
Fossil 40

Results in C with the top formula
C
1
1
3
3
1
1
3

Desired results in C
C
1
1
2
2
1
1
2

Can anyone help?
Thanks!

 

[oldbrewer]

can you explain further how you are ranking them eg why is casio 20 1 and casio 40 2 and fossil 20 1

 

[mail4ann]

The numbers represent assortment sizes of product. Each line represents a store location. i.e. Casio has 2 assortment sizes. the smaller assortment has 20 pieces and the larger has 40. Fossil also has 2 assortment sizes - 20 and 40. Each line represents a store location, but for this ranking which store the plan is in doesn't matter. I am trying to get a rank list of each assortment size by brand, so Casio and Fossil need to be ranked separately.

 

[oldbrewer]

but what do you meanby ranking - it normally means putting in order - or are you justsorting

 

[mail4ann]

Sorry I have not been very clear. I'm pretty good with the basics of excel, but i am struggling with this task. I appreciate your taking the time to review.

I am not sure what to call it. I wont be able to sort the file each time. The formula will need to count/rank correctly even if the file is not in numerical order. The full file has over 1600 lines and 30 plus brands.

I basically need a counter (or rank?) that starts at 1 and increases by 1 every time there is a larger plan size within that brand. Multiple occurrences of the same plan size get the same rank. It seems like a perfect Rankif scenario, if only rankif existed.

The condition is that it has to only rank/count within that brand and not skip any numbers.

 

[oldbrewer]

so, if within your table you had casio 10, casio15, casio 23 and casio 29 each occurring a number of times then casio = 4
similarly if sony = 7 then sony=1 and casio = 2

is that it?

 

[mail4ann]

Unfortunately no.

"if within your table you had casio 10, casio15, casio 23 and casio 29 each occurring a number of times then ..."

The results should look like the below. Each occurrence of a different value of casio would have a different number. (Starting at 1 and getting 1 bigger each time there is a bigger plan size). The same value would have the same number, just like a regular rank, but without skipping any values.

example:
casio 10 - 1
casio 10 - 1
casio 15 - 2
casio 23 - 3
casio 23 - 3
casio 29 - 4
Sony 7 - 1
Sony 7 - 1
Sony 14 - 2
Sony 20 - 3
Sony 20 - 3
Sony 23 - 4

Sony would rank in the same fashion, independently from the Casio values.

 

[oldbrewer]

casio 10 - 1

casio 10 - 1

the simple pivot table

casio 15 - 2

finds all the unique

casio 23 - 3

calculated

concatenated

items in the list

casio 23 - 3

items

code

casio 29 - 4

casio 10

1

casio 10 - 1

Sony 7 - 1

casio 10

1

casio 10 - 1

Count of items

Sony 7 - 1

casio 15

2

casio 15 - 2

items

Total

Sony 14 - 2

casio 23

3

casio 23 - 3

casio 10

2

Sony 20 - 3

casio 23

3

casio 23 - 3

casio 15

1

Sony 20 - 3

casio 29

4

casio 29 - 4

casio 23

2

Sony 23 - 4

sony 7

4

sony 7 - 4

casio 29

1

sony 7

4

sony 7 - 4

sony 14

1

sony 14

1

sony 14 - 1

sony 20

2

sony 20

2

sony 20 - 2

sony 23

1

sony 20

2

sony 20 - 2

sony 7

2

sony 23

3

sony 23 - 3

Grand Total

12

if it is vital thatsony 7 is 1 I am stumped

alpha

beta

gamma

epsilon

omega

casio 10

6

casio

10

1

casio 15

6

casio

15

2

casio 23

6

casio

23

3

alpha column is generated

casio 29

6

casio

29

4

automatically from

sony 14

5

sony

14

1

the pivot table

sony 20

5

sony

20

2

sony 23

5

sony

23

3

sony 7

5

sony

7

4

beta is the position of

the blank character

gamma is the brand

epsilon is the model

number

omega is the number required

<colgroup><col><col span="5"><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[mail4ann]

That doesn't quite get where i need, but it did give me some more ideas to test with. - maybe a concatenate rank?? i'll see what i can come up with.

Thank you so much for taking the time to try to work on this.

I will work on it some more and hopefully get there.

Thanks again.