Substituting Values From A List
Nov 18, 2006
Master List:
AA1/AB1-Bailey
AA2/AB2-Johnson
AA3/AB3-Madison
JA1/JB1-Parker
JA2/JB2-Evans
JA3/JB3-Metz
MA1/MB1-Hill
MA2/MB2-Reynolds
MA3/MB3-Sanders
I run a report that generates a list like this:
I need a way to convert the AA1,AA2,etc. in column B1 to the names they represent, when I paste my report in. The values in B1 vary each time the report is ran and there are 2 values for each name so I need it to always resolve to the right name.If anybody has any ideas,like a macro or formula,
View 9 Replies
ADVERTISEMENT
Dec 1, 2009
Got the following data in different cells:
And on a different sheet in the same workbook, have 45 different values to be converted:
What is required?
Knowing that using a VLOOKUP function, i can compare a value in the first cell to the list and return the desired value, i need to substitute the strings of characters with those the the list of 45 values so that i can proceed with my work.
Basically, for the 1st Cell for eg..
2232-2232-
I will need a formula to replace "2232-" by the value which a VLOOKUP function will give when comparing to the list of 45.
Result shall look like below:
20-20-
Looks simple when have the same set of characters in the cell, but for a cell which has multiple sets:
2210-22G1-45R1-45G1-20R1-20GP-
It becomes difficult for me. What i need exactly is:
Formula to substitute using the vlookup function so that it performs a lookup of the value in the specified cell with the list of 45 values and returns the value (20- or 40-) to be substituted in the cell. What i need finally is a set of 20- & 40- replacing the sets of present data.
View 9 Replies
View Related
Jan 3, 2013
trying to substitute out values that i am using in data validation. From the sentence:
Consulting (Logistics, IT, Management)
I would like to remove the spaces, the (, the ) and the commas so that it reads:
ConsultingLogisticsITManagement
So that i can use it for data validation.
In the data validation box i am trying the formula:
=INDIRECT(SUBSTITUTE(substitute(substitute(substitute(c2," ",""),"(",""),")",""),",","")
But i keep getting told i have an error.
View 6 Replies
View Related
Oct 18, 2012
telephone codes in SA begin with the international code of 27 which when followed by a mobile number drops the zero from the Mobile numbers. eg 27722732415 without the SA code should be 0722732415. i want to remove the 27 and substitute it with a 0 ONLY if it occurs at the beginning of the number and not when it occurs in elsewhere within the telephone number
View 8 Replies
View Related
Feb 18, 2010
The content of cell "animal!A1" will change according to a simple vlookup table. Let's say the value can be "dog", "cat", or "horse". In cell "animal!A5", I want to duplicate the content either "dog!A5", "cat!A5", or "horse!A5", depending on the current value of "animal!A1".
I've tried to do a simple reference like:
="A1"!A5
or
=A1!A5
wanting the A1 to actually read either dog, cat, or horse so the reference would refer to the worksheet of the same names. This doesn't work, so I need to know if there is a way to do this.
View 9 Replies
View Related
Jun 15, 2007
I'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create
Name: Fruit
Refers to:
banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
View 4 Replies
View Related
May 28, 2014
The function below should return the value in a cell IF the string in the cell two cells to the left of it is "Nemo" Omitting the part highlighted in blue, my function returns a list of unique values...but when it's included, nothing is returned at all. Perhaps, there is an error in the syntax that I'm overlooking?
Function listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String
[Code]....
View 2 Replies
View Related
Dec 17, 2012
how to list values from multiple columns in a dropdown list based on lookup value of 1st column as below.
This is how the table looks like.
Product MOLD1 MOLD2 MOLD3
4" AB1 AB2 AB3
6" ZA2 zd4 -
This is how the dropdown list should look like for Product 4"
ab1
ab2
ab3
View 6 Replies
View Related
Sep 15, 2013
I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)
The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.
Sample data Required Output
A | B Z
1| 10 | a 10,a,b,c
2| 10 | b 11,a
3| 10 | c 12,a,b
4| 11 | a
5| 12 | a
6| 12 | b
My system is Windows 8 Excel 2010.
View 7 Replies
View Related
Feb 24, 2009
Sheet2 contains various data, including one column of dates.
What I would like to do is populate Sheet1 with functions that search Sheet2 for date values between two separate date values on Sheet1 (cells F1 and G1). When a match is found on Sheet2, row contents from Sheet2 should populate on Sheet1.
In addition, I would like the functions only to match listings in which the value in column E on Sheet2 is greater than zero.
I have attached a sample workbook that illustrates the structure of the worksheets and the desired results. Looking forward to finding a good solution.
View 5 Replies
View Related
Aug 14, 2014
1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).
I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)
2. To include all unique string values except those starting with the letters "IC"
3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.
Here is the formula I currently use which serves #1 (above):
[Code] .....
Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.
Example of desired results:
Column A | Column B
AA | AA
DD | CC
AA | DD
CC |
DD |
DD |
IC |
IC |
View 14 Replies
View Related
Feb 11, 2013
compare some list of values in order to fiind the common values.
View 1 Replies
View Related
Jan 12, 2014
how I can list 3 different values to a list of list of 3 in a combo box, is.
combo box
select high = 35
select middle 30
select low = 25
I have the names in the box I just need it to add a value (which I have listed 1 in each separate cells) to each selection
View 9 Replies
View Related
Feb 24, 2007
I have two lists in the same workbook:
List 1) Contains customer contact information, including an account number. These account numbers may be duplicated in the list.
List 2) Contains account numbers of customers who wish to be removed from the first list.
I need to remove the rows from the customers list (List 2) where the account numbers match, and also copy those to another list for review. So far, I can manually choose and run some code to remove one particular account number only (eg 123):
Set FoundCell = Range("A:A"). Find(What:="123")
'Locate information to remove
Do Until FoundCell Is Nothing
FoundCell.EntireRow.Copy
Sheets(" Deleted List").Select
'ActiveSheet.Next.Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select..................
View 3 Replies
View Related
Dec 5, 2007
I have two worksheets, one which contains a list of workstation numbers, and another which contains all of the workstation numbers and additional information on the workstations in the company.
I want to remove all workstation numbers and their information which are not present on the first list from the second list.
In the first w.orksheet each workstation number is in a seperate row, and in column one (seperate cells).
In the second worksheet, each workstation number and its corresponding information is on the same row, and each different workstation is on a seperate row with its information.
View 9 Replies
View Related
Jan 31, 2014
how to get this with formula:
Column A - - Column B
a - - - - - - - a
a - - - - - - - b
b - - - - - - - c
c
c
View 8 Replies
View Related
Mar 19, 2009
I'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.
View 2 Replies
View Related
Aug 24, 2008
I have an excel spreadsheet that is several thousand entries long. There are cases in which duplicate entries will spring up on that list.
What I want to do is come up with a formula that will find the first duplicate entry in that list and then display that entry in cell on another worksheet. If it was at all possible, I would also like to have the row that the duplicate entry is contained on display as well, in a different cell of course.
I do not want to highlight them using conditional formatting since I have used that to perform other tasks on that sheet and that wouldn't display the entries in a different cell for me, and I would prefer not to use a macro to perform this function it that was at all avoidable.
View 7 Replies
View Related
Oct 26, 2009
I need to have a drop down list which displays a different set of values depending upon the value selected by a previous drop down list. ie. (drop down box 1)= x, y, z. (drop down box 2)= either x1, x2, x3, or y1, y2, y3, or z1, z2, z3. I can produce a single drop down box thats not a problem but linking several drop down boxes is beyond me .
View 4 Replies
View Related
Aug 13, 2007
I have a list of values on my spreadsheet in a similiar order to this:
17.91
16.59
15.00
14.86
13.56
12.17
11.01
0.0
I use the average function to work out an average for all the values, but since the value of 0.0 is showing i should not include this as an average. the range of values above (8) will always be the same for the month - so i need to find a way of omitting the 0 value without keep changing the formula
View 9 Replies
View Related
Dec 15, 2009
How can I extract non zero values from a list of values
View 9 Replies
View Related
Jan 20, 2014
I need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.
Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.
Book1.xlsx
View 2 Replies
View Related
Dec 4, 2008
I have been having fun with listboxes all day and have one last(!) problem. I need to get all of the values from a listbox and place them in variables, the trouble is I do not know how many items there will be in the list box, no more than 20 probably.
View 5 Replies
View Related
Dec 9, 2009
I have a drop down box in column N, however I would like the options in that drop down box to change depending on what appears in column M.
If column M Says "Lapse" then I want one drop down box to appear in column N, If coumn M says "NTU" then I would like column N to show different drop down options.
View 12 Replies
View Related
May 20, 2013
I have a list in Column A" Job List" (the amount of entries will change based on job openings). There are some cells that are blank; randomly throughout. I need to create a new Column B "Current Jobs List" with no spaces. I have to do this weekly and each time I update my "Current Jobs List" from the new data in the "Job List" without any spaces.(I do not want to just do a filter) .
View 6 Replies
View Related
May 13, 2013
I'm looking for a formula that can sum the first character of a list of values together, based on the cagegory.
Example below:
Column A = The category (or name of a person)
Column B = The status, I'm interested in the first character of the status (which is always a number)
Column C = The sum of the status based on the first character of the status for that specific person
James
1.0 Not Started
8
James
1.1 Not Ready
8
[Code] .....
I want to do this without adding another column (i.e. I can get the result adding a column with formula "=value(left(B2,1))", but I don't want to add the extra column)
I'd also prefer to avoid doing this with a macro, as this part of the formula is only a smaller part in a larger formula.
I tried to look for an entry in this forum, but couldn't find one that matches what I need.
View 4 Replies
View Related
Nov 6, 2013
I am trying to do up a summary for an attendance sheet that I have.
The problem is I have a number of values that could all qualify as a valid attendance marking and others I need to ignore
I have that list of value in a named range "Present" - Column G on the Lookups Sheet
And I have three sheets I need to add together
The layout of the three attendance sheets is directed so I can't mess with that - and we need uniformity across all the organisations that record attendances.
On the first sheet/tab I have entered some example markings - the "C" markings need to be ignored as they aren't in my list of valid values
The Summary sheet is still counting them as it is currently counting anything <>"".
I guess in short ... how doe I change <>"" to my named range "Present"
I have zipped the workbook as it is 445kb (as a result of some lots of conditional formatting and terrible layout)
View 2 Replies
View Related
Feb 14, 2014
I have the following situation: I have some values in column A. other values in column B. I want to be able to display a list using the values in column A and values in column B. A1-> B1, etc.. Attach file: Book2.xlsx
View 5 Replies
View Related
May 27, 2014
I am trying to build an information userform. I have 5 colomns on a sheet that include data, which I am keeping the sheet hidden.
What I am looking to do is have the information that is in these cells to populate in list boxes on a user form, I have tried
[Code] .....
I would like to do this as a loop though as there can be a mass range of information in the column.
View 2 Replies
View Related
Jul 2, 2008
I am trying to creatre a formula which list numbers from rows to columns by deleting the vertical spaces i.e.
A1 lists empty
A2 lists 3
A3 lists empty
A4 lists 7
A5 lists 1
A6 lists empty
result:
Columns: B C D E F G
3 7 1
similar to stacking values into a nice hoz column and not noteing any spaces
View 10 Replies
View Related
