I have a list of Expense Codes in Column A. They look like this
0010-0020-8200-70.
Where
0010: Represents a Location
0020: Represnts the Business Type
8200-70: Represents Expense Type
I also have 3 tables on the spread sheet, which explains what these codes mean.
I need a macro that will look at the numbers in colum A and return the three types of expenses so they dont have to be looked up manually.
I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.
I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".
I have a document needed to be printed with some pages in the middle in landscape page type, the rest in portrait. If using Word it would be easier, but in Excel I cant find the section break to chage page setup separately. Is there anyway to do it. Currently I'm printing the document separately in portrait and then landscape with some page break added and page number modified. However it's quite troublesome and easy to make mistake.
View 3 Replies View RelatedI have created a macro that goes to a report application. The report application runs a script that extracts certain parts of various reports (the script asks for user input to pull the various reports) and exports the data to a spreadsheet. The script gets one report, writes the data to a spreadsheet, gets the next report, appends the data to the spreadsheet, etc. The macro then moves and formats the data in a customized way to be put to second spreadsheet. My problem is that the macro tries to format the data before the script has completed - how can I stop the macro from running until the script has finished? The script varies in run time - anywhere from 1 to 5 minutes.
View 2 Replies View RelatedI have set up a form where the user can enter the number of copies they want for 10 items (I will call them item1, item2 etc for ease)
Is it possible to then link the numbers in the form to separate parts of a large printing Macro?
I have a cell located in $X$1 that is always populated with a person's full name.
I need to display their email address, which is the first letter of their first name then their last name + '@company.com', in another cell on Sheet2
So for example 'Bob Smith' would be 'bsmith@company.com'
How can I go into cell X1 of Sheet1 and copy the first letter & the remaining letters after the space in between the names and paste them into Sheet2?
I have below code which pin out numbers from IBAN in Column P. Its formulas run by macro. It works fine. But what i really need is that instead of putting the formula in the cell, is that it compare the value from the formula, with the excisting value , in each cell . And it mark the cells where there are a difference with yellow colour I have been thinking how to do this but cant really work it out. If some have a better solution its ok but i need a macro for it.
have a look at below code, which change the cell and correct the error, but which i want just to compare the values.
I have attached a sheet to test with.
[Code] .....
Attached File : Testforcompare.xls‎
i have a cell which contains data like this
in cell A1
12. John Smith (67)
There are hidden spaces in cell a1 as well which are not needed, which i can get rid of using TRIM so that may need taken into consideration ??
In Cell A2 i would just like John Smith
In Cell A3 i would just like the 67
the number and . at beginning is not needed just the name and number (no brackets)
I have several cells with text as such:
cell A1: john doe (johndoe@email.com)
cell A2: jane smith (jane smith@email.com)
How can I extract just the name? I want the formula results to be john doe and jane smith
I got some data like this
A1=CTAC US
A2=FCOB US
A3=TW US
The gap between each word and the word "US" is uneven. sometimes it's 3 spacebar apart, and sometimes 4.
I am trying the pick the part not including US. I tried =IF(ISERROR(SEARCH("US",A1)),A1,LEFT(A1,SEARCH("US",A1)-1)). It works but when I use match function to look up the word. It returns N.A. As the output is not just CTAC(4 characters), but 5 characters(including blank). The problem is the gap between between the word and "US" is different for each cell. so I cannot just minus the same character in the above formula (e.g. -2 if there is one blank cell). Anyone can tell me how to pick out the word and with only the number of characters in the word?(no blank)
Like u see in attachment i have products in cells wich are painted in different colors. I need to compare two numbers of different product, and write result to column "Type D,C,B". If one of two products is without color/white or they are not the same color then result is always "D", if both products/cells have same color and different number then results is "C", if the color and number is the same then result is "B"
Below cell B2 I will write numbers by hand, when I finish and start macro it will compare first cell B2 and B3 and write result to C3, then compare B3 and B4 and write result to C4 and do that until there is any number in column B:B.
Attached File : excel help.xlsx‎
I am trying to modify the macro listed below for the following example. It would work when I have only numerals in the cell but this new query, the cell has both letters and numbers.
I want to compare column "M" from worksheet one to column "B" in worksheet two. If the information matches, then copy the value adjacent from worksheet two column "A" to worksheet one column "L".
Here is the macro that worked for me using a search of only numerals.
figuring out why the data doesn't copy?
Macro:
Sub merge_accession_PS_rad_productivity()
Dim rng2 As Range, c2 As Range, cfind As Range
Dim x, y
With Worksheets("Imaging_Summary")
'N4=Accession on Imaging Summary worksheet
[Code]....
I have sheets with names of people in columns....some married...some not. When they are married, here's a sample format...
Jones, Donald T | Baker, Sarah Jane | Jones, Sarah Jane | Smith, Sarah J | Jones, Sarah Jane Smith
In this example, I would like to be able to determine which of the Sarah's belongs to Donald w/o having to visually look at each record ( 100,000's of records). (FYI: the names for Sarah would/could be her Maiden Name and possibly a name or two from a former marriage). What I need to be able to do is match and extract the names of Jones, Donald T and Jones, Sarah Jane and Jones, Sarah Jane Smith and eliminate Smith, Sarah J and Baker, Sarah Jane.
In my example, Donald is in the first column, but can be in any column on a row so the name positions are random across the columns. However, the format for each column is then same...Last Name, First Name Middle Name(or Initial) with a comma always after the last name in each column. The length of the last name also varies.
VBA or Formula that will search the cells in the columns of each row and return the names (complete contents of the cells with matching last names) that have a matching last name for that row.
Question is this, "can a single cell in a column be divided into two parts?"
View 14 Replies View RelatedI wrote a macro that will compare a cell with another cell (top cell color yellow compare wit the white cell value). If the cells matches color green will be assigned if not red color will be assigned. I have one problem with which I can't figure out is that if the top cell in yellow has two or three values such as (5780, 1975 or -116,-110,-108) and the camparing value has only one value (5780 or 1975 etc...).
View 2 Replies View RelatedI have a spreadsheet with three sheets („Data”, „20” and „30”). On the Data sheet I have only two rows, and I do not work on this sheet. I work with the sheets 20 and 30. On the sheet 20 I need to sum up the amount of column H. But my problem is that the count of this column (the rows or cells) allways change. Sometimes there are 554 the next day 663 and so on. So I need the macro to sum up the amount of column H two cells under the last cell. I need exactly the same in the sheet 30, but the only difference is that in the sheet 30 the amounts are in the G column (not in H). Finally I need to compare the two summed up amounts (sheet 20 and 30) and they must be equal. So what I would need as macro is that when I run it, it sums up the amounts in both sheets and then in the Data sheet (in the 5th row, cell A) displays the summed up amount and (in cell B) displays OK if the two summed up amounts are equal, or just dislpays ERROR if the summed up amounts are not equal.
View 13 Replies View RelatedAttached is a sample data set. If all the positive and matching negative entries are located and removed, there are 4 remaining entries. The only pertinent columns are A through J, the columns after that are where my fiddling is occurring.
First you have to highlight all the data (including the control columns to the right). Then, my macro sorts the data by the absolute value (found in column O), allowing the formulas in N to then properly mark all negative entries with an "X and all the ones that now have a matching positive entry directly below it with an "X".
Then my macro hides all the fields with an "X" in column N. There is a macro called "CLEANSE" that does this, but only hides the rows. I keep messing up when I try to delete them entirely.
Can someone correct my macro so it will properly delete the rows with an "X" in the N column, or suggest a better macro that will scan the E column and flag matching positive/negative numbers and delete them without my control columns N and O?
i need a macro to do a comparison b/w the two given files ...(book1 n book2) ...
n want the result to be like result file .. (result.xls)
I am trying to move some parts of a row (From G:AE) where text is found in G Column. It moves it back 1 column from G
[Code] .....
Attached File : ozeform.csv‎
My Process goes like this: We receive work in the excel sheet and in the column K(Period will be in YYYYMMDD)
First I want macros by which I can change YYYYMMDD TO MMDDYY.
then
It should compare column J and column K.
see the below example.
Period date (column K) Product end date (column L)
Column J column K
20131031 10/31/2013 = If column J & K dates are equal then "No action required"
20131031 7/31/2013 = If column J is higher then column K dates then "Action required"
20130930 11/2/2013 = If column J is lesser then column K dates then "No action required."
I need comments in column Q.
I am relatively new to VBA macros. I am having two sheets, in one sheet I have a non-contiguous 20 rows range and in the other sheet I have a 20 row contiguous range. I need a macro which will compare data between the two ranges(one to one compare) meaning 1st row of the first range should compare with 1st row of the second range and if it matches then it should populate the adjacent column in the second sheet with true or false accordingly.
View 2 Replies View Relatedmacro to compare 4 columns of text (first and last names) then add a checkmark in separate columns if they match and don't match?
For example I have this kind of data to compare and the results required:
Sheet 1
Last Name First Name
Smith Mike
Johnson Bruce
Hendrick Fred
Shaffer Kerry
Sheet 2
Last Name First Name Match No Match
Klee Pierre X
Verge Kerry X
Smith Mike X
Wright David X
Hendrick Fred X
I need the macro to mark an X in the Match column if the first and last names match only and if they do not match exactly a X in the No Match column.
I am looking for VBA Macros to compare 2 price list. I am trying to achieve the following.
1. Script look in sheet one i.e. Old list for duplicate if it is their it should display in 3rd sheet i.e.duplicate with sheet name and row number
2. Script look in sheet two i.e. New list for duplicate if it is their it should display in 3rd sheet i.e.duplicate with sheet name and row number
3. Script look in both the sheet for duplicate values it it is there then it should display in 3rd sheet i.e. duplicate
Also it should count the number of records on each sheet in column F1. If possible can we display the work % while checking both the lists.
Sample file is attached.
I have account numbers in Col A and in Col D .I would like a macro to highlight the color the items in Col D that are not in col D. Blanks can be ignored
View 3 Replies View RelatedCurrently I am working on a Userform based Macro that reads date and time from user and compares it with the date and time present in a cell in excel sheet(in mm/dd/yy hh:mm format). If the date and time value entered in both places matches then it will fill green colour in the cell.
I tried to convert the date present in excel sheet into a string using Cstr but it is not working. I have attached the code below for your reference.
Dim lr As Long, i As Long
lr = Sheet5.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lr
If Cells(i, "D") = "FIT" Then
If Left(Sheet5.Cells(i, 14), 1) = "M" Then
[code].....
I need making a macro which compares a particular range of columns in one sheet to other sheet in same workbook.
It is like i have two worksheets named "Working Copy" and "Cleaned Data - (Working)". In this I have to compare Columns (A:AG) from Working Copy to columns (B:BH) of Cleaned Data - (Working). and if there is any difference between any cell then it should be highlighted with a colour in both the worksheets.
I've created a macro that will compare 2 columns and move the rows around (whilst maintaining the original order) to line matches up and leave spaces for mismatches. In the file attached if you run the macro "RouteCompare" on the "Selection1" tab it works fine as there is only a small number of rows.
When i try it on "Selection2" tab with over 2K rows it bombs out. IT seems to only work for columns that are only slightly mismatched or not that long.
I'm not an expert on it, but i thought it might be excel using up all the resorces it has and grinding to a halt.
Is there any way to edit the macro to allow reset whatever is slowing it wodn after a period of time, or changing the macro to prevent it from crashing on long flows?
Here is the code I'm using:
Sub CompareMyRoute()
'Macro to compare flows
'Created by by Brendan M Murphy
Application.ScreenUpdating = False
[Code ...........
I have 4 worksheets in a workbook: “choose security”, “download”, “MC” and “Lending”,
In the choose security” worksheet in CELL A2 is an HEX code, i.e. stock code.
2. In MC worksheet is raw data of all holdings related to various p’folios and securities, the information we need from this worksheet is,
a) p’folios, which are in column A,
b) Holdings which is Col B
c) Security code i.e. stock code which is in Col D
I need a macro or a formula to look at what security code is in “choose security” worksheet CELL A2, then look for this security in worksheet “MC” Col D, once matched, copy the data for this security to worksheet “download” and paste in CELL A3, I only need p’folios and holdings to be copied to “download” so data in Col A and B.
Once data has been copied to worksheet “download”, I then need another macro or formula to repeat step 2, but this time the macro will be looking at worksheet “Lending”,
a) P’folios, which are in Col B
b) Security code is in Col D
c) Holdings is in Col H
Once the macro or formula has found a match of security code, it will then copy the data from Col B and H to download worksheet CELL E3.
Hi all,
I have a workbook with 2 sheets: "dan" and "resources"
On the "Dan" sheet, in column B there is a list of names and in column column C, a corresponding dollar amount.
On the "resource sheet" in column B is a subset of the names found on in column B of the Dan sheet. On the Resourse sheet, there is a corresponding dollar amount in column c also.
I would like a macro to compare the dollar amounts to ensure they match. If they match, I would like to place an "OK" in column Q on the appropriate row of the Dan sheet. If the amounts do not match, place an "X" in column Q of the appropriate row on the dan sheet. If the name on the Dan sheet is not one that appears on the Resource sheet, then place an "N/A" in column Q of the appropriate row on the Dan sheet.
I m trying to write some code use VBA in excel to look at cell C2 and if it equal A0 or whatever then it will put in cell D2 = "blah blah" and then go down to the next cell C3.
Select Case Range("C:C").Text
Case "A0"
Range("B1").Value = "not booked"
Case "A1"
Range("B1").Value = "Misplace documents"
Case "A2"
Range("B1").Value = "Closing cancelled"
Case Else
End
End Select
End Sub
I have a report that in one column codes (A1,A2,A3,B3 etc..) and these codes have a description and I want the VBA code to look at the description Code and then put in a different column the description. but Im having no luck.