assigning a numerical value to text strings

Question

Hi,

I would like to count the no. of ocurrences of 2 sets of striings in columns B and C using the example below by giving repeatng sets a unique number:

Example data:

B  = category

C = question string

D = a value to match text string (B) with text string (C)

B                 C                              D (return a value of)

a                  q1a                          1

a                  q2a                          2

a                  q3a                          3

b                  q1b                          4

b                  q2b                          5

c                  q1c                           6

c                  q2c                           7

c                  q3c                           8

a                  q2a                          2

b                  q1b                          4

c                  q3c                           8

Calculations:

count the number of 1's in D, 2's in D etc  say returning a 1 for 1's in e12, a 2 for 2's in f12, .... a 2 for 8,s in L12 etc

many thanks.

Answer 1

Now, the category is in column A and the question string in column B. The method for assigning numerical values to text strings doesn't change.

I approached the "analysis" specification using a table starting in E2001.

I put column labels in row 2001 and row labels in column E. Specifically, E2002:E:2013 contain a, b, c, ..., j, k, l. And assuming at most 9 questions per category, F2001:N2001 contain q1, ..., q9.

Column O will have the category total. Column P will have the percentage of each category questions (including all repeats) to that total.

After entering the row/column labels, put this in O2002 and copy down to O2013:

   =COUNTIF($A:$A,$E2002)

Then put this in F2002 and copy it into the entire array F2002:N2013

   =IF($O2002=0,"",

      TEXT(COUNTIF($B:$B,F$2001&$E2002)/$O2002,"0/"&$O2002&";;"))

Then put this in P2002 and copy down to P2013:

   =TEXT($O2002/SUM(O$2002:O$2013),"0/"&SUM(O$2002:O$2013)&";;")

Though I formatted results as fractions expressed as text, it is straightforward to change the formatting to numeric decimals or percentages (by not using the TEXT function) or to more embellished text (like "2/14% for q3").

Modify to suit.

Answer 2

Hi,

Wow!  Thanks so much for this - would never have thought of doing it this way - I have today worked out my own method but the formula were based on running a text to columns on the category/question column, sorting by category and then using IF(and( etc...)

Lot's of long complicated formula - this is much much better!

I will take your advice and use your method.

Thanks once again for all your help on this.

Answer 3

Hi,

Thanks for your help.

The letters a,b, c, etc  in column A represent 12 categories of questions.

The string eg q1a represents the standard wording of a question linked to that category.

I am trying to analyse (a) the number of questions per category (these standard questions -  may be repeated any number of times by other customers) and in any order of category, question.

If there were 9 q1a for category a and 3 q2a and 2q3a, then I would want to return:

9/14% for q1

3/14% for q2

2/14% for q3

in, say e2001, f2001, g2001 (assuming maximum no of questions and repeats would never exceed 2000)

There are a fixed number of questions per category, eg 3 different questions for category a, 4 different questions for category B etc.

Therefore I want to give a unique number to all occurrences (including repeats) of questions matching category and question no.

The other percentage I'm analysing is the total number of questions asked and the percentage of each category questions (including all repeats) to that total.

If 2000 questions were asked and 10 were from category a (ie repeats of q1a, q2a, q3a) then it would be 10/2000%

Hope this makes it a bit clearer

Many thanks

Answer 4

Let me start with the second part: the formulas for e12, f12, ...

In e12, put

   =COUNTIF($D:$D,COLUMN()-4)

and copy rightward as far as needed.

The first part is puzzling: the formulas for column D. I'm not sure what kind of "match" yields the

numbers in the example.

Maybe you're looking for the first occurrence of a value in column C. This would assign a number to

each distinct text string in column C. If so, first:

    Put the number 1 in D1.

Then: 

    Put the following in D2 and copy down as far as needed:

      =IF(C2="","",

             IF(COUNTIF(C$1:C1,C2)=0,

                        MAX(D$1:D1)+1,

                        VLOOKUP(C2,C$1:D1,2,FALSE)))

This matches column D in the example, but column B doesn't enter the calculation at all.

Hope this helps getting started.