Single Cell Comma Separated Values To Validation List
May 31, 2013
How to have a validation list (drop-down) from a single-cell in which values are separated by a comma.
Example I have.
I have a wine list which have many categories of which
COL A / COL B / COL C
Wine Name / Wine Producer / Vintage
I wrote a VBA code to look-up duplicates names and give the producers that make them. The names of the producers will be in an invis cell as Comma Separated Values.
For example: Wine 11 is made by producer X and Wine 11 is made by producer Y. I will have X,Y in a cell which need to be in a drop-down menu when Wine 11 is selected.
I got everything done. The only issue is to get the list. The problem is that the drop-down list shows X,Y as a single component and not as 2 drop-down components. If i were to write down X,Y in the validation list tab, it will show it as 2 components but referring to the cell doesn't.
I need to count the number of values entered into a single cell seperated by commas for record keepin purposes. I would prefer not to use a macro since I am not an expert, but will if i must. Here is an example of what i am talking about:
User A will enter "109,108,107,106" into a cell in Column A
At the bottom of column A the Total will read 4.
Now User B enters "110,111,112,113" into the next cell in Column A
I have a cell in a table containing user entered Comma separated values. How can I process it so that user entered values can be referred to just like in an array? Is it possible to do something like cell.value[0]?
For example -- cell A1 has values red, blue, green. B1 has a formula that can refer to "red" in A1 by writing something like A1.value[0]
Is it possible to have a function return values to another cell reference than from the cell where it is called? Like a UDF called functionIndividualValues(InputcellReference, OutputCellReference) functionIndividualValues(A1, A2) meaning that the input string for the function is in A1 and the output should go to the cell A2.
Parsing values in a column. COLUMN A contains comma separated numbers, varying from possibly an empty cell, to a list of values between 1 and 12 separated by commas. I need to examine the column and place the number 1 in the column corresponding to the number. There will be no duplicates in COLUMN A. Is there an 'easy' way to do this. The table is part of a much larger spreadsheet.
COLUMN A Team 1 Team 2 Team 3 Team 4 Team 5 Team 6 Team 7 Team 8
I have one column of data. there are currently ~10k rows, but this will increase over time. each row has either a single value (example: pepsi) or a comma separated value (google,samsung) with up to 6 commas. instead of the rows looking like this:
pepsi google,samsung coca-cola
I want them to look like this in one column (preferably via a formula):
pepsi google samsung coca-cola
I would like the above output to be a unique, alphabetized list.
Is there a way to create a drop down list from a comma delimited list in a single cell? For example, col A is Name & Col B is the delimited list - Blue,Red,Green (list can be different for each name). Would like a drop down list in col C that allows you to pick one of the values from Col B.
I have a single cell with 4 values in it all separated by commas and i would like to pull out each one separately. The number of characters in each value changes.
Given a list of beginning years and ending years, how can I create a column that gives the whole list of inclusive years separated by a comma?
Start Year End YearList of Years 1985 1995 1985,1986,1987,1988,1989,1990,1991,1992,1993,1994,1995 2000 2009 2000,2001,2002,2003,2004,2005,2006,2007,2008,2009
I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".
I have a list of comma separated values. I would like to change these to tab separated values, and then transpose them so that they are all displayed in one column. Does anyone know if it's possible to do this in Excel 2007?
I need the Keywords column to display each value separately, and in one column. I also need each row to have the original date information. So I need it to look like this:
Is there a macro that will allow me to create text to row from a comma separated cell, but also associate the cell to its left automatically?
The table below exemplifies what I need. The top of the spreadsheet is how my data is currently. The portion after the break is how I would like it to be.
I routinely have do download massive data sets of reporting that is saved as an excel spreadsheet. The three columns of sample data attached are Report Numbers, Report Evaluation Serial Numbers, and Report Evaluator ID.
As seen in the attached spreadsheet, there can be multiple Report Numbers (in same field separated by comma) which have been evaluated by different Evaluators. It is my responsibility to account for the number of Reports that have been evaluated, and many other metrics from like data. The issue I have is when more than one report number is listed in the same field I need to parse the data into its own field for ease of counting (and also maintain the adjacent data). It is not a problem to merely copy the fields and delete the excess numbers, however when dealing with thousands of Reports, and Evaluators this can be very time consuming. In the attached file I have separated .xls into three workbooks to help explain my problem: Initial state of Data; What I need To Parse Out; and The final endstate I require. I hope that I have explained this issue with enough detail. I am sure that the attached file will explain better.
I want to select items in a listbox and transfer those items via command button in a textbox. The listbox is already filled. I have no idea how to realize that.
Attached is the form I created so far. I copied everything together and matched it up for me. It's probably not the best way but it works. I marked the section where I need help in yellow.
I receive a large spread sheet weekly, one or the column contains comma separated values (CSV), I need to separate those values 1 per cell. Maximum CSV is 5; I would need to get the output to 5 cells next to them, Example,
First, I have read through the board, and I was not able to find a solution already posted. I apologize if in fact this is a duplicate.
Second, I am comfortable with dynamic lists, dynamic ranges, chained validation lists, etc: so I am pretty sure my question is not a repeat of the other recent validation list questions.
Therefore:
Is it possible to generate a validation list from a single cell with a comma separated list?
For example, if I have “Fresh, Cool, Hot, Neutral, Smooth” in given cell, can I then have those 5 items appear as separate choices in a validation list?
I have tried every combination I could think of using arrays, named lists, the INDIRECT function, etc. I cannot seem to get it to recognize them as separate items. For the moment, I have created a bad (too long/complex) workaround where formulas are used to pull out each of the 5 strings into individual cells.
I am using the following VBA to add all cell data into one cell separated by a comma, but I need a space after each comma and and do not know how to edit the VBA. How to add a space after each comma?
Function Combine(WorkRng As Range, Optional Sign As String = ",") As String 'Update 20130815 Dim Rng As Range Dim OutStr As String For Each Rng In WorkRng If Rng.Text "," Then OutStr = OutStr & Rng.Text & Sign End If Next Combine = Left(OutStr, Len(OutStr) - 1) End Function
I have four columns of data. Column A is for the Model, B is for options, C is for color, and D is for Trim. What I need to do is for each model I need to have the options in column B to be listed down the page without commas. Each 3 code option needs to have it's own cell. In the below example there are 8 models with their options, color, and trim. I cannot have the options mixed up with the other models. Hope this makes more sense.
In filed I have couple of value separated by comma like below:
A1 header1 B1 header2 C1 header3
Audi 592035, 579733, 653749, 579735 20 000
If my macro found that string (always will be separate by ",") should split the string and add rows (= to number of string). The output should be as below:
A1 header1 B1 header2 C1 header3
Audi 592035 20 000
[Code] .....
I have:
Code: Set sourceWb = ActiveWorkbook Set ws = sourceWb.Worksheets(1)
I have a relatively large data set that is separated into columns A-G. In column G, I have a multitude of values that are separated by commas. What I would like to do is to separate these entries into rows while keeping those values in A-F constant.
Here is an example of what I would like to do through VBA:
ex1.jpg
to
ex2.jpg
Also, kudos. This website has now gotten me interested in VBA.
For example the cell contains 1,M2,M7,M1,M8:2,M15,M9,M4,M5:3,M3,M6,M14,M11,M12:4,M10,M13 and I need to create M1, M2 , M3 ..etc columns and attach the coresponding number to each one of them. For instance M2,M7,M1,M8will get 1 and so forth and so on. I was acutally thinking using the Notepad to replace the , with a TAB space and paste them back in to my data set and create some IF statements. On the other hand running VBA scrip would make it even easier. However I would preffer to use a function (that I am not 100% familiar with) and leave the VBA scrip out of the question for the moment.
