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.
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."
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
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
[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.
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 RelatedI 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?
I have a database with 2 un identical columns: A, and B. Each cell in Column A should have an equivelant cell somewhere in column B. I want a code that could compare each cell in column A with each cell in Column B. Cells in A that don't have equivelant in B should have their values printed in column C.
View 4 Replies View RelatedI would like to compare some keyword lists if possible.
I have a large list of Unique keywords in Col A (From A3),
This Column is called Keyword List A - Large"
I then have a keyword list in Column C (From Cell C3),,
This Column is called "Keyword List B - Small.
I then have a column called "Unique Keywords Found",, This is Col E,, with hopefully returned results being entered from cell E3 downwards.
What I would like to be able to do if possible is run a Macro that would compare all the unique words in ColA and C and return only the difference, (The Unique words not found in ColC as Col A is the "Master List")
If possible could a pop up box appear saying
===================
List A No Rows:xyz
List B No Rows: xyz
No of Uniques Found : xyz
Time Elapsed (sec): xyz
===================
I'm running windows XP and Excel 2007.
Here's how my daily report is currently set up:
ExternalInternalDifferences
123000123000
234564234564
345456345456
456567378987
565456456567
654547524564
788879565456
865478654547
745654
788879
865478
The External column contains data provided by an external vendor. The Internal column contains data compiled internally. The data in the Internal column will always contain data duplicate to the External column, but will also contain unique data not present in the External column. I would normally cut out the data contained in Internal column that is not duplicated in the External column, and paste it into the Differences column, and move the remaining cells in the Internal column up. The result would be the External column data and Internal column data would align by row, and the data unique to the Internal column is segregated to the Differences column, like so:
ExternalInternalDifferences
123000123000378987
234564234564524564
345456345456745654
456567456567
565456565456
654547654547
788879788879
865478865478
While not a difficult task for 8 rows of data, the actual daily report contains over 1000 rows, on average. I would like to set up a template workbook, where I can simply open the template workbook, paste the data into both the External and Internal columns, then have the values that are unique to the Internal column extracted from the Internal column and inserted into the Differences column.
The end product would be used daily, with differing amounts of data (from 100 rows to 10,000 rows).
I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.
BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34
AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
I'm trying to compile a VBA that would allow me to compare 2 columns "A" in different worksheets (same Workbook) and output any unique values to 3rd worksheet together with the rest of the values in the corresponding row.
Sheet1
A
B
C
[Code]....
Excel 2010
I have a data table that repeats as follows:
CITI
SCB
SCB
SCB
SCB
SCB
SCB
SCB
RBC
RBC
RBC
RBC
From the data above I need to make a new unqie list that would grab all unique entries.
CITI
SCB
RBC
I am trying to use the following guide:
http://blogs.techrepublic.com.com/howdoi/?p=111
Get stuck on "Listing A:
I have 36 cells that will have multiple letter values. The cells in the range (MyRange) in question, have formulas in them. I will be transferring this into a cell range that has over 6000 cells, but I'll worry about that when I get this part down. :D I have 07 excel but this is in the older versions.
View 14 Replies View RelatedI have successfully loaded a combo box lookup in one cell referencing a column in another worksheet. While the drop down is pulling in all the names listed, there are duplicates in the data set. I want the drop down to only pull unique names. Is there a way I can do this and still use the combo box feature.
View 3 Replies View RelatedI would like to create a macro which will look at columns A,B and C. Based on A,B and C, I would like the macro to return only the unique combinations. At the same time, I would like columns D and E to perform a sum.
This is the original sheet:
******** ******************** ************************************************************************>Microsoft Excel - Trial19.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE1E2E3=
ABCDE1A1YES1032B2YES1013C3NO1034D4MAYBE1015D4MAYBE101NJSS*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
This is what the sheet will look like once the macro I would like to create is executed.
******** ******************** ************************************************************************>Microsoft Excel - Trial19.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE1E2E3=
ABCDE1A1YES1032B2YES1013C3NO1034D4MAYBE202NJSS*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
As you can see, the highlighted areas denotes the changes that were executed through the macro.
I have attached a sample workbook to try and explain what I'm after.
Basically I want to sum the amount of occasions a person has had checks and each occasion could have 1,2 or 3 individual checks.
I have a list of names in column A these names repeat themselves a lot and I don't know how many there are.
now I want to make a list of the names in column c starting at C2 that looks down row A finds names and puts them in column C one after the other.
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.
Couting Unique Entries
I've included a sample worksheet ...
I have a list with many names, several of them appearing more than once. I would like to extract the unique entries an then make the list of them.
I can do that using the filter. But can I do it with formulas ? (In order to, whaterever change is made to the first list, get the extracted one immediately updated).
I was wondering if there might be a better way to write this macro. What it does is clears unique items from a Range( leaves duplicates ) I've looked all over the net I can find all kinds of function and subs to remove duplicates but haven't been able to find anything that just removes single entries. I"ll bet there's a more elegant way to write this maybe using a Collection or a Dictionary.
Sub Dummy()
Dim MP1_Rnge As Range
Set MP1_Rnge = Range("A1:A100")
For Each Cell In MP1_Rnge
If Not IsEmpty(Cell) Then
If Cell.Row = 1 Then..........
I have the below table of data, in Column A there is a list of entries and there may be duplicates in them, what I want to do in column B is be able to list all the unique entries in there and basically remove any duplicates.
AB1DETAILED LISTUNIQUE LIST2Holden 3Ford 4BMW 5Mercedes 6Ford 7Ford 8Audi 9BMW 10Holden 11Ferarri 12Holden 13BMW 14Toyota 15Toyota
I have a worksheet, where in C3:C100 (or even higher) i will have text strings. A lot of these will be repeated, and i want to pick out just one instance of each text string and display it in E3:E10 (or higher).
So, for example, if i had the following values in column C:One
Two
Two
One
One
Three
Seven
Two
Four
Seven
One
Three
I would want the following values in column E:One
Two
Three
Seven
Four
I am using excel 2007.
I am trying to find the sum of unique entries in a table such as below for each respective date.
DateClient1/01/2010ABN1/01/2010ABN2/01/2010BNP2/01/2010HSBC3/01/2010ABN4/01/2010BNP4/01/2010HSBC4/01/2010ABN5/01/2010BHP
The results should appear as
Count Unique1/01/201012/01/201023/01/201014/01/201035/01/20101
What formula would I use to calculate this unique count?
I have this 2 columns, A(region) and B(location), and I need a macro to filter the unique entries in Columns A and return both the unique values in A and their corresponding values in B. I've attached a file on it with sheet 1 having the data to be worked on and sheet 2 being the output that I require.
View 7 Replies View RelatedI'm trying to write an Excel program to calculate golf handicaps. In column A, I have 20 unique dates. In column B, I have corresponding 20 scores. In column C, I'm trying to create a corresponding notational mark, "*", indicating the 10 lowest scores that are also the oldest. However, I'm running into problems as I may have duplicate scores with different dates, i.e. 3/22/06 will have a score of 85 and will be the 10th lowest score, but 4/1/06 also has a score of 85.
View 9 Replies View RelatedI am hoping this can be done with formulae. Starting at C7 and continuing down the C column there is a list of names which could potentially run from C7 to C5000. This list of names will contain duplicates. For each name there is a corresponding 'reason' in the F column which will contain the word 'Truancy' or 'Late'.
I need a formula that can count the number of UNIQUE names in the C column which correspond to the word 'Truancy' or 'Late' in the E column.
An example,
[Name].....................[Reason]
C7 ...........................E7
John Potts.................Truancy 2
Matt Jones................Truancy 10
John Potts.................Truancy 4
Matt Jones ...............Late AM
Pete Burns................Late PM
Pete Burns ...............Late Both
Steve Lopez..............Truancy 6
Count of unique names with the word Truancy in the corresponding E column = 3 [John Potts has 2 instances of the word truancy in column E but this is only counted once]
Count of unique name with the Word Late in the corresponding E column = 2 [Pete Burns has 2 instances of the word late in the E column but this is only counted once].
I have also included a sample workbook.
I have on inventory sheet that has all the data in each cell 2612 to be exact! That changes month to month with deletions.
The format is: Sheet 2
A/B/
334702/UEMR88QX
334703/UEMR85QX
334704/UEMR81QX
334707/UEMR8JQX
The master worksheet has about 5000 items and the (A and B) data are both on it with other data ranging from (A-Y). My question is how do I have a cell look up data and return that it exists or doesn’t exist on the inventory sheet?
Master
A/B/C/D/E/
35/ 465/881676311350/311350/UEMR8ZTU
36/469/881676310722/310722/UEMR8V5V
37/483/881676310924/310924/UEMR8XYU
38/805/881676331096/331096/UEMR8J13
My other problem I need to take that months inventory list and have it look at the master list and return the system number from the master list (B) next to the matching inventory number (C). Allowing me to cross match inventory to master each month and save inventory worksheet each month.
I know I can copy the column and past is as a value so I won’t lose the numbers as the master changes but getting the data onto that months inventory has been a pain.
I have a table like this:
Category 1
Category 1 Topic 1
Category 1
Category 1 Topic 1
Category 1
Category 1 Topic 2
I'm trying to pull each Category once out into 1 column, and each of it's Topics once in another column.