Giving a unique value to 2 sets of strings

[zakynthos]

Hi,
I've concatenated text strings in column B with those in Column C and would like to know what formula I can use to assign a unique value to the combined string.
Example
Col Col Col Col Function for result below?
B C D E F
Cat1text Question1text Cat1textQuestion1text Cat1textQuestion1text returns a '1'
Cat1text Question2text Cat1textQuestion2text Cat1textQuestion2text returns a '2'
Cat1text Question3text Cat1textQuestion3text Cat1textQuestion3text returns a '3'
Cat2text Question1text Cat2textQuestion1text Cat2textQuestion1text returns a '4' etc

There are 12 categories and a total of 61 questions spread in varying amounts among these categories (eg cat1 has 3 unique questions attached to it, cat 2 has 4 unique questions etc etc)

The categories and questions may be repeated in the list and after sorting alphabetically by Category in Column B, I would like to do a count of all the '1''s in col F between say F1:f20000 in say f20001, all the 2's in column F in h200001, all the '3's in column F in i200001 etc etc

Many thanks

 

[shawnhet]

Try the following : Enter using Ctrl+Shift+Enter to set up as array formula - if done right it will automatically bracket it in {} - do not add them yourself - then copy the formula for the whole range (this expression assumes that the concantenated data is in the range D2:D5000).

=COUNTIF($D$2:$D$5000,"<"&D2)+SUM(IF(D2=$D$2:D2,1,0))

It may not get you exactly what you are looking for - it will give a value of 10 to the tenth smallest value in your range even if items 1-9 are all given a value of 1 - but it should give you a good foundation.

Cheers,

 

[shawnhet]

I forgot the second half of the problem - to count the number of times the ID number was used.

You can try something like this in column E.

=COUNTIF($E$2:$E$6420,E2)

Cheers,

 

[zakynthos]

Hi, thanks very much for your help - I will take up your suggestion, much appreciated

 

[shawnhet]

You're welcome. Good luck!