# Giving a unique value to 2 sets of strings

#### zakynthos

##### Board Regular
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

Last edited:

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### shawnhet

##### Well-known Member
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

##### Well-known Member
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

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

#### shawnhet

##### Well-known Member
You're welcome. Good luck!

Replies
2
Views
97
Replies
1
Views
164
Replies
18
Views
250
Replies
1
Views
50
Replies
13
Views
292

1,127,596
Messages
5,625,714
Members
416,130
Latest member
galgozzi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back