Formula To Replace Values If Another Exists?

May 14, 2014

So I have this formula to basically lookup the letters in column L and if certain letters exist, then change those letters to another letter. Here is my Formula:

=IF(L1="A",IF(ISNUMBER(MATCH("B",$L$1:$L$200,0)),"B",L1),IF(L1="D",IF(ISNUMBER(MATCH("E",$L$1:$L$200,0)),"E",L1),
IF(L1="L",IF(ISNUMBER(MATCH("L",$L$1:$L$200,0)),"O",L2),IF(L2="F",IF(ISNUMBER(MATCH("K",$L$1:$L$200,0)),"K",L1),L1))))

For example: if column L looks like

A
B
A
E
D

Then it would convert the A's to B's since there is a B present and D's to E's, but if the B and E hadn't been in the equation then it would leave the A's and D's as they are. WIth this formula the output looks like:
B
B
B
E
E

My problem with this formula is that it only recognizes the first letter in each column. Sometimes I have multiple letters in a column and I need it to recognize them and change accordingly.

For example:
A
D_B
E

I would like to have it change to
B
E_B
E

I have attached a sample workbook that may make it more clear... If a VBA code would work better then I would be open to that as well. Combining question.xlsx

View 8 Replies


ADVERTISEMENT

Excel 2003 :: Find And Replace Missing The Ability To Replace Values?

Feb 5, 2009

This problem has come up fairly frequently lately, and I'm not sure how to fix it, or if this is by design...but in Excel 2003 I can't seem to do a "Find and Replace" based on the value of a cell. I can do a find, based on cell value, but the moment I change to the replace tab, the "values" and "comments" are missing from the "look in" dropdown.

I've only noticed this when I'm trying to replace on a filtered list, so I'm not sure if that is part of the issue.

Perhaps an alternative way of arriving at the same goal. Basically I have a worksheet with a number of filtered columns. They are filtered just right, using custom filtering, and so I do not want to undo the filters. In some columns I have formulas that are returning #VALUE! errors. I'd like to replace all of these cells with NA.

View 6 Replies View Related

Search And Replace: Loop To Look Up Xxx And Replace It With The Values Consecutively In Row C

Jun 15, 2006

I have values in row A and I have values in row C. I want to create a loop to look up xxx and replace it with the values consecutively in row C. Look at example for a better Idea. I found a way to find and replace, but I not sure how to use it with qoutes. I was thinking maybe I dont even need row A and just supply a list and excel could have the chunk of data in the code itself.

View 2 Replies View Related

Formula To Indicate Match Exists

Aug 20, 2009

to creating formulas in Excel!

Basically, all I want is for a formula to tell me if I have matching data in two separate cells. What I have is data entered in cell A1. What I would like is one single formula to check if the same entry has been entered in cells A2:A10 and if so, return a 1, and if not return 0.

View 9 Replies View Related

Formula: Look/Check If Value Exists

Oct 19, 2006

I got a pivot table that when selected will give me a value (actually its an ID number) I want in H3. I then got a list of values outside of the pivot table in column J. If H3 matched any of the values in column J, I need a “Warning”, if not “OK”. I have been using the formula =IF(H3=J3,"OK","WARNING") to match selected cells (H3 with J3) but cant get it to work to look down the column list. The OK or Warning is in cell F34 out of arms way!

View 2 Replies View Related

Fill Values And Replace Told Values By Vba Code Depends Upon 2 Columns

Mar 26, 2014

I have set of data in multiple range ,need to fill the and replace the old values depends upon two column values (AH & AL)

IF Active Calls is "TATA" In AH:AH, and IF Action Onwer Col is "Blank",in AL:AL
Then Fill the Blank cells by Values "SVC" in the col Action Owner,Then Replace Old values by "Updates Awaited" in Status Col(AM:AM)

Find the attachment & basic code take this code for this task

[Code] ....

toggle-2.xlsb‎

View 5 Replies View Related

Multiply 2 Columns When Another Formula Exists To Add In?

Aug 22, 2014

So I have the first part of my equation and it works fine. The equation is in Column D.

=IF(ISERROR(FIND("Van",A7,1)),"",B7*C7+50)

What I want it to do is: Combine the above for Col D with: if the Unit Name Column (A) says anything EXCEPT Van, I want it to multiple Cost (Col B) by QTY (E) and put the total in Col D.

View 7 Replies View Related

Formula To Determine If An Entry Exists

Jul 21, 2009

I have a database of 6 digit numbers in one column(let's say column A). I would like to put in a 6 digit number in a cell (b1)and have another cell (c1) give me a response as to whether that number exists in the database (Exists or Doesn't Exist).

View 2 Replies View Related

Lookup Formula: Tell If A Value Exists In A Column

Jun 8, 2006

i need a formula that tells me if a value exists in a column. for example if there is a value in a cell in the b column of 23 no matter where it is, i want cell c25 to return the word "yes".

View 2 Replies View Related

Replace Values In Range Of Cells With Varying Values?

Mar 14, 2013

I am trying to replace a range of cells with certain values but I can not figure it out. I'm almost there I think but don't know how to get the varying values I'm looking for. So I have a column (AJ in this instance) that has a bunch of "xx" values at different spots within the column. I want to replace those xx values with numbers 01 through 36. What I have below gets me just about there but it replaces every xx value with 01. How do I get it to go 01 on the first one, 02 on the second one, etc?

/code
Dim cell As Range
For Each cell In Range("AJ1", Cells(Rows.Count, "AJ").End(xlUp))
If cell.Value = "xx" Then _
cell.Value = "01"
Next cell
/code

View 3 Replies View Related

Formula To Return (1) If Any Value In A List Exists In Specific Cell

Oct 24, 2013

I am looking for a formula which will allow me to return a value, 1 or True, if any of a number of words or phrases in a list exists in a specific cell. Per the below example, any formula which will achieve the result in column A

For Example:

Sheet 1
Column A
Apples
Oranges
Banana
Fruit Salad
Fruit Smoothie

[code].....

View 6 Replies View Related

Formula To Count Dates Between 2 Date Periods If Specific Condition Exists

Apr 6, 2012

I am currently using this formula to count dates between 2 date periods If specific condition exists.

=COUNTIFS(
Data!S1:S100000,"*KP*",
Data!X1:X100000,">=10/1/2010",
Data!X1:X100000,"=10/1/2010",
Data!X1:X100000,"

View 3 Replies View Related

Replace Cell References In Formula With The Header Of The Cells Referenced In Said Formula

Dec 22, 2009

Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1

I need to reference the formula in D1 with the cells headers names.

In a perfect world, it would take
=A1+B1+C1:

and produce:
=Bananas+Apples+Oranges

View 9 Replies View Related

Replace Character Used In Formula Where Formula References Cell

Feb 23, 2008

I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url.

i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6

this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads

I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire

View 9 Replies View Related

Replace The Values

Feb 28, 2009

I have a workbook with two worksheets. The first is called Input and in cells C3 a product code is entered and in cell D7 the value and F10 the description.

The second sheet called History has a range of Product codes in cells AA5:CD5, AA4:CD4 holds values and AA8:CD8 descriptions.

Now for the intersting part, I need a macro to run that looks at the value in cell C3 of the input sheet and if a match can be found in cells AA5:CD5 of the History sheet then the value from D7 would replace the value in AA4:CD4 and F10 would replace the value in AA8:CD8.

Example if cell C3 has RJ12345 as the product, D7 reads £15.10 and D7 reads towel.

The code RJ12345 is foound in cell BC5 then cell BC4 would be replaced with the value £15.10 and BC6 would have the worf towel entered.

View 9 Replies View Related

Replace All In Column Except Certain Values?

Aug 23, 2013

One of the columns on my worksheet has a list of employees. I need to replace all the names with a blank (or delete) except for those employees on my team (7 employees). VBA code to Replace/Delete all EXCEPT (a,b,c,d,e,f).......?

View 2 Replies View Related

How Do I Replace Numeric Values Only

Jan 20, 2009

When using the replace rule in Excel, you can use "?" to represent a standard variable, e.g. if you had the text "Bottle 100g", and you set the replace rule as "?g", it would delete the "g" and the four characters before it, leaving you with "Bottle".

However, if I have lots of replace rules to make in a file, and I only want to eliminate the numerical values before a letter, how do I go about doing this?

For example, if I had the text "Dog food 10g", and I made a replace rule "??g", it would leave me with " food ". What I would really need, is a way of taking away the numeric values before the "g", which would give me "Dog food".

Therefore, is there a unique identifier for numeric and/or text values?

View 10 Replies View Related

Replace Values Greater Than 200?

Nov 4, 2010

I want to replace all numbers in the worksheet which are greater than 200 with 200. I mean all numbers greater than 200 should be set to 200. values are not all in the same column or same row, they are spread throughout the worksheet. I tried using the Find and Replace, but it can replace only a specified value with only another specified value. I want to use some kind of a filter which will choose all numbers greater than 200, and make them 200.

View 8 Replies View Related

Replace W/ Multiple Values

Jan 8, 2009

I am needing to do a find and replace on multiple values in an excel worksheet. For example in column d when you find ABC123456 replace with XYZ654321. But there are several hundred in a sequence that need to be replace w/ a different sequence.

Is there something I can do in vb that would help me do this?
For example the argument is:

FIND=XYZ000001
REPL=ABC000123
SFOR=5
OTHR=0

View 10 Replies View Related

Find/Replace Values With X

Jun 8, 2009

I've got a piece of code I'm stuck on. Basically, I have a range of data and most cells are empty. However, I want to replace all the nonblank cells with an X. I can't quite figure out how to have the code replace something non-specific...

View 3 Replies View Related

How To Replace Cell Values

Mar 21, 2014

I have an empty cell that a user can enter in a value if they choose. What I would like to do is override their value with a formula based on the selection from a data validation list. The part with is how to override the cell contents.

View 6 Replies View Related

Replace Doesnt Look In Values

Aug 8, 2006

I need to replace format for multiple cells. Most of them have formulas and shows values. But Replace wont let me choose to look in values as you can see in the picture:

I'm completely lost. I just did replace with "Look in: values" option couple of hours ago. And now this... Or am I going crazy?

View 3 Replies View Related

Replace Values With Id Numbers

Aug 7, 2008

I have two columns that contain IDs and Values for all our categories in a business directory:

i.e.
1 cars
2 home
3 boat
etc... (121 total)

I need to use the above two columns as a reference and use them to replace the value in the directory that is text (col 2) with the ID (col 1). I have a few thousand entries and need to find a way to quickly replace the text category field with the numerical id from my parent table.

View 7 Replies View Related

Criteria / Formula To Automatically Replace Formula With Value

Oct 22, 2013

I'm looking for a formula or criteria within a formula to prevent a cell from recalculating or just causing it to revert to a value if another cell becomes populated. Something like =IF(NOT(ISBLANK(xx)),STOPCALC(xx),...

I'm trying to determine some weighted week to week projections and I want to be able to determine the accuracy of the projections without them being influenced by the actuals once they show up (the projections use historical actuals and related data which are all in the same range where the futures will come in). Also hoping for a shortcut to do what I described without having to manually go in and stop the (thousands of) cells from recalculating since the actuals for a given periodicity arrive staggered and not at one specific time.

View 2 Replies View Related

Find And Replace COLOR Of Values?

Jul 27, 2013

Is there a way to find specific values and change their colors all at once in xls?

For example, if it looked like:

1 4 5 3 7
4 2 6 3 1
5 9 2 6 4

And if I tell it to change every 4 to red, it'd be like:

1 4 5 3 7
4 2 6 3 1
5 9 2 6 4

Or changing the cells' color would be ok, too.

View 1 Replies View Related

Replace All Sheet Values With Their RANK

Jul 16, 2014

Attached is an excel file that contains all possible values in the first column (9238 of them) and a comma separated list of a subset of those values in the second column. I need to find a way to replace the smallest value with 1, the next smallest value with 2, ... , and the largest value with 9238 in every instance. In other words, I need their rank value.

OhioBlockGroup.xlsm‎

View 3 Replies View Related

Replace Values Between Two Keywords In A Column?

Jan 31, 2014

I have data in which there are important keywords within a row (e.g.,# Message: FacesSc0_1) that are 'boundary markers' for data that is relevant to me. What I want to do is to select the data in the column between two such keywords, replace them all with the same specific value, and delete the other rows.

For example a column may look like this:

534.88
534.78
# Message: FacesSc0_1
534.78
534.55
534.59
# Message: FacesSc0_2
534.55
534.55
# Message: FacesSc0_3
534.78
534.55
534.59
# Message: FacesSc0_4

I would like to replace the values in the cells between "# Message: FacesSc0_1" and "# Message: FacesSc0_1" with the value 1, delete the values between "# Message: FacesSc0_2" and "# Message: FacesSc0_3", and replace the values in the cells between "# Message: FacesSc0_3" and "# Message: FacesSc0_4" with value 2. The resulting column would be reduced to this:

# Message: FacesSc0_1
1
1
1
# Message: FacesSc0_2
# Message: FacesSc0_3
2
2
2
# Message: FacesSc0_4

View 4 Replies View Related

Replace Values Between Specific Characters

Nov 29, 2009

I have been trying to do the following but have not been successful. thanks in advance for helping!

- i have lots of text strings on one worksheet

- some text strings have <randomstring>, some even have multiple instances of <randomstring>, but others don't have a <randomstring> at all

- what i want is remove anything within "<" and ">", including the operators "<" and ">"

i have done this so far, with simple text functions, have each of the following lines in separate columns (text string column is "C"): .....

View 10 Replies View Related

Using List To Replace Cell Values

Feb 2, 2013

I want to replace the values in column A if a unique value is identified on a particular row. Here's some sample data from Column A

A2= 01/16 Petsmart Inc 1372 Miami FL Card 7777
A3= 02/13 Sir Nicks Brodhead WI Card 7777
A4= 01/05 Canal Center Walk C37436 Orlando Fl Card 7777
A5= 01/24 Shell Oil 5744417710 Atlanta GA Card 7777
A6= 8/12 05 Canal Center Walk C37436 Orlando Fl Card 7777
A7=09/01 05 Canal Center Walk C37436 Orlando Fl Card 7777
A8=12/21 Top Deck Bridges C37436 Denver CO Card 7777

Here's my list in cells D2:D6

Canal Center Walk
PetSmart
Sir Nicks
Top Deck Cafe

I created a list of values in Column D then created the below formula in column B and replicated it down.

=IFERROR(INDEX(D$2:D$6,MATCH(TRUE,SEARCH(D$2:D$6,A2)=1)),A2)

What I would like the formula to do is look at the list in column D. If a particular row shows a match for that text string it will populate that value in column B, but delete everything preceding the text value and after.

Ex: A2 = 01/16 Petsmart Inc 1372 Miami FL Card 7777
B2 should = Petsmart

View 2 Replies View Related

Replace Different Values Within Non-Contiguous Ranges

May 8, 2008

I had some code working fine in office 2003:

Sub Replacing()
Dim rRange As Range
Dim lArea As Long
Dim Co As Byte
Dim NaCo As Byte
NaCo = 99
Set rRange = Range("B:C,E:F,H:I")
With rRange
For lArea = 1 To .Areas.Count
With .Areas(lArea)
Co = Choose(lArea, 1, 2, 3)
.Replace What:=Co, Replacement:=NaCo, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
Next lArea
End With
End Sub

the problem is that it's not working for Office 2000

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved