Giving a unique value to 2 sets of strings

zakynthos

Board Regular
Joined
Mar 28, 2011
Messages
169
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:

Some videos you may like

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
Joined
Feb 12, 2011
Messages
547
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
Joined
Feb 12, 2011
Messages
547
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
Joined
Mar 28, 2011
Messages
169
Hi, thanks very much for your help - I will take up your suggestion, much appreciated
 

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top