If I have, in one cell (call it D1):
EH,DR,HU
and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E
I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...
eg...
F,G,H
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.
View 9 Replies View RelatedI have a cell witch contains CSV I need too look up all the values off a master list an out put the vlookup results in 1 cell
Example:
( SHEET 1/Cell A1)
10,11,12
(SHEET 2/ look up list)
.A .B
110101
211102
312103
413104
514105
(SHEET 1)
I need the output to show the following in Cell B1
101,102,103
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
At the bottom of column A the Total will read 8.
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 be able to select up to 5 different models in cell A1 from a drop down list then have cell B1 look these values up and find the volumes and then return the sum of the volumes - Cell B1 should read 130. There will always be one model selected but it will vary from 1 to 5 selections.
M1,M3,M4,M5
=10+30+40+50
M1
10
[Code]....
code1
code2
lookup Range1
lookup Range2
Return Value
Lookup
Multiple Return
[Code]....
I am trying to vlookup multiple value and return multiple values in one cell
Comma separated data on sheet 2, look up info on sheet 1, return comma separated data on sheet 2.
Sample file attached : Book1.xlsx
I basically need to grab all the numbers going down in a row and get them into one cell in Text format with a comma between them.
A1
5293
5294
5295
5296
5299
5300
5301
5302
5303
5304
5305
B1
What I need in Text format
5293,5294,5295……
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
[code].....
i would like the code that allows me to copy enything I typing in column a sheet1 to column a in sheet2
View 8 Replies View RelatedI 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.
I have the following syntax in B1: "1,2,3,5,6". and I need VBA to take the very last number out of that list, and increase by four in single steps. For example: before the code: "1,2,3,5,6" and then after it "1,2,3,5,6,7,8,9." The code must be flexible, though, because it will be running within another Macro, and it must work whether the last number in the list is 1 digit ("...4,5,8"), 2 digit ("...34,35,36") or 3 digit ("...111, 113, 114"). Those three examples would be changed into "...4,5,8, 9, 10, 11" "...34,35,36, 37, 38, 39" "...111, 113, 114, 115, 116, 117".
View 4 Replies View RelatedI 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?
Example:
Date | Keywords
1/01/01 | shoe, shoes, clothes, clothing
1/02/01 | shirt, shirts, jacket, jackets
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:
Date | Keywords
1/01/01 | shoe
1/01/01 | shoes
1/01/01 | clothes
1/01/01 | clothing
1/02/01 | shirt
1/02/01 | shirts
1/02/01 | jacket
1/02/01 | jackets
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 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".
The sample sheet has been attached for reference.
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 have over 600 rows that needs this done.
1,2,3,4
B
[Code]....
I know something very similar was posted before, but unfortunately, it wasn't what I was looking for. I have a Worksheet tracking several associates and the department they have done work in. (Our associates are contractors for other departments). I need to have a final worksheet that allows me to pull up a name, and display every dept they worked with in the past week.
View 9 Replies View RelatedI am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.
I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:
To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).
I already have it working for returning a single value with the following formula but need it to return multiple values.
=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")
I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.
I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:
To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).
I already have it working for returning a single value with the following formula but need it to return multiple values.
=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,
MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP
(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies View RelatedI have a list of emails I need to merge into one cell and separate by commas. It looks like the follwing
A
1. xxx@xx.com
2. xxxx@x.com
3. x@x6.com
I need it to look like
A
1. xxx@xx.com, xxxx@x.com, x@x6.com
I have a table in the format below with about 3500 rows
Column A
Column B
0001
All vehicles, Retirements
0002
All vehicles, Retirements, Addition
0003
All vehicles, Retirements, Addition, Deletion from Y
I would like to change it to the following format:
Column A
Column B
0001
All vehicles
0001
Retirements
0002
All vehicles
0002
Retirements
0002
Addition
0003
All vehicles
0003
Retirements
0003
Addition
0003
Deletion from Y
I've a question. For instance
apple,lemon,strawberry
fruit
green,blue,white,black,grey
color
...
...
...
...
I've a list like above. How can we use lookup formulas from comma-separated cells?
white> color
lemon> fruit
I have a list of data in individual cells in one excel column thus...
Item 1
Item 2
Item 3
And I would like the data to read thus in one cell...
Item1,Item2,Item3.
Is there an excel function I can use to do this quickly and efficiently?
Cell(i,1)have 3 Numbers
Each Number Not Allowed Greater Than 10
Each Number In Cell(i,1) Will Be Added 1 In Cell(i,3) And Cell(i+1,3)....
How Can I Seperate Numbers And Make Three Variables To Run Macro
A
1,3,10
2,5,9
C
2,3,10
1,4,10
3,5,9
2,6,9
2,5,10
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,
Source data
(A1) 10,11,12,13,14
Output needed
(B1) 10
(C1) 11
(D1) 12
(E1) 13
(F1) 14
(note, not all field contain 5 values, but they all contain at least one)
When the entry in a cell is of the format e.g 3,4 is there any way to find the value 3 or 4 in that cell?
Attempts like:
Cells(i, 1).Value = variable (e.g variable=3)
or
Set cell_found = Cells.Range("A1:A10").Find(variable, lookat:=xlWhole)
do not find the cell with the 3 (or 4). The above work fine when 3 or 4 are alone in the cell. Is there anyway to find these values in the cell?
I am in need of a macro that will scan a worksheet row by row, noting the column names (found in I2 to AQ2) in a specific horizontal span of cells (I to AQ) that hold (any) data. The macro will then paste these column names in a designated cell on each corresponding row (always found in column F), separating the column names with commas.
I am trying to do this for multiple worksheets containing ~100 rows - the example attached is just a quick demo of what I'm hoping to achieve. Also, these worksheets are contained in one big workbook, so ideally I'd like to be able to run the macro once and have it apply to every sheet in that workbook (they all have the same layout - the only big difference is the number of rows). If the Total Scenes part at the bottom of the sheet is problematic in getting this to work, it's fine to remove it.