Compare Cells In 2 Columns For Incorrect Entries

Sep 8, 2007

I have two columns that use data validation. Column-F uses a dynamic named list in the validation and column-G validation is set to allow a decimal entry only. I need a third form of validation to compare the two values and an message when there is an error.

If F1="None" then the required entry in G1 must be 0 (zero). If F1 <> "None", then G1 entry can be any value greater than zero.
What I would like is a message box to pop-up explaining the error when F1="None" and G1>0.

I'm using a helper column-K that concatenates the cell values (in F:G) into a single string that I can check. Not having much success, but what I've tried so far is:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myrange, mycell As Range

On Error Resume Next
Set myrange = Sheets("Copper Data").Range("K10", Range("K65536").End(xlUp))
For Each mycell In myrange
If mycell.Value = "No Scrap0" Then MsgBox ("You entered a scrap reason of 'No Scrap' and quantity of 0")
Next mycell

End Sub

It either fires all the time if the helper cell string is "No Scrap0" or it doesn't fire at all if the string is "NoScrap1" (where "1' can be any number).

Currently trying to build this, but my fledgling VBA knowledge has me hamstrung. I'm getting an error of "Next without For').



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim r, c As Range
Dim note As String, Response As Long

note = "Data Entry Error: Scrap Quantity cannot be greater than zero with a reason code of No Scrap." & vbLf & vbLf & _
"Please check the accuracy of your entries."

View 9 Replies


ADVERTISEMENT

Compare The Two Columns And Pull Out The Unique Entries

Jun 1, 2007

I have two columns in Excel.

I want to compare the two columns and pull out the unique entries.

So any entries that are in both column 1 and column 2 I don't want anymore.

I just want all the entries that are NOT IN BOTH columns.

View 11 Replies View Related

Compare Cells In 2 Columns

Apr 30, 2013

I have an excel spreadsheet with multiple columns of options.

In column A, I select either "yes" or "no" to activate other columns.

In column B, I have, let's say, lease terms; 12, 24 & 36.

I'm trying to find a formula that I can enter that will take all the columns I select as yes, and tell me if the lease terms match up.

So, if I select yes in cells A4 & A13, are the lease terms in B4 & B13 the same...do they match?

View 5 Replies View Related

Excel 2010 :: How To Highlight Incorrect Value From 3 Columns

Aug 19, 2013

I have a Excel 2010 spreadsheet that contains 3 columns of (£) values. I am wanting to highlight a cell if one of these values do not match the other 2 columns. e.g below I would need the middle column to be highlighted as it does not match the other 2 cells.

25,449.47
25,451.65
25449.47

The thing to point out is that it can be any of the cells that do not match, not necessarily the middle column and occasionally all cells will not match and all need to be highlighted.

I am sure I need to use the Conditional Formatting, but cannot figure the formula.

View 5 Replies View Related

Compare Cell Contents In Different Columns Then Fill Neighbouring Cells

Jun 9, 2014

I have two sheets:
- sheet 1 ('current')
- sheet 2 ('31May')

The data in column C is the same, although sorted differently and can't be re-sorted to be the same. Where the data in column C is the same in both sheets, I want to fill columns A and B into sheet 1.

View 4 Replies View Related

How To Compare Values Of Duplicated Entries

Dec 18, 2013

I manage messaging server farm that keeps mailboxes and their replicas on different servers for resilience. I need a way to check the replication status. I try to do it by comparing their sizes. I got all mailboxes and replicas incl their sizes imported into excel (2010) and I need a way to compare them.

Like this:
name
size

John Smith
10

Mary Westwood
20

[Code] ......

My idea was to find matching pair (two records of John Smith) and compare size. If the size differs more than specific value (5% or 2MB for example) the result is true/value/colour by conditional formatting etc.

View 4 Replies View Related

Counting Formula (compare Values In 2 Separate Columns To See How Many Times The Same Value Appears In Both Columns)

Oct 13, 2008

I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.

ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678

In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.

View 4 Replies View Related

Macro To Compare Columns A & B And Dispaly Any Duplicates In Columns C & D

Feb 21, 2009

what I'm after is a macro to check the contents of Column 'A' against column 'B' and display any duplicates in Columns 'C' & 'D'.

N.B. The headings of Columns C & D are :-

C = Value Found in Column A

D = Value Found in Column B

Any duplicate entries logged in columns C & D should be listed in C2,C3,C4....C20 and D2,D3,D4......D20 etc (in effect creating two new lists)

View 5 Replies View Related

How To Select Data Entries From 2 Columns

Jul 29, 2014

I have a large spreadsheet of species activity data and want to separate information from two columns to create a bar chart of when a certain species is active over a 24-hour period. There are duplicates of each species in one column and a unique corresponding time of activity for that species in another, something like the following:

Species A 23:01
Species B 07:09
Species C 13:45
Species D 08:30
Species A 01:55
Species A 22:07
Species C 15:29

What formula can I enter that only selects Species A and their corresponding times that I can copy into a new spreadsheet?

View 2 Replies View Related

Taking Entries From Certain Columns And Putting Them Together

Apr 4, 2014

I have a huge spreadsheet where after every 12 columns there are two "special" ones, which contain certain entries. In the first of these two, the entries from the second one are numbered in a certain way. Please see the example.

I need a formula in a separate column (yellow) which will gather all these entries from all columns, and put them in order, as I did manually in this example.

Instead of 4 "groups" of columns, in my real file I have 200 of them, with 400 rows each, which makes it more complicated..

View 2 Replies View Related

Don't Allow Duplicate Entries Across Multiple Columns

Jan 14, 2010

how i can set 'Data Validation' to stop me from entering duplicate values across multiple columns, i can set it for a single column i.e. A1 to A100 but i am after this but for something like A1 to H100.....?

I am currently using this formula in Data Validation:
=COUNTIF($A$1:$A$100,A1)=1

I need something to do this but for multiple columns.

View 7 Replies View Related

Unique Entries In Each Column Of 620 Columns?

Jun 18, 2012

I have 620 columns of data with each column consisting of 17 rows. I need to be able to count the number of unique entries in each column. I recorded a macro that would do what I wanted, but it just used absolute references for each column, which would make the process rather tedious. Is there a way to make the macro less restrictive?

Sub Macro1()
Columns("B:B").Select
ActiveSheet.Range("$B$1:$B$17").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

View 1 Replies View Related

Duplicate Entries Validation With 3 Columns

Feb 15, 2014

I am looking for either vba or excel method to prevent duplicate entries across multiple columns. The data validation only works when it is input manually by the user and not when it is generated by the formula

What i am trying to do ( assuming " | " are column dividers and each new line is a row)

abc | 2 | bac | 3 | cab | 4

bac | 3 | abc | 2 | cab | 4

abc | 2 | bac | 3 | cab | 4

View 2 Replies View Related

Compare Different Columns Based On One Columns

Jul 30, 2014

2014-07-30_16-52-23.png

Fist I want to compare ColB=ColH, if TRUE, compare colA=colG, if TRUE, again compare colC=colI, IF all this conditions true, then give (colD-colJ) on colL.

all the unmatched rows in 2 tables to populate with different for each table

View 4 Replies View Related

VLookup / Finding Reverse Entries In Two Columns?

Dec 23, 2013

I have a dataset that looks like below -

Header1 Header2 Data
100 101 20.50
102 19
103 1003.2
101 100 20.50
200 300
102 100 20
200 104 30

Now, what i need to do is for each of the rows in my dataset, I need to find the reverse entries (consider first row where header 1 is 100 and header2 is 101, I need to search the entire dataset to find if I have some data for header1 is 101 and header2 is 100). In this case, I do have such data where header1 is 101 and header2 is 100.

Header1 Header2 Data Reverse Entry Difference
100 101 20.50 20.50 0
102 19 20 -1
103 1003.2 Not found Not found
101 100 20.50 20.50 0
200 300 30 270
102 100 20 19 1
200 101 30 300 -270

let me know if that's possible through excel formulas?

I need a column D with such data and column E for differences. So that, my final output should look like below -

View 10 Replies View Related

Comparing Two Columns And Display Not Matched Entries

Jun 17, 2009

I've tried searching for an answer all over with out success. Here's my dilemma. I've got two columns (A and B) with over 1000 entries in each column with computer names. Most of the names are the same however some differ in the way that they might be in column A and not in column B and vice versa. What I would like to achieve is to compare those two columns and display the result in column C and D but keep the names of the in the retrospective column.

What I mean by that is that if a name was found in A but not in B I would like to have it displayed in column D and vice versa. If a name is found in B but not in A, to have it displayed in column C. If there is an easier of doing this, please by all means (i.e. highlight the name in A if missing in B and vice versa).

View 5 Replies View Related

Userform VBA To Check 2 Columns (A And B) For Duplicate Entries

Apr 23, 2014

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 : .....

View 3 Replies View Related

Find Duplicate Data - Identify Which Entries Are In BOTH Columns

Apr 27, 2014

I have data (e.g. PPL0106AU) in columns A (Rows 2 to 104) & C (Rows 2 to 303) and need to identify which entries are in BOTH columns.

I've entered in column B: =IF(MATCH(A2,C$2:C$303),A2,"No")

This formula is resulting in a duplicate of column A in column B.

View 7 Replies View Related

Duplicate Entries In Rows - Add Accompanying Data To New Columns?

Jun 4, 2013

I have attached a before and after image of what I am looking to accomplish.

In the before image, you can see that there are 3 rows of data

- a header

- a repeating model number (in column F) with accompanying data (values in columns G - J are the items of interest)

In the after image, you can see that I took the 3 rows of data and turned it into simply two rows of data

- a header

- the repeating model number

What I did however in the after image, as can be seen, is I took the data that appeared on the repeating row (Row 3 from the Before image), and included the PRICE, COST, BEGIN, and END values in new columns on row 2.

The model number is the same of course, which is why I did it that way.

If the model numbers were different, I would have simply left it alone, as-is.

Is there a way to accomplish this via some functions or maybe even a VBA script?

By the way, if we have to dump all of this onto a new sheet, then so be it.

View 5 Replies View Related

Create List Of Unique Entries Based On Several Columns

Jul 27, 2013

[URL] ......

In that file, you can see there is a list of somewhat similar data across 5 columns. Some of the entries are duplicates, some are different by only one character. What I want to be able to do is generate a list of all the unique rows of data, taking into account the data from ALL 5 columns. The list can be on another sheet or simply just further down on the same sheet, that doesn't matter too much.

I could do it if it were just one column of data, but struggling trying to do it with several columns.

View 7 Replies View Related

Finding Unique Entries Among Two Columns Of Alphanumeric Data

Jun 16, 2006

Columns A & B contain several thousand Project Numbers (e.g., P1052, PA844, etc.). Many of the Project Numbers in column B are the same as in column A, but column B also has additional (i.e., newer) Project Numbers scattered throughout. Ideally, I would like to use a built-in function (versus a custom function if possible) that compares all the Project Numbers in both columns and then separately lists those that are unique to column B.

View 9 Replies View Related

Cross Check Two Different Columns And Output Common Entries?

Jul 12, 2012

So 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 Related

All Number Combinations From Entries In 7 Columns And 3 Rows With No Duplicates?

Dec 2, 2013

I have 7 columns x 3 rows with unique numbers. I wish to determine every combination of the above numbers without repeats.

5
11
17
24
29
35
40

[Code] ........

Column 1 would only use column 1 numbers.
Column 2 would only use column 2 numbers...etc.

Also have the same for 5 rows of unique numbers.

I've been working on this for 10 years and this is the last step to finalizing the WINNING numbers.

Back checking using the 5 row technique I would have spent 10,000,000.00 playing over 8 years with a winners purse of 33,000,000.00

With the 3 row I would have spent close to 200k and won 2.5 mil.

Only problem is that I don't have the 10 to start with, the 200k is a maybe .

I need to be able to generate the combinations to play the numbers.

View 9 Replies View Related

Count Number Of Rows With Unique Entries In 2 Columns

Oct 9, 2006

I have a spreadsheet which is to record quality checks on work carried out by staff. The spreadsheet has a customer reference number in column B and a Staff reference number in column C.

I can carry out a number of checks on a member of staff on one transaction, so for instance, I could carry 3 checks on one customer number, which would result in the staff ref number being enetered 3 times (there is 1 check per row).

I need a formula to count the number of checks I carry out on each member of staff. My problem is that although 3 checks could be completed on someone, if it is on the same customer NO, it only counts as 1 check. In effect, I need a formula to count the number of staff ref numbers which have a unique customer number eneterd in the adjacent column.

All the cust numbers are unique so would I be able to use a wildcard?

View 4 Replies View Related

Compare Two Set Of Columns?

Jul 26, 2013

I need to compare add Column A&B in sheet1 and column A&B in sheet two and compare the two result for the difference(like vlookup).

formula to merge two cells in two sheet and compare.

View 3 Replies View Related

How To Compare 2 Columns

Apr 18, 2007

i've check the other threads on how to compare two columns but for some reason it is still not working for me.

I have 2 columns. Column A is the master column and column B is the comparing list. If the company that is listed in Column B (Anywhere in column B) matches exactly with the company name in column A, I would like it to say "check" in column C.

View 9 Replies View Related

Compare Columns

Jan 23, 2009

I have two diffrent sets of columns of data on a spreadsheet one column set is A,B the other is D,E I want a formula or makro that will check columns A,B and compare it to D,E and if it finds a match will paste it in G,H is that possible and how would I do this?

View 9 Replies View Related

Compare Value In Columns

Dec 12, 2006

I have two worksheets but have a same column named "Admin No.". I will need to compare the 2 "admin no." column.

Example:

Worksheet 1
Admin No.Name
1111Ali
2222James
3333Janet
4444Lihua
5555Ting Ting
6666Jasmine

Worksheet 2

Admin No.Name
1100Alvin
2200John
3300Mavis
4411Haoyi
5555Ting Ting
6677Jason

I will ike to compare both "admin no." column and when worksheet 1 5555 can be find in worksheet 2, it will return True. 6666 cannot find in worksheet 2 then it will return false.

View 4 Replies View Related

How To Compare Columns To Get Minimum Value

Apr 14, 2014

I have numeric value in columns B, D, F, H.

I want to compare the values of these four cells in each row and update columns M (with the minimum value) and N (with the column of minimum value).

How can I manage this comparison?

View 2 Replies View Related

Compare Two Columns And Result In The Third?

Apr 30, 2014

I need to compare two columns A and B with a result in column E. BUT I don't want the result to be added together. ( since it's receipt numbers ) SO my formula is incorrect I need a formula that will show the receipt numbers next to each other divided by a comma or forward slash...

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved