Find Multiple Numbers In One Column And Replace With Single Phrase In Another Column

Jan 31, 2013

I have taken over this spreadsheet for my work, and it is basically a statement in excel. What I want to do is find a list of invoice numbers in column B populated from a remittance, and then replace column F to say a specific thing depending on check number and date paying for that invoice. So if a check printed today I would have it replace column f to say paid 1/31/13 check # xxxxx. Currently I am searching for each invoice indivudually and then replacing with check number and date. There are about 200 invoices per month that I deal with, and it is a big waste of time!

For example from this

invoice #1 / reconciled (DATE)
invoice #2 / reconciled (DATE)

To this:

invoice #1 / paid (DATE) check # (xxxxx)
invoice #2 / paid (DATE) check # (xxxxx)

View 2 Replies


VB / Macro Code For Multiple Find And Replace Within One Single Column?

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

Find & Replace In Single Column

Dec 9, 2009

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 6 Replies View Related

Find Phrase In One Column And Apply Formula To Different Columns Same Row?

Aug 27, 2013

I am trying to come up with a macro that will find rows that contain the words �as % of Revenue� and then calculate and apply the appropriate formula to the actual and budget columns. I have attached a sample of what the source data look like.

The formula divides the category�s total expense by the revenue in row 4. For a complete macro, I would want to also subtract the budget result from the actual result and put its result in the �better (worse)� column. I need to use this on 12 or so different workbooks which is why I�m wanting a macro.

Here is my attempt to accomplish this task; however, I quickly realized it is woefully inadequate because I neither understand how to apply it to the different columns or how to find the next instance of �as % of revenue�).

Sub Macro1()
' Macro1 Macro
Cells.Find(What:="as % of Revenue").Activate
ActiveCell.FormulaR1C1 = "=R[-1]C/R10C"
End Sub

View 8 Replies View Related

VBA Code For Find And Replace A Particular Phrase

Apr 23, 2008

I am trying to write a code for VBA code for find and replace, I want to find a particular phrase (i.e. 1. Value Added Processing) which is all in one cell and replace it a range of cells of other cells which is contained on a different sheet.

So basically the original 1 cell would be replaced for anywhere between 1 to 20cells. Depending upon what I type in.

Worksheets(1).Range("B2:B50").Replace What:="1.ValueAddedProcessing", Replacement:= _
"Sheet1!A1:A11", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

View 9 Replies View Related

Find Specific Phrase In A Column And Change The Row Color And Its Border

Mar 27, 2014

I have the table, as shown on left in the appended image, and the final result should be the right one.

The steps are written as 1, 2, 3 and 4.


View 13 Replies View Related

Multiple Row, Single Column Cell Blocks Into Single Row, Multiple Column Format

Mar 21, 2008

I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...

View 9 Replies View Related

Multiple Date Column To Single Column & Sort

Sep 23, 2006

I'm looking for a way to sort dates from several columns into a new single column (perhaps multiple columns if the entry columns become too numerous). I've included an example. There are currently only 4 columns, but there may be as many as 20 in the future, each with 20 dates under each heading. Any blank cells would be eliminated. If I filled a blank with a new date, that date would be placed into the chronological column. So basically, this would take the date from several different categories and create a single calendar of events.

View 8 Replies View Related

Find In One Column Replace In Another Column

Apr 11, 2008

I want to find an instance of a word in one column......and replace whatever is in the adjacent column with a word.

View 12 Replies View Related

Delete Numbers And Replace With Numbers From Adjacent Column

Feb 12, 2010

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 9 Replies View Related

Find Median Of Set Of Numbers On Column That Correspond To Dates On Left Column

Sep 9, 2013

I am trying to find the median of a set of numbers on a column that correspond to dates on the left column. I want a monthly median average of the numbers on the right which correspond to the dates on the left. So for example. I want to make an equation that gives me the median of all the numbers on the right if they fall within the range of a certain month(in this case October). I've tried These:


View 4 Replies View Related

Find Lowest 5 Numbers In Column A With Highest Values In Column B

Oct 5, 2013

I am looking for a formula that will find the lowest 5 number group in column A with a total value from column B under $100 in the below table.

1 $28.75
2 $28.00
3 $27.75
4 $24.75
5 $25.50
6 $25.25
7 $25.25
8 $16.50
9 $24.75
10 $26.50
11 $24.50
12 $27.00
13 $26.50
14 $23.50
15 $23.00
16 $19.25
17 $20.75
18 $25.00
19 $20.50
20 $20.50
21 $17.75
22 $22.50
23 $20.50
24 $19.75
25 $20.00
26 $10.75
27 $11.25
28 $5.25
29 $13.00
30 $9.00

View 3 Replies View Related

Find And Replace Certain Signs In Column - VBA

Sep 3, 2013

Column H contains alphanumeric data (codes).

I need a macro that renames all cells in column H starting with the letter "B" by replacing B with "Temp" and thereafter cuts the last two digits of the code. B224501 would thus be Temp2245.

View 2 Replies View Related

Find & Replace With Column Data

May 30, 2009

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 9 Replies View Related

Find And Replace Cells In A Particular Column By Using Macro

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

Find Numeric Codes In A Column And Replace In Vba

Oct 18, 2007

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:

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 9 Replies View Related

Excel 2010 :: Find And Replace Within Specific Column?

Oct 1, 2012

I have an excel workbook where some sheets have a column called "Name" in Row 3. The column where "Name" appears shifts based upon other criteria so it's not set within 1 specific column.

The real data for the "Name" column starts in Row 5. Is it possible to create a macro that looks in Row 3 for "Name", then once it finds that column, it does a find and replace from Row 5 to the end of the data and replaces every space with a ^.

For example, if I have:

Row 3 Name
Row 4
Row 5 John Smith
Row 6 Jane Doe
Row 7 John Doe
Row 8 Jane Smith

I'd like the data to become:

Row 3 Name
Row 4
Row 5 John^Smith
Row 6 Jane^Doe
Row 7 John^Doe
Row 8 Jane^Smith

I'm using Excel 2010 if that impacts anything.

View 1 Replies View Related

Find Certain Text In Column And Then Replace All Similarities From Another Cell

Nov 6, 2013

I'm trying to find a function, or conditional format, that will allow to find certain text in a column and then replace all the similarities from another cell.

So basically, I have 3 Columns.

Redundant Text
Replacement Text
What The Text Currently Reads

Red Hats
Save On Red Hats Online
Black & Red Hats

Blue Jeans
Find Blue Men's Jeans
Blue Jeans On Sale

1) There is text that is recurring in the the thousands of the cells of Column 3.
2) I have used a program that found the recurring data, and put them in Column 1
3) Column 2 is what I want ever cell in Column 3 to change to when it matches the cell in Column 1
4) Is there a function that will find text in the 3rd Column that matches text in the 1st column (multiple cells in the 3rd column will match one cell in the 1st column)
5) Then Replace the text in the 3rd Column with the corresponding text in the 2nd column

View 2 Replies View Related

Seeking To Average Numbers In Single Column Broken Into Increments Of 5

Sep 12, 2008

I have a column of over 500 replies to a survey. I want to create a pie chart showing the average ages of the respondee to that that question, broken down into increments of 5 years (this was the question -- how old are you) and reported as a percentage.

View 9 Replies View Related

Copy An Entire Column Having Numbers And Words On Every Single Cell Into Another

Jul 2, 2007

How to copy an entire column having numbers and words on every single cell into another column but only copying numbers, not words.

View 9 Replies View Related

Split Phone Numbers Into Single Column & Remove Duplicates

May 4, 2008

I have to take a range of phone numbers that I have to port from another carrier and manually type them out or copy into another speadsheet, removing duplicate numbers. For example, I have the following numbers:


The results on the next spreadsheet need to be:


I am looking for a way to automate this process.

View 8 Replies View Related

Rank Instances Of Phrase In A Column

Mar 21, 2014

Let's say this is my data. It's already been sorted so that like entries are all grouped together.


I need to make a macro that numbers my data like below.






Essentially, my data is many columns and sorted on a bunch of different levels. I need a quick way to assign a rank to every row in the same group, to see at a glance which performed the best.

View 3 Replies View Related

Find And Replace Or RegEx - Remove Symbols From Column Of Strings

Sep 27, 2011

I have a find and replace function that removes + smybols from a coloum of strings. How can I remove the first instance of a space (if it later contains a +, too?


Before: [ +Test +Test]
After: [+Test +Test]

Selection.Replace What:="+", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

View 4 Replies View Related

Select Multiple Names In Column And Replace?

Dec 18, 2013

I have an email list that I email. Sometimes I get some that doesnt exist anymore. So my program sends email out and respnods to me that says these emails do not exist and gives me the emails. So I manually have to go and find them one at a time on my list to delete by CTRL F. Is there a way to submit all email names to find at once instead of doing one at a time.

View 9 Replies View Related

Search For A Phrase In Column - Function Return Error

Feb 18, 2013

I am trying to search for a phrase in a particular column but the excel search function is returning !Value as an error...

Both fields are text so nit sure what is wrong here.

I have a list of cities that i want to cross reference across a cloumn of notes on another sheet. So, I really want to search for the city (Sheet1 Cell A1) in Notes Sheet2 Cell A1) and I want to return it in Sheet1 Cell B1 Whether it exisits in the notes. I then want to copy this down my list of cities in Sheet1 Column a.

View 5 Replies View Related

Add A Word Or Phrase In Front Of The Data Already In A Column Of Cells

Apr 3, 2008

what's the fastest way to add a word or phrase in front of the data already in a column of cells?


cell contains product description and I need it to show
company name product description

View 9 Replies View Related

Single Column -> Multiple Rows

Mar 5, 2014

I have a single column of about 1500 points, and I need them to be separated into many rows of 20 numbers.

I have multiple issues:

1) The middle number of each row (10th and 11th) need to be repeated. So a transpose formula can not work all the way through the 20 numbers. Unless I transpose the first ten, then tranpose the second ten. Is there an easier way around this ?

2) The bigger issue is, I would like to write a formula that can manipulate the date to enter in the correct cell.

To illustrate I will use a set of ten numbers:





View 9 Replies View Related

Find / Replace From Column A Values To Column B Values VBA

Mar 6, 2014

I have a workbook with 20 sheets in English Language, but I must to translate in Russian. Translating has done by human translator, so translation is good. I must change phrases and words in english to russian. I can use find/replace function but doing by hand it's very tie consuming. I need VBA or macro who takes values in column A, finds it in entire worksheet and changes it to matching value in column B. Is it possible and how I can do it?

View 1 Replies View Related

Add A Certain Phrase In One Column And Put A Value In Another Column

May 27, 2009

I want to find the equation that will add a certain phrase in one column and put a value in another column. For example, suppose I want to find out how many people in column A has a red shirt. In the column there are a variety of colors. If they have the red shirt then I want to put a yes in column B.

Another example that I've struggled with is if I want to find out if someone had a birthday between March 1 and March 31 (a certain time) in column A, and put the value of all those people in another cell?

View 9 Replies View Related

Transposing Multiple Rows To One Single Column

Apr 12, 2005

I frequently have to work with tables set up like this:

A1 A2 A3
B1 B2 B3
C1 C2 C3

All I would like to do is to transpose them into one single column:




View 5 Replies View Related

Copyrights 2005-15, All rights reserved