VBA - Compare 2 Cells For Positive Match?
Nov 7, 2013
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)...
View 5 Replies
ADVERTISEMENT
Jun 5, 2008
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.
View 9 Replies
View Related
Jan 3, 2013
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
View 3 Replies
View Related
Oct 19, 2012
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,"")
View 6 Replies
View Related
Aug 27, 2013
I want to compare 2 columns based on their positive and negative values..
For example,
A B C
ID1 91 -7
ID2 -7 74
ID3 -4 -5
ID4 90 4
ID5 -55 34
ID6 33 3
I want to know 1) which id's have both negative values in both columns B & C, 2) which id's have both positive values in both columns B & C, and which id's have opposite signs in both columns B & C.
I want to have a sum of each one of that..
View 5 Replies
View Related
Sep 22, 2009
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
View 5 Replies
View Related
Jul 12, 2014
If i want to sum A1+ A2 and A2 containes a negative number i only want to sum A1.
Like this:
Example 1
A1,A2
3,3
3+3 = 6 (Both cells contains positve number)
Exampel 2
A1, A2
5, -3 = 5 (A2 contains negative number only sum cell A1)
View 6 Replies
View Related
Nov 4, 2008
I have a code below to only allow a number to be entered into one of 2 cells. I'm trying to do conditional formatting based on these to cells, and the evidence is showing that even when I type a value into either I21 or I22, they are taking on a value of zero.
I have conditional formatting stating that if I21>0, then do one thing. And in a separate cell, I have =I22>0, then do another thing. Neither works, and even using =I22<>0, then do formatting, and it doesn't work, telling me that the assumed value is always zero.
View 4 Replies
View Related
Mar 15, 2011
I have two columns of data, which are constantly being added to. Column A is a unique name (no duplicates) and column B is its corresponding value. In many instances the value in column B is zero. I want to produce a list in column C with the names that have a positive value which will update automatically as names and values are added to column A and B. Is this possible?
View 6 Replies
View Related
Nov 12, 2009
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 Related
Apr 9, 2008
Two columns contain data (cash amount) which should be the same. If there is a difference, I need to show a minus value in a different cell and a plus value in another cell.
View 4 Replies
View Related
May 17, 2008
VBA code to change a cells value in a column and the adjacent column to the right of it. If the column has a negative number in it, I need to put the absolute value of that number into the next cell over on the right. I then need to change the original column values to 0. I've added a spreadsheet that shows an example and the desired output.
View 3 Replies
View Related
Oct 16, 2012
I 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
View 9 Replies
View Related
Mar 5, 2014
If i has this table name delivery
Table name is delivery and goes from a to p and 1 to 621.
View 5 Replies
View Related
Mar 19, 2014
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.
View 1 Replies
View Related
Jul 4, 2013
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 Related
Jul 4, 2013
I 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 Related
Mar 27, 2009
I 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
View 10 Replies
View Related
Dec 16, 2006
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
View 4 Replies
View Related
Apr 10, 2007
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 Related
Jun 29, 2007
I 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 Related
Nov 11, 2009
I have a 'rota' worksheet that includes staff names and their rostered hours on all dates through the month.
On each date of the month I need excel to find that date in column A and then look across the row to find any cells that aren't blank. When the cell has a value I need the name above it in row 1 and the value (number of hours) itself to copy across to a 'daily activities' sheet. The date is autopopulating on this sheet in cell H5.
The name needs to drop into column A and the hours worked into column B. I would like the first cell to be A9 and then down from there with no spaces in between names. The other info on the sheet will then complete itself using the name that has been dropped in using VLookup.
I'm presuming I can use a macro to do this for me but am really struggling where to start with it, and how to ignore blank cells as they will change cells on a monthly basis.
View 7 Replies
View Related
May 6, 2014
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 Related
Mar 5, 2014
I 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 Related
Dec 16, 2009
I 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”.
View 2 Replies
View Related
Jun 17, 2008
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).
View 9 Replies
View Related
Jun 13, 2006
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 Related
Jan 11, 2008
Trying 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.
View 4 Replies
View Related
Aug 7, 2014
Look 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‎
View 4 Replies
View Related
Aug 2, 2006
i have two columns in tow seperate workbooks to be compared:
in say inp.xls, there is a column(COL A) containting a lot of entries(numbers) - roughly around 60,000. Now in the other workbook, say mem.xls, i have a smaller set of numbers in a colum(COL B) - roughly around 300.
Now, the problem is, COL B, has numbers which may or may not match with numbers in COL A.
* If there is a match(between COL A and COL B), the row number at the match in COL A.
* If there is no match, the row of the closest number (in COL A) before to the number in COL B.
Example:
ROW COL A-----> COL B---------->
1 2184058252 2184276560
2 2184058280
3 2184058296
4 2184058312
5 2184058320
6 2184773640
7 2184774216
8 2184774272
9 2184774424
Now, i want to find 2184276560 in COLA, since it is not there, i want it to return the row number of the closest(previous) viz. row 5(2184058320).
Now, since this is a huge comparision set, im wondering what is the best way to do it. Im using VBA....
View 9 Replies
View Related
