=IF(ISNA(INDEX($D$3:$D$100,MATCH(G19,IF($C$3:$C$100="DF",$A$3:$A$100),0)),"",(INDEX($D$3:$D$100,MATC H(G19,IF($C$3:$C$100="DF",$A$3:$A$100),0)))
what i'm trying to do is to replace #N/A with blank. Taken the above from vlookup examples but does not seems to work.
Need assistance with the code for catching errors when using the find / replace function in excel? In particular, I am trying to write code to break to an error message when the value or string searched for isn't found in the find / replace. At the minute I have just copied the standard code using a macro and all this does is return a message box saying X entries replaced.
View 3 Replies View RelatedI'm wanting to use Excel's built in replace function to replace ANY date with "Call:"
I'm not going to go into details about why, but I cannot use a code, as I only want to change them at specific times.
The dates are currently formatted as 12/09/2009. So I need to change the 12/09/2009 and any other date there may be to "call:" without having to go through every possible date.
I am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
=SUM(VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("FAID",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("BPCM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMD",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE))
Where:
F13 = Employee number
Column C on the pivot 156 workbook is where the employee number is based.
The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies View Relatedreplace row of first cells match
replace row if 1st column match
But that's only a very small part of my question.
1. I have an original list of IP addresses which contain many duplicates.
2. I used "The Duplicate Finder" Add-in and created a unique list of IP addresses from the original list.
3. I did proprietary search on the unique IP list to get an approximate geographic location of each IP address.
Now I want to match all the IP search information to all the duplicates in the original list.
It's difficult for me to craft an exact question without knowing the proper terminology, so I'll "illustrate" a few point below.
Original List of IP addresses in a single column (IP Address)
IP address
12.34.56.78
12.34.56.78
98.76.54.32
95.62.84.37
95.62.84.37
95.62.84.37
95.62.84.37
55.11.99.225
55.11.99.225
23.56.89.14
78.56.12.43
78.56.12.43
Unique list of IP address with additional info in 3 more columns (4 columns total)
IP Address Country State City
12.34.56.78 USA CA SomeName
98.76.54.32 USA NY AnotherName
95.62.84.37 GB England Ye OldeName
55.11.99.225 MX EL SmokyCity
23.56.89.14 USA WA RainyCity
78.56.12.43 USA FL ViceCity
What I want to do is if an IP address in the original list matches the IP address in the unique list, the original IP row is replaced with the matching IP row in the unique list.
Basically where the columns say 2011 or 2012 AND 1, 2, 3. I want to be able to have it index the number below based on the GL number on the left and both the year and period on the top. I think that you can do with using the sumproduct function with the binary, but the computer is a little dated and it takes a while to run those calculations.
2011
2011
2011
2012
2012
2012
[code].....
Question: I have two sheets named Sheet1 and Sheet2. Sheets looks like this:
Sheet1
A B
1 John Smith
2 Jane Doe
3 Joe Smo
4 Jackie Chan
Sheet2
A
John Smith
Joe Smo
Joe Smo
Joe Smo
Jane Doe
Bruce Lee
Here is the logic: If any cell in Sheet2 Column A = Any cell in Sheet1 Column B, change sheet2 column A cell value to sheet1 column A value (id).
Another interesting dilemma to solve. Using this formula:
View 2 Replies View RelatedIs it possible to take a list of numbers then match those numbers to another list of numbers as a whole? I'm trying to make changes to my code and take some of the advice from previous threads, but I can't visualize it. I'm trying to get away from matching each list of numbers to each other line by line. I have an existing sheet (Master) that has several col's of data. Then I have another worksheet that has updated info that I need combined into the master. What I've done so far is added the information to the bottom of my existing data and sorted.
Now I have setup an advanced filter to display the dupes. Here is where it gets tricky..I can't just delete dupes b/c there are cols to the right that have history that cannot be deleted. So I need to copy ranges A to J of the new data and replace the old data within that range.
I have two workbooks.masterdata.xls (M: estmasterdata.xls)emp1.xls (M: estempFilesemp1.xls)
Workbook emp1.xls has one worksheet- same name as workbook(emp1). In first (masterdata.xls) workbook I also have worksheet name (emp1). What I am trying to do is transfer all data from (emp1.xls, worksheet "emp1") to worksheet "emp1", which is in masterdata.xls workbook.
let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in
Match: Lookup value = $E3
Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1
match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4
Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452
match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
I'm working with a word table pasted into excel. The dashes in the word table paste into Excel (2003) as small boxes. I can use the find and replace function in Excel and in the Excel environment the replace function works. I recorded the replace function so I could develop the action in VBA. The recoded action does not work. The data I've pasted in below looks fine, however when pasted into Excel all of the "-" paste in as small boxes. When I used the chr function the chr code equals 63 which is "?". I used chr(63) in the replace function. This causes the VBA replace function to replace all characters in the file with dashes. I've tried several other codes (127,129,141,143,144,157) (none worked) based on the following website:
[url]
Cells.Replace What:=Chr(63), Replacement:="-" 'finds and replaces the - that converted to boxes
I can paste in the data using paste special "unicode text" but I loose the strike through formating which I need to identify obsolete data.
I was trying to help another user and came across this problem.
In cell A1 I have "abcd"
In cell A2 I have =REPLACE(A1,4,1,"e") resulting in "abce"
I tried to use = REPLACE(A1,RIGHT(A1,1),1,"e") but get #VALUE!
I am wanting to use RIGHT because the number of characters is unknown.
I've always used event driven functions - ie. user clicks a button and my function gets called.
What I want to do now is replace a HUGE formula that is impossible to debug with a function. So the idea is, any time the user changes any cell, the function should be called and update a certain cell with a new value.
See my file. I want to calcuate a % needed (20%) to add to a another material. Should be easy but I can not figure it out.
View 3 Replies View RelatedI just want to replace the offset with find function. I have attached the sample file with the code. Just need a little change.
View 5 Replies View RelatedI am working with a lot of data and have been making a table of statistical analysis for several rock types. I have it worked out to where i can filter the data and with the use of a bunch of IF functions, below, and it keeps everything the way I want.
{=MAX(IF($H$2:$H$22="sedimentary rocks",$W$2:$W$22))}
The problem is I have to introduce new rock types, but if I just copy the formulas over i then have to go through hundreds of cells and switch sedimentary rocks to volcanic or whatever new class i have.
How this can be done faster. Maybe some way to say "find "text" within this range and replace" i can do it for actual values but when its embedded in a function im not sure.
I m using SUBSTITUTE function to replace commas with fullstop so I can multiply the end result with a number. But when I try to multiple for example B2 (0.1831) with 5, i get the VALUE! error.
Value Real Value (after substitute function)
0,1831 0.1831
23,3333 23.3333
12,5199 12.5199
5,5000 5.5000
20,5999 20.5999
24,4671 24.4671
200,0000200.0000
2,5386 2.5386
0,4000 0.4000
1,5019 1.5019
how I can resolve this so I can use the real values for computations (eg Real value *5), without having the VALUE! error message.
Plan numbers are contained in Row 1 (1518, etc). Options are listed in Column A (L101, L102, etc). I want to add the costs of each option, resulting in the total and each option code only listed once, with that total, below each plan. Example:.......
View 2 Replies View RelatedI am working on a spreadsheet which contains a number of reference data sheets (named “Reference data 2009”, “Reference data 2010” etc). As their names suggest, these sheets contain reference data applicable to the particular year. This reference data is used to perform various calculations in a “Calculations” sheet.
On the “Calculations” sheet, the user specifies the year for which they wish to perform calculations. At present, I am using the volatile INDIRECT function to perform various HLOOKUP calculations along the following lines:
=IF(D15>=HLOOKUP('Detailed net pay calculations'!D16, INDIRECT("'Reference data" & 'Detailed method'!$C$2 & 'Detailed net pay calculations'!D1 & "'!$B$43:$Y$52"), 4, FALSE), “Do something”, “Do something else”)
The reason I'm using the INDIRECT function is to identify the sheet with the appropriate year (hence "Reference data"&'Detailed method'!$C$2&'Detailed net pay calculations'!D1 which could be interpreted by Excel as "'Reference data 2009NEW" or "'Reference data 2010", depending on whether there's text in cell D1).
My query
The function I'm using is working perfectly fine but I am wondering if it is possible to replace the INDIRECT function (in red) with INDEX or another non-volatile function in order to reduce the performance impact (I have a fairly large number of these types of functions).
Here is the situation:
- Large block of text in one cell (1000+ words).
- I use the Find & Replace function (from the top menu) to find a specific word that may appear several times within the cell.
- Excel "highlights" the cell... but not the word specifically so I have to read all the text to find that word throughout the cell.
I'm receiving this message when I attempt to use the Find, Replace function. Formula is too long". I have a column of cells containing text only. ( about 2-3 paragraphs worth) I'm trying to replace a name with another name, which works fine where the cell contains a single or few sentences, but fails to replace when the cell contains too much information.
View 8 Replies View RelatedI am trying to create a function that will evaluate a cell, lets call it B10, and depending on this numeric entry input data in cell ranges B14,B22.
What I am trying to do is create a list of locations for a packing slip that when I enter the corresponding location number (i.e. 200) that it will populate the shipping address in B14,B22.
Is this possible?
Any way to use a find and replace function involving a cell reference. I have a spreadsheet with ton of junky text burying information that I want. Here is a simplified example
A B
Book 345353hg dgdgsdgfd fff Book belongs to Jim
Sneaker fdg4 Sneaker is worn by Jan
Gum dfg s d e te4345Gum tastes great
Cake jklsjflsjfjikCake smells delicious
The pattern is that the A1 text appears in the B2 text. What I want is
Find *A1
Replace with blank
Find *A2
Replace with blank
and etc.
So that in the end, I get this:
B2
belongs to Jim
is worn by Jan
tastes great
smells delicious
I've looked at the functions of find, replace, substitute, left, and right and I can't seem to find the right one to do what I need.
I am using a macro to find a value in a cell and if found, replace the value in the cell to the left. Thr macro works fine - unless the value I am searching for does not exist. The answer is ( Ithink) an IF statement, but I am having problems with the errors, when I try the IF.
The macro below blows up at the ActiveCell.Offset(0, -1) = "109073X line with an error "Run-time error "1004" - Application-defines or object-defined error.
I've tried a number of things I've seen from this board but have not found a way past the error.
If the value is found, the macro moves to the else statment and stops at the ActiveCell statement with the above error. If the value is not found, then the macro goto NotFound.
Sub Macro2()
'
'Range("A1").Select 'Start from the home cell
Dim ValueFound As Object
Set ValueFound = Cells.Find(What:="Cirrus Reversals/CREDITS")
If ValueFound Is Nothing ThenGoTo NotFoundElseActiveCell.Offset(0, -1) = "109073X"'ActiveCell.FormulaR1C1 = "109073X"End If
NotFound:
End Sub
I have special character that I removed with =CLEAN formula.
It was only one character which represents carriage return. It looks like one little square with question mark inside.
After I applied =CLEAN formula it disappeared, but now I don't have space between these two words.
How could I replace this special character with space?
I have two sheets. One is named sheet1 where all my data is and sheet2 where all my values are. I want to do a find a replace on column N (sheet1) using the data in sheet2. Column A on Sheet2 has all the values that are found in column N and column B on Sheet2 has what the data should change to.
So for example:
Sheet1 says the following on column N:
cat
dog
lion
bear
Sheet2 says:
Column A
A1: Cat
A2: Dog
A3: Bear
Column B
B1: 2
B2: 8
B3:15
I want the values on column N to be replaced with 2, 8, 15 and so forth. I use excel 2010.
I'm getting a Sub or Function not defined error on the code below. It highlights the Replace Function as the problem, have also tried the substitute function too with the same result.
View 11 Replies View RelatedI'm looking to loop a comparison code. I'm using dynamic referencing (using x and y) to find maximum values for specific time intervals. The code works on a cell to cell basis, meaning if I input the formula and change the cell referencing manually then the equation will give the desired results. However when I attempted to create a VBA code to speed up the process I kept getting a 1004 Unable to set FormulaArray Property of the Range Class error, I later figured out that the Formula Array function is limited to a certain number of characters so I split up my function into 3 different string formulas. I still get the same error.
[Code] ............