Find the attached : example.xlsx
My requirement is compare two cells a1 & a2 if found true highlight a2,compare a2 & a3 if found same highlight a3 or compare a3 and a4 and repeat the same for the entire sheet.
I am trying to write some code that will compare three cells on a row and if they match then it will delete the row.
Column U-has Y, N entered
Column V-has Y, N entered
Column O-has 1, 2, entered (some cells may have the fill color set to red)
I need the macro to look at Column U and Column V and for example if the cell U2 has a N, and V2 has a Y then the macro would look at O2 and it there is a 1 with the fill color set to red the macro will delete that row.
The spreadsheet will vary on how many rows it contains. It can contain up to 5,000 rows and the macro would need to go through all the rows.
I have a data output from citrix that looks like the top half of the attached file. Each ID has 2 rows of data; open orders & recommended orders. Is there a macro that can sum up the two rows of data on to the first row (the one with ID and description in Collumn A & B) and delete the row afterwards, leaving only one row of data (format can be seen in the bottom half of the attached file). The original citrix report has hundreds of product IDs, which may vary from month to month. Original Format.xls
View 2 Replies View RelatedI have a dataset of shifts and want to compare each shift that needs to be filled to a list of requests for time off (vacation, etc).
For one cell the code is: =IF(ISERROR(SEARCH(B1, A1), 0,1)
Where A1 is the column of requests in comma delimited forme (ie: "AB,CD,EF").
Where B1 is the column containing the person assigned to shift 1 (ie: "AB")
In this case, would return a "1" as no error was returned, as AB was found in the list. Here "1" would represent a schedule conflict.
Without creating many cells for each shift- there are 20 shifts- can I create an array or string together this type of "SEARCH" function?
I need to create a formula that will give me True or False if text in a cell matches any cells in a range.
For example:
Column A and B have text:
Account
Date
Name
Species
Column C has other and sometimes matching text:
Name
Deal
What formula would I use to find out if all cells in Columns A and B are represented in Column C?
In the below link u will find each state driving license formats of ..example for alabama 7 Numeric..for ALASKA Up To 7 Digits..what am i looking is if we select a specific state and then insert any driving license of that state in next cell if the format matches i want that next cell to be true if not false ..wonder if we can create anything like this ..there are about 50 states with different license formats..
Can we write VBA for this .. [URL] .......
I've got a very big spreadsheet with over 40,000 lines.
The text is sorted alphabetically, and on its side I'd like to have a column displaying how different it is from the cell above.
Example:
A1 - Mediterranean Sea. -----
A2 - Mediterranean Ses. ----- 5.55%
A3 - Atlantic Ocean. ----- 100%
Basically, 'Atlantic Ocean' is 100% different from the cell above, but A2 has only one letter different (out of 18, including the space and the dot) compared with A1, so it's only 5.55% different.
This would help me find which cells that are supposed to have the same content, have errors and thus are slightly different.
I have one sheet and i want to compare the first word in the column "D". If the first word of D2, is the same than the first word of D3, i want to copy to another sheet and after the copy, insert 2 blank rows... if it's different, i want to copy and insert 3 blank rows
Maybe it's better to attach the file. In the first sheet, you'll see a little part of the real sheet, and the second sheet is the example of what results i want to obtain with code...
I have three cells - A1, B1, C1 and each contains a value of yes or no.
If more than two of the cells say "yes", I want the fourth cell (D1) to display "yes."
If less than two columns say "yes", I want the fourth cell (D1) to display "no" .
I've a spreadsheet and I need to find cells in Column A that contains certain text and subsequently being able to compare the data in 2 other cells (column F and Column J) and post the results of comparison in Column L. Please see the tables below:
In this table, if Range("A1") contains abc, then data in Range("f1") would be compared against Range("J1"). If F1 = J1, then Range("L1") would say "ok" otherwise "needs to check" as in Rows 2.
Please note that the amount of data varies daily.
ColumnA
Column F
ColumnJ
Column L
abc
fff
fff
ok
abc
fff
ggg
needs to check
bbb
fff
fff
Is it possible to compare code that's in two cells and show the differences? It's possible with Notepad++ and an extension but I have hundreds of pages to examine weekly and it's not efficient to do it manually.
View 1 Replies View RelatedI have a if function that i am creating in excel 2000, and it has 8 variables. I am trying to put it in a macro as it is not working as a nested if function. Here is the current if function...
=IF(B2=C2,"OK",IF(B2=D2,"OK",IF(B2=E2,"OK",IF(B2=F2,"OK",IF(C2=0,"OK",IF(D2=0,"OK",IF(E2=0, "OK", IF(F2=0, "OK", "INCORRECT"))))))
the excel file is attached for further clarity
I have a workbook with tons of programming I need to achieve. Here is my work book so it can be viewed.
Right now on "Protocol" Sheet I want the cells with the "Protocol 1" field to look at Admin_Panel Sheet and find the row with the same text. Then take the text in cell A of the same row and copy it to the cell below the "Protocol 1" listed on the protocol sheet.
I need search and clean the entire cell, with the word "MENOR". (in english is Minor). The problem is: The cells can contain the word "es Menor" or "Menor de edad" (the macro is in spanish) in english is "is minor" ". "You are a minor"...(I try but my english is not very good,)
View 4 Replies View RelatedI receive thousands of raw data every week as shown in the excel sheet {raw data}
I would like to use a macro that could automatically sort the raw data like the format in the excel sheet {template}
For example:
Looking at the raw data and compare with the template, i would have to delete the whole columns that have (BU_CODE , EAN_CODE, SUPPLIER_CODE, SUPPLIER_NAME , BRAND_NAME, and SUB_CATEGORY_CODE) and add in columns and name it as Principal, Cateogry , Brand and range).
I need to remove multiple instances of pages numbers from a 2000 character text string I have dumped into a cell. The spacing is not equal between the page numbers but they always take the form |#####-##| i.e. bar delimited 5 digit-2 digit. I tried SUBSTITUTE but it will not accept wildcards. I was thinking of looping through a SEARCH. The numbers are ordered ie |00001-01| to |00001-25| then |00002-01| etc, stopping at 25. This might lend to looping but I couldn't wrap my head around the VBA to accomplish that. A sample of the text
"|00006-01| (Defendants' Exhibit Nos. 1,2 marked for |00006-02| Identification.) |00006-03| BY MR. JOHNSON: |00006-04| Q. Doctor, I am handing you Exhibits 1 and 2. |00006-05| Exhibit 2, is that the one that you brought with you, |00006-06| the deposition notice of today? |00006-07| A. I believe so, yes." Auto Merged Post Until 24 Hrs Passes;I should have said this before but this is for use on Excel 2003 WinXP.
i m creating a macro to delete extra characters in a column. I have over 200,000.00 records in one column which consists of numbers with characters. For example:
#80723666
-80726960
80730187--
/ 80730279
80736277 /
( )80739210
* 80739823
& 80735380
80796440 @
## 80722138
if anybody can come up with a macro, so that I can just have the numbers and nothing else. I would like to exclude the following -~!@#$%^&*()_=+?/.";:|][{},^` within a cell.
I am making a buiness card request form, which I have attached. On the Master Sheet (Sheet 1), the user enters in his/her personal data, such as his name address, phone number, etc. After he/she finished entering all the relevant data, Business Card Layout (Sheet 2) automatically populates and shows the user what their business card will look like.
Everything works fine, however I just realized that the contact information might vary from person to person. Right now, a person can enter 5 different contact information, but most of them will probably only choose four.
I wanted to know how to shift the cells down if the user does not fill out one of the contact information.
I am using Excel 2010 and I need a macro to delete all text from cells and leave the dates.
Below is an example of the contents of six cells. Some have just text, but some have text and dates.
1st Time Attender: 4/8/20121st Time Attender:1st Time Attender: 4/5/20122nd Time Attender:2nd Time Attender: 4/8/20122nd Time Attender: 4/5/2012
In this example, I would want the new cell contents to be:
4/8/2012
4/5/2012
4/8/2012
4/5/2012
i have this formula and need to change true and false to text for both the expressions
=OR(E23<=DATE(YEAR($L$2),MONTH($L$2)+$J$2,DAY($L$2)),($L$2>E23))
what im trying to do is e23 has a static date (food sell by dates)and if that date gets within 3 month (J2) period of todays date (L2) i want it to say "warning" if e23 is equal to or older than todays date i want it to say out of date.
L2 has =today()
J2 has the number of months in it for the warning
I want to have a function or macro that if cells have mingled bold text, that only the bold text shall remain in the cells, and all the rest of the text must be deleted.
View 4 Replies View RelatedI have a worksheet where I have around 300 rows, each with 7 columns. What I want to do is add a checkbox to each column. I plan on setting non-applicable checkboxes to mixed status and locking the worksheet. I will unlock applicable checkboxes and sumif or countif their value according to row-based scoring, for example, each checked checkbox represents a value of 3. I do not know VBA and have chose to use the form control checkboxes rather than ActiveX.
I believe that a formula for this would be something like: =SUMIF(B1:B3,True,"3") or =COUNTIF($B$1:$B$3,True)*3
I am wondering firstly if I have that right and secondly if there is a way to stop my checkboxes from displaying labels. Currently, if I click on one it displays True behind the active checkbox. If I uncheck it, it displays False.
Attached 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 to count values of numbers in a row next to text(True ot False) Like:
A1____A2
True | 150
True | 66
False| 40
True | 78
False | 56
Output:
True: 294
False: 96
I have values in A1 through A4.
I have a string of text in cell B1.
In C1, I need a formula that displays TRUE if any of the 4 numbers from A1 through A4 are contained within the text in B1. If not, FALSE will be displayed.
Been messing with SEARCH and FIND, but not getting it to work.... not sure if those are the functions I need.
I have 2 sheets. On one sheet, this column J2:J490 contains text that either says "TRUE" or "FALSE" and another column AJ2:AJ490, text that says "FEES". (many of the other cells in this column say something else). I need a formula that can find the matching text TRUE/FEE or FALSE/FEE in these columns, count them, and give me the product.
View 9 Replies View RelatedI have a document passed to me that includes information pulled from an internal system.
I want to use some basic formulas to show Totals, averages etc.
However, the SUM function does not work on the data provided unless I select the cell (As if to edit the text) and the press enter.
Is there a quicker way of selecting all of the cells in Column A for example and performing this function on mass, rather than selecting each cell individually.
I have attahced a similar sample copy - My SUM formula is show in cell B15.
I have a spreadsheet with 4 columns.
I would like to Compare Column A with Column B, if Column A contains text or numbers anywhere in Column B, then remove the corresponding rows not just in Column B, but Column C and D as well for that row.
I have several (Child) sheets of data which I need to compare to a (Parent) master sheet. If a cell in column "A" on a child sheets matches a cell in column "A" on the parent sheet, I need the row on the Parent sheet deleted so in the end, only the rows with no Child matches are left on the Parent sheet.
View 4 Replies View RelatedThis compares cells in every sheet to a master sheet "AllUnits" and deletes all the matched rows on the Master. I modified it slightly to also delete the matched row on the source sheet as well (looking for rows that are not matched on any sheet). This works to a point, but I have to re-run it many times since when the source row is deleted, the code actually skips to the next row. I tried to reset the source variable with
View 2 Replies View Related