Find In One Column Replace In Another Column
I want to find an instance of a word in one column......and replace whatever is in the adjacent column with a word.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Find & Replace With Column Data
I was wondering if it is possible to perform a find and replace which would replace data from another column. We have a lot of data fields of our inventory that we exported which we are trying to condense for another program. Example: I have a description column (AV) that has [[Manufacturer]] within the description. We want to find [[Manufacturer]] and replace with our data column (DD). This is the data that has our manufacturers name in it. We have several find and replaces to do within the description column besides the above field.
View Replies!
View Related
Find Numeric Codes In A Column And Replace In Vba
I am using the following vba code to find numeric codes in a column and replace them with the same code along with descriptive text: Range("Q:Q").Select Selection.Replace What:="11", Replacement:="11 - GSA" Selection.Replace What:="10", Replacement:="10 - NYS OGS" Selection.Replace What:="1", Replacement:=" 1 - Dist Sale" Selection.Replace What:="2", Replacement:=" 2 - Direct Sale" Selection.Replace What:="3", Replacement:=" 3 - No Sale: Warranty Exchange" The problem as you might already see is that when the replacement of "1" happens, Excel will replace the 1 in the 10 and 11. Of course I want to only replace the number 1. Is there a better way to code this operation?
View Replies!
View Related
Find Rows W/same Value In Column A And B And Offset Column C To Next Available Column
I need to combine rows that have the same value in column a and column b to the same row by offsetting column c to the next available column. For example, I would like the first 6 rows of the provided sample to appear like this. 0014B22<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities 0014B23<@28>Coursey<@28>Ogles<@28>Shelocta Sometimes the values are the same in column c, sometimes they are different. I do not want to delete duplicate rows where they are the same. Sometimes there are 2 rows that have the same values in column a and column b, other times there may be 3 or even 4 rows with the same values in column a and column b. Regardless, I would like the values in column c combined on the same row in the next available column. It would be nice if the duplicate column a and column b rows (with a null column c cell) were then removed, but I could do that in the next step. 0014B22<@44>Soil Properties and Qualities 0014B22<@44>Soil Properties and Qualities 0014B22<@44>Soil Properties and Qualities 0014B23<@28>Coursey 0014B23<@28>Ogles 0014B23<@28>Shelocta 0014B24<@33><i>Available water capacity:<p> High (about 11.5 inches) 0014B24<@33><i>Available water capacity:<p> Very low (about 2.9 inches) 0014B24<@33><i>Available water capacity:<p> High (about 9.0 inches) 0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr) 0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> High (about 1.98 in/hr) 0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr) 0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches) 0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches) 0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches) 0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches 0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches..............
View Replies!
View Related
Find The Column Name Purple & Delete The Whole Column If There Are No Blank Cells
I'd like a macro that does 3 things.. 1. Find the last row (cell) of data in the "Customer Number" column. This search should be by the name "Customer Number" rather than by column letter because the column that "Customer Number" will be in can change. 2. Find the column named "Purple" (also by name for same reason) 3. If the "Purple" column has no blank cells in those same number of rows as the "Customer Number" column, delete the whole "Purple" column.
View Replies!
View Related
Multiply Every Nth Cell By Another Column Whith Merged Cells, THEN Find Column Total
Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals". In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole. If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.
View Replies!
View Related
Trying To Find Max Date In 1 Column For Selected Text In Another Column
I have 2 columns: Locations (Column A) & Dates (Column B). I want to find the most recent date for a selected Location (stored in $G$1). currently I have in C2: =If($A2=$G$1,$B2,"") copied down the column to the end of data (currently C153). Then in C1: =Max(C$2:C$153). I also have in D2: =If($C2<$C$1,$C2,"") copied down to D153, and in D1: =Max(D$2:D$153) to find the 2nd most recent date. Is there some way to condense this into just 2 cells? See attached file for example (note the columns are currently sorted by date, but that is not always the case).
View Replies!
View Related
Find Duplicates In Column A And Calculate Difference Between Times In Column B
I love this forum, and am usually able to find the help I need without bothering anyone However this one has me stumped and I wonder if anyone can help. It feels like it should be a fairly simple solution, but they can often be the ones that are most eluding LOL! I have two columns; in column A are incoming telephone numbers and in column B are the date and time the calls were made. (I've put a few hashes in column A just to maintain confidentiality of the numbers, but in reality the cell is formatted as text in order to maintain the leading zero, and entries will follow the format 01234567890) A sample would look like this: 0##6270####01-Mar-2009 00:01:440##6271####01-Mar-2009 00:03:020##6271####01-Mar-2009 00:03:040##6272####01-Mar-2009 00:16:330##6273####01-Mar-2009 00:30:490##6274####01-Mar-2009 00:55:470##6274####01-Mar-2009 01:06:170##6274####01-Mar-2009 01:07:420##6275####01-Mar-2009 01:08:360##6275####01-Mar-2009 01:11:410##6276####01-Mar-2009 01:13:45 Some numbers only call in once, I need to identify them as only called once. Some numbers call twice, if they do I need to be able to show time it took between call 1 and call 2. Some numbers call more than twice. For each successive call I need to be able to show the time since the previous call. In my mind, the results table would need to look something like this: NumberTime of callTime between 1st and 2nd call Time between 2nd and 3rd call Time between 3rd and 4th call 0##6270####01-Mar-2009 00:01:44Only called once0##6271####01-Mar-2009 00:03:0200:00:020##6272####01-Mar-2009 00:16:33Only called once0##6273####01-Mar-2009 00:30:49Only called once0##6274####01-Mar-2009 00:55:4700:10:3000:01:250##6275####01-Mar-2009 01:08:3600:03:050##6276####01-Mar-2009 01:13:45Only called once
View Replies!
View Related
Macro To Find End Of Column And Paste Contents In Column 1
I have the file here i work with, basically the first column is a legend and the column to the right of it is a pointer column to help me find out where a legend is located in another file. So i was wondering if a macro could be made to basically find where the "legend column" A, C, E ect ends ( every other column is a legend column , one next to it is a pointer column ). and then combine the ends all of the columns contents and put them into 1 column. In the file with this question i have showed you what i start off with, i highlighed in yellow where each column legend ends, ( normally these are not highlighted and i find them manually ). In the 2nd tab i show what the end result should be. All the columns are now consolidated into 1 column. 1 after the other.
View Replies!
View Related
Find Value In Column & Return Cell On Same Row- Different Column
I have an excel sheet I am working on and in columns F1:F2000 I have an IF statment, I need to be able to add more "IF"s to it but I will exceed the 1024 char limit. Is there a way I can put this formula into VB as a function called DocumentType() and then in excel F1:F2000 =DocumentType()? Is it as simple as: Function DocumentType() If(...........) End Function
View Replies!
View Related
Find Data In 1 Column Transfer To 2nd Column
I have a spreadsheet (XL2003) that calculates some price data based on a specific algorithm. The program then determines which price data meets a certain criteria and if met, will place the value in a column ( Column C in the example WS attached.) If criteria not met the cell is returned empty. Under certain settings, this column could have values in all rows (C9 to C27 in the example) but is usually just a specific consecutive list as in the example. I have to transfer the calculated values to another spreadsheet so I want just these values to be initially transferred to a separate column (Col. H in the example WS.) from which they can easily be transferred later to the other spreadsheet. I need to figure a way for a macro to check in col. C (ie C9) and if there is a value in the cell, to transfer it to the next available cell in Col H (Starting with H9). The macro would then look at the next cell down in Col C (C10) and do the same routine until the last cell is reached. The transferred data would then end up in consecutive cells in Col. H. I have shown in the example attached how I would want the data to look after the macro is run. I figured this is some type of loop but could not see any examples in the forum that would do what I need.
View Replies!
View Related
Find Value In Column And Return Value From Adjacent Column
I have are two worksheets, "Sheet 1" which is a bill of lading form and "Sheet 2," in which columns A and B contain Part Numbers and Weights respectively. What I'm wanting to have happen is when a user inputs a part number into Sheet 1 (say in cell B6), it calls a UDF that looks up the part number on sheet two column A and returns the corresponding weight from column B (to say cell C6 on sheet 1).
View Replies!
View Related
Replace Column Items
I have a form that will open a workbook and then run a function to replace items in a column based on the column name. The problem is that it only replaces some of the items. Here is my click event that calls the function
View Replies!
View Related
Replace All Non Blanks In Column
I am trying to find a way to code to replace any cell that is not blank with the text "LOA", then after I replace all blanks with "Active" The Replace blanks is easy: Columns("K").Replace What:="", Replacement:="Active", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False but before this I dont really know how to quickly replace all non blank rows. Only way I know is to loop thru all cells in the column checking for not equal to "" then replacing, but the code above for replacing blanks is elegant, and only replaces in the used data section.
View Replies!
View Related
Find Column B At Anotherworkbook By Looking Column A
I am looking for a macro that will fill the column C by looking at column A at another workbook. For example: Full workbook: A(column that will look) B C blala blabla Blabla Workbook missing entries in C row: A B C blabla blabla Null So the workbook that missing entries in Column C,will look for it,by looking at at column A,wich is the same at full workbook.
View Replies!
View Related
Search And Replace Text In A Column
I want to replace all blank cells in column A with the word "BLANK" but my problem is that I need to tell Excel to stop searching for blank cells in column A once and only once column A is equal to the last data row in the column named "Product Number". I tried this macro but, naturally, it replaces all the empty cells in the WHOLE COLUMN. I will always have a header column in row 1. The data is below that (data will always start at row 2). Columns("A:A").Select Selection.Replace What:="", Replacement:="BLANK", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False How can I change this so that the find/replace macro searches all the way down equal to the last data row in the "Product Number" column? Below are 2 examples. Example 1 is pre macro and example 2 is post macro. ...............................
View Replies!
View Related
Replace Column With Certain Criteria Matches
4 columns a-d. now if i find either 1 of the 2 particular strings "hello" or "bye" in column A, I want to move column b into its place and leave the rest of the columns untouched Example: dog, circle, blue, 123 hello, frog, green,223 pig, triangle, red, 445 bye, elephant, grey, 322 into dog, circle, blue, 123 frog, blank, green, 223 pig, triangle, red, 445 elephant, blank, grey, 322
View Replies!
View Related
Replace Numerical Data Column A, 11K Rows! VBA
Need to sort my data in column A which contains a long list of numerical data. To do this manually using copy and paste would be easy, but not viable because of the large amount of data. The attached PDF file illustrates the data. I wish to replace each date of dirth entry in column A (one D.O.B. in each cell / row, expressed as YYYYMMDD) with the client identifier number (format 937XXXX) a couple of cells above. And at the same time delete the row containing the client reference (or its contents). The data in column A should retain the same relationship to the data in the other columns (B,C). it may require a short piece of VBA code.
View Replies!
View Related
Catching Errors When Using The Find / Replace Function / Replace All
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 Replies!
View Related
Find, Replace With And Then Replace Adjacent Cell
I am trying to create a macro where it finds a a certain word in a column for example C. What i want it to do is find anything that says FWD_EUR and then replace that cell (e.g C2) with CASH_EUR_FWD and after it has done that it replaces the adjacent cell (e.g. D2) with EUR_FWD. I then want this to do the same with FWD_USD to CASH_USD_FWD and adjacent cell to USD_FWD.
View Replies!
View Related
Find Value On Row & Find Last Used Column Before Found Value
Dim ColNo As Byte Dim LastCol As Byte ColNo = Application.WorksheetFunction.Match("Column find", Range("sheet1!A1:Z1"), 0) LastCol = Range("sheet1!b1").End(xlToRight).Column But the below code errors? Dim ColNo As Byte Dim LastCol As Byte ColNo = Application.WorksheetFunction.Match("Column find", Range("sheet1!A1:Z1"), 0) LastCol = Range(Cells(1, ColNo)).End(xlToRight).Column
View Replies!
View Related
Check One Column, Get Value From A Second Column And Display Sum In 3rd Column
I am tying to total all the sales for a given customer. This is what I have at present Customer Sales CustA 1000 CustB 500 CustA 1250 CustC 750 Elsewhere in the spreadsheet I have the following Customer Total Sales CustA CustB CustC As you can probably see I need to get the sum of each customers sales and display this under Total Sales. I thought about using VBA but am not really sure where to start. If anyone has any ideas how I could progress this I would be most grateful. I am using excel 2007
View Replies!
View Related
VBA To Autofill Formula Over Column Data Calculate And Replace Original Data
Id like to apply a formula, any formula to an entire column if it contains data, and incorporate the original data in the calculation and then replace the original data with the result. I don't want to have to create new columns. I'm using this to fix up database results; a common problem is dates in dot format e.g. 14.11.2008 All I have so far is an autofill formula that overwrites everything. Can someone help me with the rest? I'm using the SUBSTITUTE function to replace the dots '.' with slashes '/' Sub Create_formula_result() Dim Limit As Long Dim r As range Set r = range("A1") r.FormulaR1C1 = _ "=IF(RC[0]"""",(SUBSTITUTE(RC[0],""."",""/"")+0) ,"""")" Limit = ActiveSheet.UsedRange.Rows.Count r.AutoFill Destination:=range(r, Cells(Limit, r.Column)) End Sub
View Replies!
View Related
Find Last Used Column
I am trying to find the last used column in a row with the following LastCol = Range(OldCell.Address).SpecialCells(xlCellTypeLastCell).Column but this returns 70 whenI only have 35 columns being used?
View Replies!
View Related
Find Last Non Zero Value In Column
I have a template which calculates the balance of overtime hours by using the prior row plus and minus the current row. Is it possible to calculate the balance of overtime by using the next available record? In the attached sample, my overtime balance is wrong as the prior row is empty, is there a code to refer to the next available record?
View Replies!
View Related
Find Certain Value On A Column
I have a sheet where I have several dropdown lists on the column A. All of the dropdown lists are the same and they contain numbers from 1 to 100. Then on the same sheet I have second row's cells numbered from 1 to 100 (starting from b10 untill b109) Now I need a macro to put Letter "X" to a cell, which is located on the intersection of the currently activated row (so the same row where I selected the value from dropdown list) and the selected value from dropdown list located on the row 2. I need the macro to run right after I select the value drom the dropdown list.
View Replies!
View Related
Find Last Column
I'm familiar with finding the lastrow within a spreadsheet, how to find the last column? I've tried: Sub ColumnTable() Dim lastcol As Integer lastcol = Cells(5, Columns.Count).End(xlLeft).Column End Sub
View Replies!
View Related
Find First Value In Column
I've got time series data in columns which can have various #N/A cells until the first value appears. I am searching for a formula which retrieves the first value appearing in such a time series output list (column). I know this following formula to search for the last value: =INDEX(M12:M500;MATCH(9.99999999999999E+307;M12:M500)) There must be something similar to find the first value...?
View Replies!
View Related
Find In Column Possible Matches
I have a list of values in a column. In that column, I want to find 50 possible matches. Is this possible in Excel? In the column there are multiple strings of text. After it finds these 50 potential matches, the formula should return a value next to the cell where it matched. PLEASE HELP IF THIS POSSIBLE.
View Replies!
View Related
Find Column And Paste...
Currently there are 3 sheets in my wbook, however there are likely to be 30 or so soon enough. Each sheet has loads of Cols of data and no sheet is generally the same. I'm after my "Control" sheet to display a replica of the columns entitled "Destination" found in all other wsheets. i.e. each sheet has a destination col and I need an automated way of getting these lists side by side. The challenge I'm faced with is that the destination col in each sheet is never usually in the same column, i.e. sheet1 could be colA, sheet2 colB etc and each sheet might contain more than one destination col,
View Replies!
View Related
Find And Match The Value In Column
A--------------b----------------c 1 abc klm-sometext 2 def nop-sometext 3 ghi def-sometext 4 klm 5 nop ghi-sometext 6 abc-sometext i want a macro , so that when it is run, it matches the value in column A with value in Column C and update the same in Column B For example the macro should find A1 Value = abc with C6 Value= abc-sometext and update B1 value as = abc-sometext So after running the macro the excel sheet should look like this A-----------------b------------------------c 1 abc abc-sometext klm-sometext 2 def def-sometext nop-sometext 3 ghi ghi-sometext def-sometext 4 klm klm-sometext 5 nop nop-sometext ghi-sometext 6 abc-sometext
View Replies!
View Related
Find The Last Column Having Data
The foloowing is a column in my worksheet: Sheet1A1WIRE CODE210PN90J310PN90K4510PN90HA610PN90J710PN90K810PNJ7#1DB91010PNJ9#86DRN1110PNJ9#86GN1210PNJ9#86YL1310PNJ9#87DRN1410PNJ9#87GN1510PNJ9#87YL161710PNJ9#85DRN1810PNJ9#85GNExcel 2003 you can see that A4,A9 and A16 are blank, what i need is a code which will find out until which row in column A has data and should enter that row number in cell B2. For eg in above sheet after running the code i get "18" in B2.
View Replies!
View Related
Find Last Instance In A Column
I am in the process of designing an inventory like spreadsheet with prices. If possible, I'm trying to find a well to look at a column for every instance an item shows up (lets call them apples) & then find the LAST instance of apples showing up to pull the pricing of that row only. Is this possible?
View Replies!
View Related
Find Lowest Value In Column
if it is possiable to do a find function to find the lowest value in a colum and then output that entire row. e.g a list of dates, I need to know what is the oldest date and what row that is for reference.
View Replies!
View Related
|
TRACKER
