I'm trying to check if data in column u is in column g if so highliglt the
Cell row in column G
Sub Macro1()
Sheets("Recipes").Select
Rows("2:5602").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
rlc = LastCell(ActiveSheet).Address(False, False)
lr = ActiveCell.Row
lc = ActiveCell.Column.............
Any formula that can look at the data in multiple columns and check for a match - returning a value such as YES or NO. Below is an example of my sheet.
A
B
C
D
E
F
G
1
Name
Number
Name
Number
Name
Number
Match?
[Code] ..........
Basically the formula would be in Column G and would look at B, D, & F to see if they match or not. In the example above - row two - they all match. So a True, 1, or Text option such as YES would be great. Same as row 3 only you can see they are different.
This is my basic setup, each piece of equipment will have more than one routine to be performed:
Equipment Type
Make
Model
Serial
Location
To Be Performed
Frequency
Last Performed
Performed By
Next Due Date
R1
[Code] ..........
I want to track changes on "Last Performed" and "Performed By" and, if changed, copy both values to a separate "log" sheet. In total, there could be up to 10 routines for each peice of equipment. That means I would need to track 20 columns for changes. I would like this check to be done on save because that ensures that the user is satisfied with their changes and keeps the log from being flooded. And lastly, it needs stored in the log sheet in a way that I can identify which piece of equipment it was for and which routine was done.
the traditional count if statement doesnt return what I need. I have an array of values that need to be checked.
Column: A B C D E
Data: .25 .49 .18 (Criteria 1 Result) (Criteria 2 Result)
What I need to do is check the data for the following conditions and if it meets the crirteria I need excel to place a 1 in column D or E.
Criteria 1
If any of the coulmn data contains a value less than .5 I need a 1 placed in column D
Criteria 2
If any of the column data contains a value greater than .5 but less than 1.0, I need a 1 placed in Column E. I tried using an IF/ Count If statement, but cant seem to get it to return the result I need.
How can I check (using VBA) if 2 columns (F and G) are already grouped.
View 7 Replies View RelatedI would like to count al the rows where in certain columns there are certain values.
For example:
=SUMPRODUCT(--(Bakjes!$C$2:$C$4000="1");--(Bakjes!$G$2:$G$4000="")
This works fine. However, I would like to look in columns N2:W4000 as well if in that specific row value x is present. This gives:
=SUMPRODUCT(--(Bakjes!$C$2:$C$4000="1");--(Bakjes!$G$2:$G$4000="");--(Bakjes!$N$2:$W$4000="x"))
This gives a #value error. Is it not possible to look in multiple columns at one time with sumproduct?
I have a worksheet with many dimensions and I would like to have a formula or macro to do the following: I need to check 4 different columns that have dimensions and calculate the best combinations in order to fit within the least amount of 96 inch boards.
I want to check relations between 2 columns.
Example:
1 2
1 3
1 4
4 1
4 5
4 3
2 1
2 3
In case that I have relation 1->2, I what to check do I have relation 2->1.
Could you propose me a formula or script to make this check.
Is there a way that when looking at selected cells you can check their formatting?
I am trying to write some vba code to check for blank spaces in a number of columns (doing it one column at a time at the moment).
However, the current code im using messes up as at the bottom of the data there is a Total: column which is formatted in blue. Total: is only in the A column so the code I have won't work for other columns when no data is in them..
Range("A65536").Select
Selection.End(xlUp).Select
If Selection.Cells.Row > 6 Then ' there's some data in the sheet since we didn't hit the header row - now look for blank currency cells next to occupied lessee prospect cell
While Selection.Cells.Row > 6
If Selection.Value = "" Then ' there's a blank entry in the sheet - add error to 'passed' string
passed = "Blank spaces in ACC column " & Selection.Cells.Column & Selection.Cells.Row & " - please correct." & vbCrLf
End If
Selection.Cells.Offset(-1, 0).Select
Wend
End If
If there isnt any data at all up to the header row but blank rows between the header and the bottom total (blue) row it doesnt work.. how can i get around this? I thought perhaps checking that it isn't blank OR the total row somehow by checking the color (ie formatting)?
Is there a good way to check two columns at the same time and return the value in the 3rd?
Example
12 0.07 8
12 0.37 10
12 0.82 12
12 1.30 15
12 3.90 22
12 8.00 28
12 15.00 35
I look for 12 in the first column with vlookup but I need to also look for a figure in the 2nd column which is greater than my figure. So say I want 12 & 6.0 I would want the formula result to be 28 because the nearest larger number in the table column 2 is 8.00.
I am using Excel 2007 on Windows XP. I have written up a questionnaire that allows the user to simply check boxes in the cells. My problem is coming up with a formula to count the number of boxes have been checked. I tried several count formulas without any luck. I think one of the problems is that when I highlight the cells with these check boxes it doesn't actually show in data in the fx line.
View 9 Replies View RelatedIf you open the attachment.. then you will see that Column A has all the names and Column B has it's Value.. likewise.. Column D has all the names and Column C has it's values...I'm trying to first match the names from Column A to Column D and then see if Column B matches to Column C..
View 8 Replies View RelatedAs an part of QA activity
1. first I export data and directly paste in the "RAWDATA" Sheet.
2.There are 50 columns and each has header like Clientname,date,Id,campaign,websitename,frequecncy etc.
3.Exported data does not have same order
4.so when I paste data in excel I check manually if orders are in correct or not?which takes my long time to review/manually check
Using macro or something else to review the order and if any discrepancy then raise error? Need Excel to check order of columns ?
I have a userform that I use for data entry with lots of combo boxes, list boxes and text boxes
One thing i haven't cracked yet is to check for duplicates against two matching fields
If a user (for example) enters 'SAB' (which will be stored in column A) and then 'UK' (which will be stored in column B) and there is already an exact match for both, then I need to inform the user that a matching record already exists
The following is OK: (the dots below are meant to illustrate spaces between the columns!!)
Col(A).....Col(B)
SAB........UK
SAB........USA
SAB........Italy
BUT, if a user then tries to enter:
Col(A).....Col(B)
SAB........UK
I need my userform to register a duplicate entry.
My sub routine is detailed below : .....
what i am trying to do is look at an entire column, and see if that same date exists in the other Activity tab, if the dates match, then show the activity
here is the formula i am using, =IF($A9=Activity!$A2,Activity!$B2,"No Activity")
my problem is that i am only able to isolate one of the cells,, how do i check the entire column?
in other words, if i select two weeks, those two weeks of activity will show, if i select all weeks, all of the weeks activity will show, right now i can only get one of the weeks
my data looks like the following
1st tab
1/8/2013 - 1/14/2013
1/29/2013 - 2/4/2013
1/22/2013 - 1/28/2013
[Code].....
I have a named range BGl (G1:R1). I would like to see if a cell address ($U$6) is within the columns of that range. I need a function to give me a true or false based on changing cell addresses.
View 5 Replies View RelatedI have a column on sheet 1 which contains 200 lines with a unique number in each row (account codes). I have another sheet (sheet 2) which contains the same column. sometimes an extra row for an extra account code is added to the col in sheet 1. I need to make sure that the col I have in sheet 2 always has exactly the same as the col in sheet one, where the same account code should never be repeated. I am currently trying to setup a sheet where I keep all the checks and controls.
View 7 Replies View RelatedSo I have two columns, in column a, i have a list of zip codes and in column b i have a larger list of zip codes. is it possible to find what zip codes in column a appear in column b, and then output those zips?
View 1 Replies View RelatedI have an excel document with hundreds of columns. Each column should not be empty; they must have at least one value. Right now I'm applying a filter and going through each column one by one to make sure that it is not empty. How can I perform this check without having to go through each column one by one?
View 6 Replies View RelatedI need to make a excel costing model to calculate various products prices. I am using Excel 2010.
I have many products and do not need all of them displaying at the same time, therefor I inserted a Checkbox (ActiveX Control) that hides my columns that I do not need. I did that by inserting the following and it works fine :
[Code].....
The problem is I now need to lock certain cells so that they can remain fixed and the recipe cannot change.
Once locked my checkbox no longer works and I get the following error :
"Run-time error '1004': Unable to set hidden property of the range class"...
I have a cell in my spreadsheet that is used to designate if the row needs to be hidden or not. If the user enter's "Y", the row is hidden. I need to check some of the previous cells for entered data before the row is hidden. If data is not entered in these cells I want the empty cell(s) to be filled with a color. The user will then have to enter a value into all the correct cells before the function to hide the row will work. What is the best way to do this? I am using the following function to check for the "Y" and hide the row.
View 11 Replies View RelatedCheck and format some data. I have 100,000+ rows so kinda need a macro to do it.
I have data like this
Sheet1 ABCDEFG1start_date start_time end_date end_timeTypeSOC Before SOC After
208/01/201218:36:2008/01/201218:40:00T 308/01/201218:45:1208/01/201218:50:44T
408/01/201218:52:1208/01/201219:50:46C3446508/01/201219:54:2708/01/201221:44:40C
[Code] .....
I'm trying to merge all the "C" rows in Column F into one row, like this:
Sheet1 ABCDEFG14start_date start_time end_date end_timeTypeSOC Before SOC After
1508/01/201218:36:2008/01/201218:40:00T 1608/01/201218:45:1208/01/201218:50:44T
1708/01/201218:52:1208/01/201223:02:00C341001809/01/201208:16:5109/01/201208:44:08T
1909/01/201208:45:1109/01/201208:49:55T
So you would just keep the first row,but replace the 'end date' and 'end time' with the 'end date' and 'end time' from the last row and same with the SOC.
I have the following macro which:
- opens a database checks each sheet in the database for a contract number,
- highlights the row in which it finds the contract and updates the database with claim number etc (not important)
- returns information to another sheet from the database
The macro works fine BUT I have recently realised that if the contract has already been paid out it will simple overwrite the information and I will not be aware that the contract had already been paid out.
I need the macro to return a value of "Already claimed" instead of "Found in Database" only if the contract number has already been updated.
Sub DataBaseCheck()
' Designed to check Database for claims issued each month
' Open Data Base file to facilitate matching process.
' Check for matching data
' Return checking data to spreadsheet & Update Database
Dim currentfile As String
Dim WS As Worksheet
Dim R As Range
Dim Myvalue As String
Dim Myrange As Range
Dim Tcell As Range
I am looking a program to check if any data be duplicated to avoid any mistakes.
There are many program for duplicate check but they all only compare the cells but not to check the data within any cells one by one.
Is there any program to check the duplicated data in the selected column, columns or cells?
A workbook contains two worksheets. The first sheet is a membership list (Mem06) containing adresses and telephone numbers. The second sheet is a list of activity groups (Groups) with members details who are attending. There are 28 activity groups whose venues are scattered over 20 miles, each has a Leader. It is difficult for the Leader to know if those attending are paid up members, we know that some are lapsed. The solution is to get those members attending to complete a form with their name and 1st line of address. The membership secretary enters the names in the Group sheet (1st line of address only required when people have the same First and Surname).
We would like a formula to check this entry against the list in Mem06. If the entry corresponds the Telephone number for that entry would be returned to the Group sheet. Col A in both sheets has a formula to Concatenate First Name and Surname. The Telephone number in Mem06 is at Col I. The required formula would be in the Group sheet in the Telephone number col.
If my cell range in Sheet 2, K2 to K25 is empty/blank, then I want to fill that blank range with the same number/Value. It can be a 0 or a 1 or any other Value as required. But an Error 13. Type Mismatch message appears. Where is the error if I'm trying to fill the range with a Value if blank ?
Sub Macro_2()
Sheets("Sheet2").Select
Sheets("Sheet2").Range("K2").Select
If Sheets("Sheet2").Range("K2:K25").Value = "" Then
Sheets("Sheet2").Range("K2:K25").Value = "1"
End If
Sheets("Sheet3").Select
End Sub
My company uses 4 types of barcodes 8, 12, 13, & 14 number barcodes for our products my problem is that I can't figure out how to force the barcode to format properly no matter how somebody enters it if they don't use spaces or put them in the wrong spots, I can't use custom formats because there is 4 different layouts
8 digit should be "#### ####"
12 digit should be "###### ######"
13 digit should be "# ###### ######"
14 digit should be "# ## ##### ######"
these barcodes are in columns L, M, & N also right now 'm using a formula in another cell to verify the barcodes by calculating the check digit and comparing it to the check digit typed the formula i'm using is
I have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.
Code:
Sub Tester()
Dim WB As Workbook
Dim WS As Worksheets
Dim modCounter As Long
Dim Cell As Range
Set WB = Workbook("Transverse Series.xlsm")
Set WS = WB.Sheets(BM18)
[Code] ......
I have a workbook that has worksheeets for every day of the month. The data in the worksheet consists of columns (employee #, name, clock in/out times, and break penalty).
What I am trying to do is create another worksheet that searches all the other worksheets for a "yes" in the "break penalty" column and then create a list of all the employees that received a break penalty for the entire month. I would like this to be able to auto populate throughout the month as data is entered and not have to use a filter every time I want to compile this list.
So let's say I have a table like this:
Apple
Banana
Cherry
Banana
Durian
And when I tick one row:
Apple
Banana
Cherry
Banana
✓
Durian
The rest of rows of same values will also be ticked:
Apple
Banana
✓
Cherry
Banana
✓
Durian
I would like to have the macro/VBA for this.
This question was also posted at:
ExcelForum: [URL]...
MrExcel: [URL]....