• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Checking cell contents

kevinonearth

kevinonearth

Member
Hi,

A friend of mine has passed on a problem to me


He has a massive survey spreadsheet, one of the questions people responded to was:

What is the reason for hospital closure? There are 15 possible answers, numbered 1 to 15

So users typed in 1 for the answer

But there may have been more then one reason

So in some cells users have typed 1,4,7,10,11 for example or 1 4 6 2 12

What I want to do is count the reasons


I have used this function to count the occurences of 1:

=LEN(A1)-LEN(SUBSTITUTE(A1,"1",""))


If cell A1 contains: 1, 4, 7,

then the above function will return 1


But if cell A1 contains 1, 4, 7, 10, 13

then the above function will return: 3

which of course is incorrect, it counts the ones in 1, 10 and 13

how do I correct this?


PS I am aware that this problem should not arise in the first place if the spreadhseet was designed correctly, but the problem is there now and needs solving! Thank you
 
Hi


Can you post a sample of the spreadsheet so that we know exactly what we are dealing with. Dropbox or other....


Cheers

Glen
 
The actual spreadhsheet contains sensitive information but here is a sample:


https://dl.dropbox.com/u/90990975/sample.xlsx


Does that make sense to you?
 
Hi


This is a one shot deal - right? IE this is not a working spread sheet, you just want to clean the data so you can analyse the results?


I would:


1) Start again

2) Use the DATA : Text to Columns function

3) Delimited with , and space : Treat Consecutive delimiters as one


This will split the data into separate columns with one number in each.


Should be easy to process from then on in.


Hope that makes sense.


Glen
 
Thanks for the reply

Yes splitting the data in to separate columns would be a possibility

But the problem with that is you could potentially need to have 14 more columns


The original file was filled in by thousands of people and now somebody has to analyse the results


The sample file just indicates the problem, there must be a way of counting a 1 as a one and a 10 as a ten and not a 1 and 0!
 
For the cell using comas:

=LEN(D1)+1-LEN(SUBSTITUTE(D1,",",""))


For the cell using spaces:

=LEN(D2)+1-LEN(SUBSTITUTE(D2," ",""))


The second formula seems to work if both comas and spaces are used.

1, 4, 7, 10, 11


Regards,

Howard


Posted before I saw your example sheet. This probably is not going to help.

Howard
 
If cell D2 had this in it


1,4,7,10,11 OR 1 4 6 2 12


the two formulas I offered would return 5, the number of digits.

I believe you want the number times a particular digit occurs in the various cells.

So if you had ten number 12's then that would indicate there were ten no. 12 reasons

for a hospital closure in that persons opinion.


Howard
 
Hello Kevin,


Assuming A1 is the data & B1 to down is occurrences entered ie: 1, 2, 3...15


So, here is one way: in C1


=SUMPRODUCT((TRIM(MID(SUBSTITUTE(","&SUBSTITUTE(A1," ",","),",",REPT(" ",99)),99*ROW(A$1:A$15)-98,99))=B1&"")+0)


Note: After =B1 must be &"" to convert numeric values to texts.


If you select column A & replace all 'spaces' with 'comma' then you can remove 2nd SUBSTITUTE function.


Hope this helps,
 
Hi Haseeb

I tried your suggestion but it hasn't worked for me

would you be able to the sample I shared, modify it and upload it?

Thank you
 
Hi Kevin ,


Can you see the file here , with Haseeb's formula. Do you want the number of times a digit occurs in each cell at a time , or do you want the number of times a digit occurs in all the cells taken together ?


http://speedy.sh/Vmjp4/sample.xlsx


Narayan
 
Hi Haseeb A

what do you mean when you say assuming A1 is the data & B1 to down is occurrences entered ie: 1, 2, 3...15

???

Thanks

Kevin
 
You must log in or register to reply here.
Back
Top