Compare/Sepearte Values from Cell

I have a set of survey data in Excel format. Some questions are in the format 
of "check all that apply" and the answers i have are in a single column of 
cells with each answer seperated by a coma. The answer could also be "other". 
For some reason, the survey doesn't mark the answer as other, but simply 
record the data, so i can have answers like

A,B,C
C,E,custom input data1
A,custom input data 1

I want to get the data into seperate columns with binary indicating values 
like
A  |  B   |  C  |  D  |  E  |  Other | Value of Other
1     1        1
                1             1         1        custom input data1

How should I make this happen
1) Is there any excel function that interprets a filed with a contain like 
function. The only one i know of is search(), but search() returns error 
"#Value" when it fails to find a match and I don't know how to convert 
"#Value" to a useable value understood by other functions like if()

2) How should I get excel to understand the "other" field and parse the 
answer from the preceding cells. Is there anyway i can do this without a 
macro. If i need to write a macro, how do I make a macro parse the data as I 
showed above.
0
ren (13)
4/6/2007 8:58:03 AM
excel 39879 articles. 2 followers. Follow

1 Replies
456 Views

Similar Articles

[PageSpeed] 13

Nevermind. I figured this out using a combination of search(), IsError(), 
Len(), and Right() Functions. A macro wasn't needed.

"Ren" wrote:

> I have a set of survey data in Excel format. Some questions are in the format 
> of "check all that apply" and the answers i have are in a single column of 
> cells with each answer seperated by a coma. The answer could also be "other". 
> For some reason, the survey doesn't mark the answer as other, but simply 
> record the data, so i can have answers like
> 
> A,B,C
> C,E,custom input data1
> A,custom input data 1
> 
> I want to get the data into seperate columns with binary indicating values 
> like
> A  |  B   |  C  |  D  |  E  |  Other | Value of Other
> 1     1        1
>                 1             1         1        custom input data1
> 
> How should I make this happen
> 1) Is there any excel function that interprets a filed with a contain like 
> function. The only one i know of is search(), but search() returns error 
> "#Value" when it fails to find a match and I don't know how to convert 
> "#Value" to a useable value understood by other functions like if()
> 
> 2) How should I get excel to understand the "other" field and parse the 
> answer from the preceding cells. Is there anyway i can do this without a 
> macro. If i need to write a macro, how do I make a macro parse the data as I 
> showed above.
0
ren (13)
4/6/2007 10:46:00 AM
Reply:

Similar Artilces:

Custum Cell Format
I have an array formula which automatically summarizes all available values of a certain column, in ascending order. The summary is fixed to a maximum of 8 cells and in many cases it uses less than the available 8 columns. Is it possible to format the cells by showing a 0 when the result/cell is empty In other cells in the sheet the cells show empty instead of a zero by using the custom format of 0;-0;"" Now I want an empty cell to show 0 Thanks, W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ----------------------------------------...

Plotting with a formula in a cell
Hi, I need help with this, I am sure it is a simple answer, but I cannot figure it out. I have a workbook with 2 worksheets in it. The first worksheet has all the inputs from my data in it, and the second worksheet has the charts in it. I have pasted the links into the second worksheet and all is fine with that, but when the value is zero, it plots it as a zero. I would like to just have it as a gap when the value is zero. I have gone into the options menu and clicked on the one that reads "NOT PLOTTED,(LEAVE GAPS)", but it does not seem to fix it. What I found is that be...

Count of values within a cell?
Hi, I have a spreadsheet containing a row with data something like: A | B ____________ 1 | 1111 2 | 1112, 1113, 1114 3 | 4 | 1115 If I were to do a COUNTA(B:B) I would see a count of 3. What I'd *like* to find is a way to see a count of all numbers in that row (in this case, a count of 5). Any suggestions? Thanks! Jeff =SUMPRODUCT(--(B1:B100<>""),(LEN(B1:B100)-LEN(SUBSTITUTE(B1:B100,",",""))+1) ) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) <jeff.fry@gmail.com> wrote in message news:1163...

how can I view all the data in a cell?
I'm working in Excel 2003. Throughout the entire spreadsheet there are cells that when printed do not show all of the data. We have tried formatting the height and width, wrap text but nothing seems to work. Does anybody have any ideas? RC Excel Help on "limits" or "specifications" reveals that Excel will allow 32,767 characters to be entered in a cell. However, it goes on to state that "only 1024 characters will be visible or can be printed" To work around this limitation, stick a few ALT + ENTERs in at appropriate spots, about every 200 characters.. ...

Count only cells with red font.
I am working with Excel 2003. I currently have my data range set up to count only non-blank cells with a counta function. However some of those cells contain gray font and others contain red. How can I change my formula to count only non-blank cells that have red font? -- Have a great day! Thanks for your help! Sincerely, Cassie Start with Chip Pearson's site: http://www.cpearson.com/Excel/colors.aspx If you're new to macros: Debra Dalgleish has some notes how to implement macros here: http://www.contextures.com/xlvba01.html David McRitchie has an intro to ma...

comparing from one sheet to another
Hi. I am fairly new to Excel, and am trying to make a workbook to analyze home purchase costs, etc. One thing I want it to do is be able to enter a purchase price on the main sheet, and then compare that price to data on another sheet that has a range of home prices and the title/escrow fees at that price range, and them put the corresponding fee back on the main page. Any idea how I do this? Thanks. Hi have a look at VLOOKUP. See: http://www.mvps.org/dmcritchie/excel/vlookup.htm -- Regards Frank Kabel Frankfurt, Germany "Trish" <anonymous@discussions.microsoft.com> schri...

#Value! errors
I have experienced a problem that I am unable to find a rectification for. When my staff constantly use the space bar to delete an entered in error number and leave the cell blank (although there is a space in the cell) any cell which uses that cell within its formulation shows #Value! or #####. I am aware that they should not be using the space bar to delete numbers and the space within the cell is the reason for the other formulation errors. I guess my question is how do I format the cell so that only a number and nothing else can be entered? -- Field8585 --------------------------------...

INSERTED table no holding exact float value
Hi All, I have a table A with a float column and a trigger to write to a log on inserts to table A. I am using the sql INSERTED table to get the value of the float column, and insert it into a varchar column in the log (cant be changed, its generic and used for multiple tables). I am having a weird issue where the value in the log gets rounded to the next integer in the audit table, it inserts correctly on Table A though. Is this a known issue? Is there a workaround? If not, what am i doing wrong? Thanks in advance, Ben *** Sent via Developersdex http://www.developersdex.co...

change default value "save message"
Hi all, I have a little problem. When i wanna save a mail message, de default filetype = rtf. I wanna change that to *.msg file. But i can't get that default! i have to change it every time manual! ...

Inseting data in cells
I have a row of cells that have last names in them, is there a way I can insert some data into all of the cells, this data will be the same for all names. Ex Before jones1 After (alias=jones1) then if possible export all of the names to a word or text file all in one line. Ex (alias=jones1)(alias=smith2)(alias=johnson3) Thanks for any help!!!! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Assuming last names are in A2:Z2 Put in A3 :...

Get values from blank cells
Hi, I have some width and height values in several columns with a lot of blank cells of sheet2. Like this: A B C blank blank 2*987 blank blank blank blank 4*500 blank 5*400 blank blank How can I get only the cells with values in column A of sheet1: A 5*400 4*500 2*987 Can this be done! Thank you so much What do you want to do if there are two values in a row? Average them? Add them? Both of these would work as long as if the values were numbers or blank. "canvas" <spyele123@gmail...

MS Excel VBA Pivot table link cell fetch records
MS Excel VBA Pivot table link cell fetch records I have a table 'tbl_Final' in MS Access 2007 where the data is used and cached in a pivot table found in the 'Data' worksheet in MS Excel 2007. In this MS Excel 2007 workbook, I have various worksheets reports which links to the pivot table values found 'Data' worksheet. I linked formulae in one of the cells found the worksheet reports, looks like this: =GETPIVOTDATA("Amount",Data!$B$4,"Month", 2,"Year","2008","Scenario","Budget") Lets sa...

How can I assign a value to a cell without putting a formula in it
I have one cell (C33) that has a list validation attached to it. The list (column A on a separate sheet called Data_text) is a series of text statements that the user can select from the drop drown. I have a second cell (C44) that has a Vlookup formula based on the contents of the first cell (C33). The Vlookup formula returns the text in column B (from the same list, Data_txt) from the selection made in column A (or what is now in C33) Both cells have only text values. I need to assign the returned Vlookup value to C44 but I don’t want to put the Vlookup formula in it. Th...

Extract names from string based on value list
I have a list of productnames and a list of brandnames. I want to supply the productnames with an attribute "Brand". I am currently manually filtering the list of productnames based on "Contains... Brand" and adding the Brand to the product using copy paste, but this takes me too long and I can only match records one brand at a time. Can I make Excell search through the list of productnames and whenever it comes across a brandname within the string of a productname it will add it to the column next to it? One way Assume productnames running in A2 down A...

How to prevent users from selecting protected cells in XL97 ?
Hi, I created a workbook in ExcelXP but when I put it on a XL97 pc protected cells can be selected. Is there a way to correct this other then upgrade XL? Thanks a million in advance. -- Marvin Hlavac Toronto, Canada Hi Marvin You can do it with VBA code If you copy this event in the Thisworkbook module http://www.mcgimpsey.com/excel/modules.html It will run automatic when you open the workbook and Protect each sheet in the workbook and set the EnableSelection Test it on a test workbook Private Sub Workbook_Open() Dim Sh As Worksheet Application.ScreenUpdating = False For Ea...

removing blank cells #2
Column A looks like this (there are actually 162 cells, not just the shown here) @Sea @Sea @Sea Bos Bos Bos @Oak @Oak @Oak I want to get all the cells with an @ symbol in them to be in column without the blank cells in between and all the cells without an symbol to be in column B. I have to do this for 30 sheets so I don' want to have to cut and paste on all 30 sheets. Is there a way to d this with a formula or formatting? Thanks -- Message posted from http://www.ExcelForum.com Hi try the following: 1. In B1 enter the array formula (entered with CTRL+SHIFT+ENTER): =INDEX($A$1:$A$...

Put all cells in one cell seperated by comma
I have an Excel spreadsheet with data in seperate cells such as wordone wordtwo wordthree I need all cells in one cell seperated by a comma such as wordone,wordtwo,wordthree I found out how to add the comma after the cell data, but cannot figure out how to put them all in the first cell. Can anyone help? Christine -- kokopoko ------------------------------------------------------------------------ kokopoko's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25892 View this thread: http://www.excelforum.com/showthread.php?threadid=392760 =a1&",&q...

Need Vlookup to work with formula in reference cell
The following formula does not do the lookup: VLOOKUP(T7,Dues!$A$3:Dues!$G$154,2,FALSE) When T7 cell contains =(B9-S9) When I put the total that T7 equals in T7, it works. Any ideas how to make it work with the formula? thanks mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581 View this thread: http://www.excelforum.com/showthread.php?threadid=393139 My guess is that T7 is not what you think it is. If you see 9, it could be 9.0000000001. You coul...

how do i pick up the values ?
This is a multi-part message in MIME format. ------=_NextPart_000_01C7_01C33A40.6E296370 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi=20 I have this XML file, and i am not sure how do i read this xml file and = read it to an arraylist, for which i use to fire some SQL statements.=20 I want to be able to pick only xml attributes, say, which belong to = Section=3D"two-4" and put them into an arraylist and similar for the = rest of the Section attributes. i had got the code below working, till the time, i had to change the...

2nd Request
The two tck_201_ fields are text boxes on a form that are being set by to calculated text boxes on a different form!subform. As you can see by the debug statements and results, the values will just not transfer. I have set the format for both of the TCK _201_ fields to Fixed! Any Ideas? Me.TCK_201_Done = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual Me.TCK_201_Tot = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_NumDates Debug.Print Me.TCK_201_Done Debug.Print Me.TCK_201_Tot Debug.Print Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual Debug.Print Forms!frmPT...

Securing a cell.
Is there a way to lock the data in a cell so no other user can edit it and still leave the edit ability in all other cells? Thanks. Hi Peter, 1) Select the cell, use menu item Format|Cell; open Protection tab; click to remove check mark in Locked box 2) Use menu item Tools|Protection and click Protect Sheet (password is optional) Best wishes Bernard "Peter" <grouchyfd@bellsouth.net> wrote in message news:1bd601c3e026$c67d37e0$a401280a@phx.gbl... > Is there a way to lock the data in a cell so no other > user can edit it and still leave the edit ability in all > othe...

Using a cell reference as the axis value for Horizontal axis crosses
Is there any way of referencing a cell as the value in the "axis value" area of the "Format Axis dialog box. I am currently entering a number i.e. 2.5. I would like to be able to have this number change when a user enterer a value in a cell? Would it have to be VBA code or is there another way? Hi, A coded solution is currently the only way. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info <betuttle52@gmail.com> wrote in message news:408bbd01-b24e-4ca3-9e63-02daed2b146e@s8g2000prg.googlegroups.com... > Is there any way of referencing a ce...

Cell Referencing Problem
Greetings, In excel 2000 I would like to be able to read in different values for AK into the following cell entry: ='Feb01-04'!$AK39 Stated another way, I would like to rewrite the above cell reference so that the value AK is read from another source cell. For example I might type AX into source cell A1 and the above reference would change to ='Feb01-04'!$AX39 Any help would be appreciated. Thanks, Don Rist Don, You can use the INDIRECT function to do this. E.g., =INDIRECT("'Feb01-04'!$"&A1&"39") -- Cordially, Chip Pearson M...

Add Date And Time in a cell.
I want to add the time and date for a cell, but I want these date and time to be automatically updated itself with each passing day, and second. So when I log on this excel sheet later, the cell will show a different date and time in which I am currently in. How do I accomplish this? Is this possible at all? use the Now() function - this is volatile and will be updated after each sheet calculation takes place. put the following in the cell required - it formats to date and time (which you can change using cell format) =NOW() Cheers Nigel "Raymond" <NotValidEmail@yahoo.co...

How to merge or split cells?
Hi, is it possible for Excel merge cells or split cell like tables in Word? Hi Min, Excel will allow you to merge cells, but not split them (unless they are part of a merged cell). To merge a cell, highlight the cells you want merged, Format>Cells>Alignment, and select merge cells from the text control sections (Excel 2003 - similar for 2000) Hope this helps. Warm regards Sunil Jayakumar "Min" <caomin111@hotmail.com> wrote in message news:Orcqf5paFHA.2124@TK2MSFTNGP14.phx.gbl... > Hi, is it possible for Excel merge cells or split cell like tables in > W...