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!
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.
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?
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'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
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?
I have a table in the format below. Pin_11 in this table is CLK and Pin_12 is STRB. I want to replace Pin_11 with PIN_CLK. Basically I want to look for CLK in Column3 and record what ever is in Column1 and Column2 on the same row (base row). For every repetion of that value in column1 and column2, in the rows above and below the base row, I want to replace column 2 with PIN_<column3 value>.
See below for initial table in column 1,2,3 and final result in column 6,7,8. I have a huge file this format of data that I need to end and was wondering if there is a script or formula to do it.
There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
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..............
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.
I have a 2 groups of column headings with a different month and year in each heading so
1st Group of columns range Columns AJ through AX Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"
2nd Group of columns range AY though CE Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"
Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"
Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"
Because the ranges keep changing month over month, how do i do this.
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:
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.
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)
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
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.
I have 2 worksheets, "Data Dump" and "Target List"
I'm trying to cycle through the "Target List" in column "A", find the same value in column "B" in the "Data Dump" sheet. When I find it, I want to copy several other cells from the found row into cells on the "Target List" (though probably to a different column). Here is the code I'm trying to use. How to correct it to get the result I'm looking for.
I have a macro that copies data from one sheet to another. I would like getting the macro to figure last row used in column B from B3 on and put the word Sum below it. For example, if last row used is B64, on B65 would like the word sum on it.
I cannot just input in as the number of rows could change when macro is run. Data is dynamic.
Here is simple code I have:
Sub Weight() Sheets("Final Month").Select Range("A2:B1055").Select Selection.Copy Sheets("Final Weight").Select
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.
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).
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()?
I am trying to work out how to nest offset in a code using search replace. The goal is to find a value from cell A1 (example) and replace the values in the cells next to the cell containing samuel and the cell containing samuel.
A sort of search and remove data tool if you like So for example:
[QUOTE]A1: "samuel" (the search criteria) Search range is: B1:D400 (for example) Cell B40 = samuel C40 = Driver D40 = year
So, the macro is activated and finds "samuel" in B40 and I would like C40 & D40 replaced with "" The code I'm using is below: (this is just replacing the cell containing the search criteria with "test". I would like to nest offset(0,2) & offset(0,3).Value = ""
Sub Macro1() Range("B1:D400").Select Selection.Replace What:=Sheets("Sheet1").Range("A1").Value, Replacement:="test", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub
One of the columns on my worksheet has a list of employees. I need to replace all the names with a blank (or delete) except for those employees on my team (7 employees). VBA code to Replace/Delete all EXCEPT (a,b,c,d,e,f).......?