I have 24 cells I want to compare to another 24 cells. I want to compare cell G5, 7, 9, 11, 12, 13, 15, 20, 22, 24, 26, 28, 30, 35, 37, 39, 41, 43, 45, 50, 52, 54, 56, 58, 60 with cell K5, 7, 9, etc. Cell G5 has to be compared to K5, G7 with K7 and so on. All the cells consist of is 1, X or 2. I want excel to count the number of matches and write it in cell G63.
But since I am a total noob when it comes to excel I have no idea how to do it.
I have multiple colums that I want to compare (A=F), (B=G)........
I want to see the difference of the two as shown in Blue column (example file is attached.)
Skærmbillede 2012-12-13 kl. 18.37.15.jpg
In Worksheet 1, Cell B63 I would like to create a drop down menu, with two options for the user to select - 0.05 and 0.01. I would like each selection to then control the formula in the cells C63:L63, for example;
Where 0.05 selected it computes for cell C63:
=IF( COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!N55>'WORKSHEET4'!L55,"YES","NO")
then cell D63
=IF(COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!AC55>'WORKSHEET4'!AA55,"YES","NO")
and so on
Then if 0.01 selected it would compute for Cell C63
=IF(COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!N55>'WORKSHEET4'!K55,"YES","NO")
then cell D63
=IF(COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!AC55>'WORKSHEET4'!Z55,"YES","NO")
Need vba to do the following: Compare 4 Columns for a 100% match: B, F, N, K.
IF ALL 4 columns] contain matches then, it is considered 100% match and can proceed with other code.
'Below is an example of how it should perform the COMPARISON:
'1. Compare what NSN's exists in B3 and B4:
'...If = MATCH, proceed to check critical column F for matches
'...If = NO MATCH, proceed with 100% FAIL rule.
'2. Compare what NSN's exists in F3 and F4:
'...If = MATCH, proceed to check critical column N for matches
'...If = NO MATCH, proceed with 100% FAIL rule.
'3. Compare what NSN's exists in N3 and N4:
'...If = MATCH, proceed to check critical column K for matches
'...If = NO MATCH, proceed with 100% FAIL rule.
4. Compare what NSN's exists in K3 and K4:
...If = MATCH, proceed to the set of rules used to perform updated and merging to these 2 rows.
...If = NO MATCH, proceed with 100% FAIL rule.
PROVIDE MESSAGE:
IF all four match, THEN,
MsgBox ("100% Match")
IF at any point along the way, one of those columns does NOT match, THEN
MsgBox ("NOT a 100% Match")
(for example, if it looks at Col B - finds match, it then moves to F and if F does not match, stop and provide message (no need to continue comparing the last 2 columns)...
I have two cells with values that are strings.
I would like to know of a macro that can compare each two cells(start from E2 and compare with E3)to see if the values are NOT equal.
If they are equal, do nothing and if they are NOT, highlight the value.
Example:
compare E2&E3, E4,E5,E6,E7,.... if for example E2&E3, E4,E5,E6,E7,.... each hat same string, do nothing, but if they were not the same highlight it with red.
This code checks two cells and highlights one cell when there is duplicate, I like the opposite one, but don't know what to change!
Code:
Sub CompareCells() Dim r As Range, cell As Range Range
("E2", Range("E" & Rows.Count).End(xlUp)).Name = ("MyRange") Range("MyRange").Select
' clear all colors from selection Selection.Interior.ColorIndex = xlNone
' loop through cells and compare Set r = Selection Set r = Selection.Resize(r.Rows.Count + 1)
For Each cell In r If cell.Offset(1, 0).Value cell.Value Then
cell.Offset(1, 0).Interior.ColorIndex = 3 End If Next End Sub
I am trying to compare two cells and if they match then I want to fill in a list on sheet one from sheet two.
I have the absolute working when I compare one cell to one cell, but I can't get it to compare one cell to a range of cells (two weeks of days)
Here is the absolute one I have working:
=IF(C1='Data Sorted'!A1,'Data Sorted'!A2,"")
Here is what I tried that doesn't work:
=IF(C1='Data Sorted'!A1:N1,'Data Sorted'!A2,"")
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
i have weights entered in A1 , B1 , C1 and I manually enter almost same weight in D1, E1, F1 . Now I need the result as " OK " in G1 if the weights A1,B1,C1 match with D1,E1,F1 if it doesn't match then result should give "Please check again".
I have 2 sheet
1-"Check Employee Hours"
2-"Check Hours"
Below is what Check Employee Hours looks like
Below is what Check Hours looks like
I want to the user to press ebutton i.e Sunday and macro does the comparison.
Process
I would like macro to look at sheet "Check Hours" column "E" (cell E4) and compare it against the sheet "Check Employee Hours" column "E" (cell E7). If the hours matches i would like to say OK on sheet "Check Hours" cell E4, if it doesn't match then say CHECK.
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 want to compare the colour column in book1 to the colour column in book 2. If I find a match I want to take the id from book1 and port them over to book2. How do I go about doing this?
View 10 Replies View RelatedI need to compare names in column A(sheet 1) to names in column A(sheet2) and if there is a match then I need the date from column B(sheet 2) to be copied to column B(sheet1).
Example of the sheets:
Sheet 1:
Name
Food Safety
Smith,John
Walker,James
Stratton,William
Sheet 2:
Name
Food Safety
Christian, Marie
03/03/1994
Harker, Stuart
01/06/1995
Walker, James
05/06/1997
If i has this table name delivery
Table name is delivery and goes from a to p and 1 to 621.
I've been trying for a few time to make a sub, on VBA, that writes, in a specific column, " " or 1 depending on the date that is inserted. The thing I can't quite do is:
1 - I have a sheet where the different projects of a company are stored (name, starting date, ending date and person responsible) and where new projects can be added from a UserForm
2 - I also have another sheet were costs from the projects are stored (one line with cost 1 from project 1, another with cost 2 also from project 1 and cost 3 from project 2, etc) and where I can also add a new cost with another UserForm (this cost as also a date that is added, to when it happened)
3 - What I need to do is, on the costs' sheet I need to create a sub that in the last column adds me 1 if the date of the cost added is not a date between the starting and ending date of the project that it corresponds.
I have 2 columns of usernames A and B, A has approx 700 usernames and B has about 80. In column C I have a list of names 1 for each username in column A. I want to compare the B with A and if there is a match return the value in C and place it in D.
View 3 Replies View RelatedI have column A of 700 usernames and column b with 85, I want to compare b with a and if appears in the list place a 1 in column C
View 1 Replies View RelatedI have two spreadsheets SP1 and SP2. I need to match values in column E of SP1 with values in column A of SP2. If I find a match in SP2, I need to find a value starting with 'TC_' from the previous rows and get the corresponding value in column B of SP2 and paste in column F of SP1.
For example, E1 value in SP1 matches with A3 value in SP2. So I need to find the row with value TC_AM01_axs_fhgn and get the corresponding value in column B AM01 and copy it in column F of SP1.
The two example spreadsheets are given below. The values in SP1 sheet has expected result in column F in red.
SP1
SeriesS.NoItem #CaseReuse CompScenario #A1abc001_CTC001AM01B2bcd002_ATC002AM02C3def003_BTC003AM03D4efg004_DTC004AM04E5ghi005_ETC005F6hij006_FTC006G7lmn007_GTC007H8opq008_HTC008I9rst009_ITC009J10uvw010_JTC010K11xyz011_KTC011
SP2
Case #SubjectTC_AM01_axs_fhgnAM01TC001asdhagsdjharteuTC_AM02_axs_fhgnAM02TC002asdhagsdjharteu12sdfsiu786dfgg88hdcfs676566532kjjjiTC_AM03_axs_fhgnAM03TC003asdhagsdjharteuTC_AM04_axs_fhgnAM04TC004asdhagsdjharteu
I'm writing quite a large electronic logging program using excel and vba and have hit a snag in one of my subs which reads from a text init file. Let's take a look at the code.
Private Sub ReadInit()
'Opens txt init file, looks for target list, splits line by commas and enters into array
Dim FileNumber As Integer
Dim TargetsRead As Boolean, LastFileRead As Boolean
Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = ObjFSO.OpenTextFile(SaveDir & Init)
TargetsRead = False
LastFileRead = False
Do Until objFile.AtEndOfStream 'do until end of file
I have a list of names on 2 separate sheets that I need to modify. If I have a name on sheet1 matches a name on sheet2 I would Like to add a "space" then "(SV1) at the end of the text string on sheet2. I have a list of 1500 and will only have to modify 75-100. Example shows on sheet2 how I would like the desired outcome to be.
View 3 Replies View RelatedI am not a programer by trade so I can't figure out what I need to do to solve my problem. I need to compare two worksheets to each other and only keep the rows of data that match. I am comparing address's in each worketsheet with each of them located in column C in each worksheet.
View 4 Replies View Relatedthe table look as follows;..........
Meaning. Cell A1 (A) gets a match then checks in the same row on column two matching with another table B1 (2).. if both cells in both columns are matched, then C1 (55) will be returned. =INDEX(C:C;MATCH(A1;A:A;0);MATCH(A2;B:B;0)). I can sort the whole table and use vlookup, but how can i make it without sorting ?
I am trying to compare two fields using the INDEX MATCH formula. One field needs to match exactly and the second field should return the closest match to a date. I have attached an example where I am trying to find the closest production date (column F) for the matching ID that is before the complaint date.
View 8 Replies View RelatedI must match column A and column B to return the column C In colA there are all my countries In column B there are all my countries code and countries I want get the colC where all my colB match than colA.
View 14 Replies View RelatedI have a sheet (name “master”) that I need to compare against data in a table. For each row, I am looking at two columns of information that I need to compare against the data table. The columns are (1) “State of Sale” and (2) “Associate ID”. I need to determine if the associate is allowed to make sales in the state that is identified, which requires looking at the data sheet.
This steps I need to take are as follows:
1 – grab the associate ID and determine if it is in the data sheet. If the ID is not in the sheet, then “No record found”. Else, go to next step
2 – if the ID was found in the data sheet, then the next step is to grab the “State of Sale” and compare against the data table. Go to the row of ID in question, and then go to the column of the State code in question. If there is an “x” in the intersecting cell, then sale is “ok”. If there is not an “x”, then “sales violation”.
I would like a macro that can match column A & column B data of Worksheet(WeeklyJob) to column A & column B of Worksheet(Master) then if match is found copy column C through column F into Worksheet(Master) column C through F (and overwrite any [outdated] existing data there may be in those columns [thus updating the job's weekly charges, etc.]).
If match is not found I would like it to copy entire row from Worksheet(WeeklyJobs) into first blank row at end of Worksheet(Master) (thus giving me a new record of a new job from the weekly report).
All the columns in both worksheets are labeled the same (& row 1 is headings).
I have 2 workbooks each with last name columns. One book has a column of a few hundred names and the other has a column of over a thousand. I need to compare the last names and pull out the employeeID (which is in a seperate column) to a specific column for the ones that match.
View 2 Replies View RelatedTrying to compare data from two spread sheets, if there is similar data in column a, output all data (sheet1 & 2) to the 3rd sheet.
Sheet1
a b c d
smith john 888 cicero
king larry 123 syracuse
Sheet2
a b c d
smith marge 777 liverpool
king mike 458 dewitt
Sheet3
a b c d a b c d
smith john 888 cicero smith marge 777 liverpool
king larry 123 syracuse king mike 458 dewitt
The closest thing I can find to what I'm trying to accomplish is this link here:
Compare Worksheets
I'm also would like the ability to change the columns I am comparing. The actual sheets may have more that 4 columns.I can somewhat read the formulas but have a hard time under standing them completely.
I am trying to have a macro to take a value from a text box and see if it is matches a value that is in a specified range. If it comes back true then it shows an error message. This code is working for text values, but if it is numeric it will not find find it.
Sub SearchForMatch()
'Procedure level declarations
Dim rngCell As Range
Dim bMatch As Boolean
' Loop through each cell in the nominated range
For Each rngCell In Range("B4:B50")
'If there us a matching value then set your
'boolean flag to true and exit the loop
If rngCell.Value = TextBox1.Value Then
bMatch = True
Exit For
End If
Next
'If a match was found then alert the user
If bMatch Then MsgBox ("Name already exsists.")
End Sub
If 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 RelatedLook at the attachment below. I would like to compare two data sets;
DCLG Codes and our internal codes (all highlighted in the colour peach).
One or more of our codes can 'feed into' our parent organizations codes. Please see the Summary sheet and it will become apparent.
At the moment, I have been able to remove the duplicates from the DCLG codes and then 'marry them up' using a SUMIF function to add up all our ledger types- according to their codes.
Basically, the bottom line is that all our spending or income (The ledger Types) has to be grouped (if more than one) and reported back into their system codes.
Here's the more, possibly challenging bit....
Our system generates the ledger types and the monthly spend and year to date every month. from time to time, different ledger types may be added to the table (this will always be from our end).
We would like a way to copy and paste this data next to their table without throwing out the tables or distorting the codes that they relate to on a permanent basis.
My question/s is;
Is there a way to match and validate the data and put in an alert to notify us of the error? And then when we are happy, we could carry on as normal and generate the SUMMARY sheet.
I know of the data validation method and looking for an exact match but I was wondering if the corresponding figures would be affected in anyway.
What may be good would be to have a data dump/input area where the data is matched possibly via a VLOOKUP and anything that is not matched is displayed at the bottom of the list?
The results do not necessarily have to be like what I've suggested above. all I would like to be able to do is match the data first (and their corresponding figures) and output according to their codes. I need to be able to identify new or unmatched Ledger types.
And a report : DCLG Workbook Template Master.xlsx‎
