Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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 In Single Column
I'm wanting to use VBA to search down a column for a one dynamic value and replace all instances of that value in the column with a seperate dynamic value. My first attempt was this:

View Replies!   View Related
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
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
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
Code Change Below To Find The Customer Column By Name Rather Than Column Letter
I have this macro below and would like the code changed to find the "Customer Number" column by name rather than by column B. Note that the "Customer Number" column will always be somewhere in row 1.

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
MINIF Function; Find The Minimum Value Of Column A If There Is Anything Greater Than Zero In Column B
I want to find the minimum value of column A if there is anything greater than zero in column B. I tried this formula (simplified): MINIF(B2:B6,">"&0,A2:A6) And it gives me the # NAME? error

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
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
VBA Find First Populated Column In A Row, Starting At Column X
How would I write something that finds the first populated column in a row, starting at column C and moving to the right?

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 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
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
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 A Charater In A Column
Before

Company segment
AAA A1
BBB A2
CCC A3
AAA A1

After

Company segment
AAA A9
BBB A2
CCC A3
AAA A9

how to use Excel macro to change segment Colume with company name " AAA " , segment code from " A1 " to " A9 " ?

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
Replace All Negatives With 0 In Column
I have a column with both positive and negative numbers, is there a macro or a bit of code that will convert all negative numbers in a selected column to zeroes?

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
Macro To Loop A Column To Replace The Name
I want a marco that can "find & replace" in a column, let's say K. Let's just say I want to find "AAA" and replace "BBB" and "CCC" and replace "DDD", I just need the code so I can change it myself. It will be more than 40+ replacement.

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
Lookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.

I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.

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
If Column Contains Data Then Insert A Blank Column And Shift Specific Column To The Right
What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.

View Replies!   View Related
Any Way To Find A Name In One Column And Put It In Another Column
I have a big spreadsheet with all the spending for this company. In column C it has description of each indiviual expense, ie. Travel for XYZ to Vegas or Hotel for XYZ to LA.

I want to find out all expenses on XYZ. Is there a way to search for the "XYZ" in column C (C2:C3200) and if there is XYZ, put "XYZ" in Column D (D2:D3200).

View Replies!   View Related
Look Up In Column A Of "AS" Once It Find That Number I Need It To Return The Number Thats In Column E Of That Row To Sheet "IS"column D
i have 2 sheets one called "IS" and the other called "AS" in cell a2 of "IS" is a number that i need to have excel look up in column a of "AS" once it find that number i need it to return the number thats in column e of that row to sheet "IS"column d. summary: a2 of "IS" looked up on sheet "AS" and returns the number in column e to cell d2 of "IS"

View Replies!   View Related
Find Replace :: Find All Occurences Of And Or AND And Replace With And
Range B3:B1000 is text strings. Column C2:C50 is a list of words that I would like to "Find" in Column B and replace with it's lowercase values unless they start off the string.

Example

Find all occurences of And or AND and replace with and
Find all occurences of With or WITH and replace with with.
Find all occurences of Or or OR and replace with or

View Replies!   View Related
Subtracting Column B From A, And Leave Third Column Blank If No Value In Column B?
I have a spreadsheet with three columns (A,B,C). I want the third column C to be column A - B (A minus B) for each row, but only if there is a value in column B.

If there is no value in column B, then I want that row in Column C to just stay blank.

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
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
Replace All The Times That These Words Appear In The Rest Of The Sheet With The Words In Column B
I have a column of words in Column A and I want to replace all the times that these words appear in the rest of the excel sheet with the words in Column B. If someone has already answered a similar problem link me to the thread because I can't find anything.

View Replies!   View Related
Find Column A Of Same Row Anywhere On Row
I typically record macros that need to copy information like a company name from say sheet 2 and paste in a specific cell in sheet 1. As the cursor can be anywhere on a specific row I usually create a column that holds something like zz.. and then in the macro go

Shift + Spacebar to select the line I need to work from then Ctrl + F to find zz.. and that is how I know where the cursor is as a starting point.

How is it possible to have the cursor know how to find a specific point i.e. column A from any specific column on that same particular row?


View Replies!   View Related
Find Last Row In Column
Is it possible with a formula to determine what the last row is in a
certain column which is "Not Empty"?


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 The Min And Max Value In Every Second Column
I want to find the Min and Max value in every second column of a sheet.

Column B contains a date, column C a figure, Column d a date and so on.

I want to return the max and min date, so want to skip the other values. I run a macro to get the data, So it can be any length, how do I set a command to find max, min. I can go all along the sheet and select every second row into the formula, but only want it to search data that comes from the macro as may put more info in other cells.

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 The Last Column In Row
I have been playing with code to try to find the last column in a row in order to add the contents of the row to a combobox list.

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
Delete Numbers And Replace With Numbers From Adjacent Column
Is it possible to delete a column of numbers then replace those numbers from adjacent columns and the adjacent columns are then filled with the numbers adjacent to them

View Replies!   View Related
Find Last Row Reference In A Column
I have a range in column A. which will vary time to time.

I need to find the row reference(say A99) that is end of the range. Then I need to use that row ref in another formula.

How to do this without VBA?

View Replies!   View Related
Find Largest Value In Column
if c2c1 do nothing.

05 =20-5
10 =20-10
15 =20-15
20 =20-20


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
Copyright 2005-08 www.BigResource.com, All rights reserved