Find And Replace Matching Cells Across Multiple Sheets
Dec 11, 2013
Following Excel task I am trying to complete:
I have an Excel file with multiple sheets and I want to find and replace matching cell data on the same row across all of the sheets. For example, I have two columns, Column A and Column C and 10 sheets. I want to only replace the content in Column A if text matches both Column A and Column C on the same row. So, I want to be able to search for the following data across all sheets:
Column A = "car"
Column C = "yellow"
If both "car" and "yellow" are found in Column A and Column C on the same row, then replace "car" in Column A with "truck".
Is there a way to do this automatically as I have few hundred to find and replace?
View 5 Replies
ADVERTISEMENT
Jul 6, 2007
I have a list of cell entries that I want to find and replace with different text or a number.
My code below is 4 of them.
I am trying to do the replace over 3 different sheets at the same time but I am only changing the 1st sheet with my efforts.
Sub Find_And_Replace()
Sheets( Array("Resolution", "Response", "Open")).Select
Sheets("Resolution").Activate
Cells.Replace What:="1 Widespread*", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="2 Critical*", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="3 Non*", Replacement:="3", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(Array("Resolution", "Response", "Open")).Select
Cells.Replace What:="4 Require*", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
View 8 Replies
View Related
May 27, 2014
I have an excel workbook with 60 sheets (each contain data in the same categories and in the same column locations, just different information on each sheet). What would the VBA code look like if I wanted to manually enter the find and replace values and perform the function (find and replace) across multiple sheets in the workbook?
View 5 Replies
View Related
Mar 13, 2014
So I have a list of organizations in Column A, with multiple names (anywhere from 0 to 50 names) for each org. in Column B.
Org
Names
Company A
Brown, Jones, Smith, West
[Code]...
I want to do a find and replace for all of Column B, where all the names are replaced with their respective color values. If possible, I'd like this to all happen with the individual cells (so for example, B3 might go from "Brown, Jones, Smith" to "Red, Red, Blue").
If that's not possible, I could divide all the names into individual cells and then find and replace.
View 3 Replies
View Related
Jan 30, 2008
I have in sheet1:
Item QTY
A 1
B 10
C 0
In sheet2 I have
Item QTY
B
View 6 Replies
View Related
Jan 10, 2007
I am attempting to run a macro that will do a find and replace on a protected sheet but this is not working. I am using the following code to Protect all Sheets, which I found on this site when workbook opens using the Workbook Open Event and uses UserInterFaceOnly :
Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="111", _
UserInterFaceOnly:=True
Next wSheet
End Sub
And then a find and replace similar to this:
Sub FindAndReplace()
Selection.Replace What:="w", Replacement:="a", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
View 6 Replies
View Related
Nov 16, 2011
I have one workbook in that 6 sheets have.
Here I want match the total one sheet to another but each sheet there is no fixed ranges so that I have confused.
How to find out Each sheet total and match each other.
View 3 Replies
View Related
Jun 14, 2008
I'm working with 2 sheets. Both have the same headings, A1 is CustomerCode, C1 is JAN, D1 is FEB and so on. The CustomerCode column doesn't match exactly between the 2 sheets, because some of the customers from Sheet1 aren't listed on Sheet2. I need a formula to put on Sheet 3 that will Sum the values in JAN, FEB, etc from the first 2 sheets for matching customers, and also bring over the totals from the customers on Sheet 1 that don't have a match on Sheet 2.
I have attached an example of how the finished product would look in my case. But also, here's this:
Sheet1 Charges
CustomerCode JAN FEB
ABC 30 25
AEF 20 15
BBB 50 30
CED 15 20
Sheet2 Misc Charges
CustomerCode JANFEB
ABC 510
BBB 3 8
CED 2 4
Need Sheet3 To look Like this:
Sheet3 Total Charges
CustomerCode JANFEB
ABC 3535
AEF 2015
BBB 5338
CED 1724
New customers are added often, so I have a SQL Query that I can put in column A of sheet3 that will pull the same CustomerCode list as sheet1, so getting the names to sheet3 shouldn't be an issue.
View 3 Replies
View Related
Aug 7, 2013
I have a workbook with 4 sheets one is called final and the other 3 are data1, data2, and data3. The data sheets have the actual data I am needing to pull from. Each has two rows of data: data1 has employee number in column A and employee code in column B. Data2 has employee code in column A and employee name in column B. Data3 has employee name in column A and employee email in column B. Now I need to pull the info form all three sheets into the sheet names final. So the final sheet needs to contain 4 columns for employee number, employee code, employee name, and employee email. The kicker is the data sheets don't line up with each other within each sheet it does but the first one in data1 is not the first in data3 and so on.
View 2 Replies
View Related
Sep 10, 2009
I need to recode 30+ files the have the old account numbers and I need to replace them with new codes ie
Old New
123456 10_1240
256789 12_1130
789123 15_1122
I have an excel spreadsheet which contains some 800 accounts in the format above showing old account number in column A and the new account number in column B. How would I write the VBA code to search all files in a specified folder search all rows in column E and find/replace using my spreadsheet containing the mapping details as above?
or in the very least just be able to run a macro to replace all the old codes in an individual spreadsheet.
View 13 Replies
View Related
Feb 15, 2008
I want to have a macro in excel where it will do a simple find and replace, except I want it to search for more than one word and replace it with one eg, I want it to look for any month and replace it with january.
View 4 Replies
View Related
Jul 10, 2014
is it possible to provide a formula to match as per the uploaded file. Data in Sheet one columns A and B, compared to what is in Sheet two column A I have a lot of data, and only need a Y for yes, and N for No as a result.
View 3 Replies
View Related
Apr 16, 2014
I would like to search for numbers and replace them with text (multiple entries).
I tried doing this with vlookup, but for some reasons i dont get the good values.
View 5 Replies
View Related
Nov 29, 2012
I have two worksheets in same workbook.
Sheet1 on columnA contains my data.
Sheet2 on columnA contains the entries I am going to find on Sheet1 colA
columnB contains the entries I am going to replace with
I am currently working with these code but seems I cant make it work:
[Code] .........
View 5 Replies
View Related
Oct 28, 2011
I have been put together some simple coding to find and replace a value (1) that i have obtained from a pivot table. My aim was to replace any "1" with the column header. The reason for this is that the cell(s) will be used in a VLookup at a late stage.
My problem is that I have the code to work for a single cell, but I want it to work for multiple columns so I don not have to create multiple macros.
I need to go from Column C to AW. I have a total row at the end - hence why I am using 'Step - 1'.
Code:
Sub FindReplace()
Dim i, lRow
Application.Calculation = xlCalculationManual
[Code]....
View 7 Replies
View Related
Nov 9, 2012
I currently have two lists I am working with. One is a big mix of many different names, names which all need to be replaced.
I have another list which has all of these original names included in one column and the names which should replace those names in the column next to it.
Is there a way to go down this list and have excel find and replace every name in the sheet with its corresponding name without having to do it manually over and over?
View 1 Replies
View Related
Oct 27, 2009
say that i have alist of data in "A2:A", which are a string file (text) and i want to rename each data to a certain name that defined in range "B1:B2" (String file too).
For Example: for range
C2 = S16e
C3 = 16/E
C4 = 18W
C5 = Site18 w
& The target name is to be:
B1=16E
B2=18W
For case above, i need to replace C2&C3 in to 16E, while C4&C5 to be 18W.
View 8 Replies
View Related
Apr 1, 2012
Is there a way to do a "find & replace" across multiple pages in an excel workbook?
I am trying to update 1 particular data-set, and need to do it across all my pages...just spent 30 minutes doing find & replace on each page.
View 4 Replies
View Related
Oct 17, 2013
I looked everywhere but did not find any tools to complete the task I want, "convert" FW ruleset that look likes:
access-list outside_access_in extended permit tcp 192.168.0.10 255.255.255.254 host 172.23.34.90 eq 3978
access-list outside_access_in extended permit tcp 192.168.0.10 255.255.255.254 host 172.23.34.89 eq https
access-list outside_access_in extended permit tcp 192.168.0.10 255.255.255.254 host 172.23.34.89 eq 3978
I do want to replace all the IPs with a new values like:Original
New
172.23.34.89
172.1.1.1
172.23.34.90
172.1.1.25
172.23.34.43
172.5.2.1
192.168.0.10
192.168.0.10
192.168.0.108
192.168.0.95
192.168.0.130
192.168.2.21
View 1 Replies
View Related
Jun 7, 2009
I have the following dataset:
[Date] [Category] [Currency] [ExchangeRate] [.....], etc.
1-3-09 A USD 0,8
1-6-09 A EUR 1
1-7-09 A USD 0,7
1-8-09 B USD 0,9
1-9-09 B USD <formula>
I'd like to have the value of <formula> looked up in older records. Currency and category should match and it should pick the exchange rate with the maximum date.
Which formula and what syntax should I use to have this done?
I use Excel 2007.
View 10 Replies
View Related
Sep 9, 2013
i have a worksheet that is about 100rows x 100columns.
i would like to create some code to find a certain record. for example..
a1 a2 a3 a4
the record might be " tony tony alex tony"
basically, this group of cells could be any where in the group and i need some code to search the worksheet and then outline it. i just want to be able to find a certain block of cells and outline it.
View 2 Replies
View Related
Dec 24, 2013
I have a set of sheets on a workbook that contain addresses, which are all combined on the first sheet (as you can see in the attached file). I would like to use a formula to compare the addresses on the Main sheet with the other sheets, then return in the D column of Main a colored cell indicating on which sheet the address was found on; different colors for different sheets. I tried to figure out some conditional formatting, but to no avail. Also, on occasion I have spreadsheets with 5 or 6 sheets containing these mixed addresses. Is there a solution that won't be limited to just a few sheets for comparison?
Excel Help.xlsx
View 8 Replies
View Related
Oct 9, 2013
For certain cells in this spreadsheet Excel's find and replace is not working:
For example, if I type the species "carex_filamentosa" it will say "...cannot find the data you're searching for"
View 2 Replies
View Related
Mar 17, 2014
code to find/replace the letters: "A" with "Active", "P" with "Contract", and "C" with "Settled sale", all in column "Q".
View 2 Replies
View Related
Nov 14, 2006
I want to read information from the "Mapping" sheet, then find information in the "GLEX" sheet that correspond to the read information, then copy the information that was found in "GLEX" to the "Calc" sheet and add it all together and display the sum in a particular cell in the "Report" sheet.
The "Mapping" sheet is a mapping of information to show what makes up a particular total and where it must be entered in the "Report" sheet. The "GLEX" sheet is the output from a financial system and the source of the information that must be "reformatted" to the "Report" sheet. The information is only an extract as the whole totals to around 10MB. I have also deleted the "Report" sheet as it is fairly big on it's own, but the principal will be the same, I think. (If i should upload the "Report" sheet,)
Step 1:
"Column A" and "Column B" of "Mapping" are the starting points. The macro must read the values in "Mapping" "Column A" and "Column B" and remember them. Then it must find the row in "GLEX" "Column A" where the information from "Mapping" "Column A" matches and where the information from "Mapping" "Column B" matches with the information from "GLEX" "Column E".
Step 2:......................
View 5 Replies
View Related
Apr 11, 2014
I have a excel which contains 5 columns in which 5th column data cells has to be replaced with another set of data which have relationship with other 4 columns data.
View 3 Replies
View Related
Jan 4, 2014
I am looking to Find and Replace (via ctr-F) but I need to replace it only within a range of selected cells. Each time I try to do this it replaces everything even though I have tried selecting each cell and replacing only that. Is there a way to select a certain amount of cells and replace?
View 2 Replies
View Related
May 8, 2008
I would like to be able to replace all cell values in a range of 20c by 20r (i.e. 400 cells). In all cases the condition would be the same (find all cells with a value greater than than zero), but then replace with different values.
e.g. Cells with value >0 in range CX119:DQ138 replace with "NT", then cells with value of >0 in range DR119:EK138 replace with "NU"
I thought you could do it with find and replace by just selecting that range of cells but can't see how to set the conditional >0 bit.
View 9 Replies
View Related
Feb 15, 2008
On the worksheet, I need to find wherever the cell value begins '2 X 4 ...' in column L, and then replace the cell value in the same row, in column K, with '113010'. In my attempts, my code just changes the first occurrence, not all occurrences.
I want to use VBA to do this.
Here's my code so far:
Sub Update_Product_IDs()
Application. ScreenUpdating = False
Dim i As Integer, iCount As Integer
Dim numberOfPMDs As Integer
Dim PMD_Name As Integer
Dim worksheetName As String
Dim worksheets_in_file As Integer
worksheets_in_file = Worksheets.Count
worksheetName = Worksheets(2).Name
numberOfPMDs = worksheets_in_file - 1
View 5 Replies
View Related
Sep 13, 2010
I need a macro that will look for a specific text string in the tab names of the workbook and replace it with a new specified text string (leaving the rest of the existing tab names). In other words, a simple find/replace but applied to all tab names in the workbook rather than cells. Ideally, I'd like it to pop up something and ask for the text to find and the text to replace it with, so I don't have to edit the macro itself each time I want to use it, but editing the macro each time is fine. Either way will be wonderful.
View 9 Replies
View Related
