I have two columns that is use to record last name first name. I need a macro that will find matching names in the columns. i have found some examples for searching one column but have not had any luck finding someway to do 2 columns. in the example below i need it to find and highlight The names Smith John.
View 3 RepliesMagazine subscription list. How to highlight the customers that are already in the sheet if enter them again (renewal). Our list is like so....
ColA ColB ColC ColD ColE ColF
First Last 123 Ave City State Zip
Is there a way to highlight the row if the info on ColA, ColB, ColE, and ColF all match? Sometimes the Street info is abbreviated or entered PO Box instread of P.O. Box and they wind up on the list a second time.
I would like to check column B and if any of these values match values in column J hightlight the cell on the same row as the match on column E green. I don't know much about excel but this would be a tremendous help because i'm looking through quite a bit of data. I've tried using conditional formatting and i can't seem to get that to work for my needs. Let me know if you need anymore information.
View 13 Replies View Relatedhow to go about finding sequences and highlighting them.
here's an example list:
7600
7601
7602
7603
7604
7608
7609
7610
7611
now i need to find 3 things in this list.
1) how many 'batches' of sequences there are in this list. The numbers 7600 to 7604 are a consecutive sequence of five numbers followed by another sequence from 7608 to 7611. that makes two sequences. i need a formula for one cell that would specify how many sequences there are in a given list (in this case they're two sequences in this list).
2) is there any formula that can allow me to see each sequence highlighted in a different color (or any way to differentiate the sequences so i can copy paste them easily?). Pretty sure i'd have to use conditional formating on the entire list to do so but am clueless as to the implementation.
3) a formula for a single cell that can state the largest sequence in the list. in the list above the largest seuqnce is 5 numbers so that would be the value i'm looking for.
I need to highlight all the duplicate IP-addresses in my sheet except the first one.
What I did:
I chose the range (F column) -> Conditional formatting -> Highlight cell rules -> Duplicate Values. It highlighted all the duplicate values so i googled how to highlight the duplicate values except the first one so I came across a formula =COUNTIF($F$2:$F2, F2)>1
So I added the formula and now it does not highlight the first duplicate but only the second and not the third and fourth and so on.
What am I doing wrong?
Edit: I have sorted them in order and in one IP-address I have 6 duplicates but it highlights only the last one when I want it to highlight all of them except the first one.
In Column A, there are about 2000 plus entries, is there a simple formula to highlight the duplicate values in conditional format, so that the duplicate values stand coloured.
View 2 Replies View RelatedI have a set template for 3 sheets. I copy/paste data into all three sheets within the template. Some data are repeated twice within the 3 sheets. How can I highlight the duplicate data between these sheets?
View 5 Replies View RelatedI'm use with this macro to Highlight duplicate cells BUT the macro highlight only from the second duplicate
I need to highlight ALL duplicate cells in column A:A .
FUNCTION like " =COUNTIF($A$1:$A$11,A1)>1 " it's not good for me in this case, i need it with a fast macro
I am trying to set up a workbook for work that will highlight duplicates. I know how to use conditional formatting to do this, however, I would like each duplicate "set" to be a different color in order to better identify. The area that I need to search for duplicates is E3:E27. How can this be accomplished?
View 2 Replies View RelatedI want to write a macro that highlights duplicate values within a single column. I found - on this site a macro that removes duplicate values, which is huge, but sometimes I just need to highlight duplicates values rather than remove them.
View 2 Replies View RelatedI have a spreadsheet with 3300 rows. In column A there is a list of company names and in column H there is a corresponding Sales Rep name.Column A has many duplicate company names. I would like to run a macro that will find the a company name and then delete all the rest of the rows that contain that same company name.
Attached is a sample of that spreadsheet.
How can I find prime numbers in a range and highlight it if a number in a cell is a prime.
View 9 Replies View RelatedI have a spreadsheet with thousands rows of items on. I am trying to identify duplicate entries based on two or three cells within each row. If those two or three cells are duplicated for that particular ID then the duplicate entry (row) should be highlighted for deletion.
Better still it would be great if there was a macro or formula which would delete that duplicate row.
So for example on the spreadsheet I have attached, I would like rows 6 and 9 deleted or highlighted as the criteria for deletion is a duplication of the entry in the 'YEAR' column AND the 'VALUE' column for the REFERENCEs AB-1234 and AB-9876 which are the unique identifiers for each individual case.
This would leave the end result as per the table below on the attached spreadsheet.
I have a large compilation of data on an excel work sheet (I am using 2013). All cells relate to each other by means of dates, names, and other numeric values such as stock numbers, id numbers etc... In one column some stock numbers are repeated. I would like to know if there is a way to set up the program on this worksheet to highlight all repeating stock numbers, not only in the column but across the cells as well, in lieu of doing so manually.
View 1 Replies View RelatedI was wondering how I could highlight duplicate words or in a text strings of 5 or more words, 6 or more words etc or a desired value within a document? It would be great if I could highlight the cell with duplicate word in yellow. Essentially I was hoping that this could be done using VB/macro with just a simple button loaded on the toolbar. I hope that my explanation is easy enough to follow. Below is an example:
, KERK STRAAT 13,ROBERTSON,ROBERTSON,6705
I have multiple columns / rows of data, some of which are duplicates.
Column S is a concat of columns A:R where this data is stored, and is sorted alphabetically.
I'm looking for a way using VBA to find duplicate concat rows by cycling through this list that is already sorted. I'm interested in moving down this list, 1 by 1, and if current cell = cell above, delete the data in columns A:P of that row, then delete the cell data in column R of the cell above the current cell.
So for example, if I have sorted data in S8:S14, and S9 = S8, then I would like to delete A9:P9, then delete the data in R8.
I have a set of data which are deal numbers
So week 1 i could have
Deal
ASD
ADF
AGF
Then next week i would get another list of deals
ASD
AHJ
ADF
AGF
AIG
Is there a formula i can use which well tell me if a new deal has been entered in the following week.
So in the above AHJ and AIG would be new deals
(i would simply copy the deals and put them in Column A and B and the formula to go in column c)
I have columns B3 C3 and D3
Each have the header below
net_addr1net_addr2net_addr3
It looks like this
net_addr1net_addr2net_addr3
3 0 0
I have 3456 rows that have IP addresses. I need to either highlight the duplicate IP address and or use column I3 to put a word "Duplicate"
I am creating an interactive tool in Excel.
One of the Fields is "Employee Name"
I created a validation list that includes the names "Mary, Joe, Michael, and David"
When I plug in the name "Mary" I want to pick out her qualifications from a list I have created on a separate worksheet.
Passport
Mary
Joe
David
Lunch
Mary
Joe
Michael
Car
Joe
Michael
David
I want Excel to have the ability to find the name "Mary" from those three lists and reply back to me with the headers of either Lunch, Car, and/or Passport based on the lists Mary appears in. I want this to change depending on the Employee Name and I pick and the lists they appear in.
In a column i have multiple names, most individual names appear many times.
I want to find the names that have only one entry (appear only once)
Is there a vba that could search a column, and underline all the names that appear only once?
I have three columns of names. I need to loop through the first two checking to see if any name is in the third column and if so, place a 'found' in a fourth column.
View 9 Replies View RelatedI have two spreadsheets of "leads"
One lead is a do not call list that has 4 separate columns of phone numbers,
The second list is a batch I purchased.
I need to make sure that the second batch does not contain the info from the first spread sheet.
I guess I could modify the 1st spreadsheet to just be one column of numbers, then I would be comparing one column to one column.
Any way of finding particular data from a data set and deleting the data from that field without using conditional formatting or remove duplicates?
I have used a vlookup to find the words that I needed I now need to find where the are located in my original data set and delete these so I am left with data that if I perform a vlookup on it will not bring back any words as they will have been deleted.
I use this code below to copy information from various sheets on to one sheet,
PHP Code:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim xrow As Integer, xcol As Integer
[code]....
i use a command button to run the macro, but i would like something added that if someone else pressed the button and ran it again that it would check to see if it had duplicated the previous line and if it did it would delete it leaving just the one record for that week.
I deal with leads for a sales room and get sent over leads in bulk, I've created a master scrub list that I can attach to the end of a new lead file and sort by number to show which are duplicates.
When you do the: Data, Filter, Advanced Filter, select Unique Records, it hides the duplicate but what I need is not only for the duplicate to be hidden or gone but the row that it is a duplicate of, i.e. I need BOTH rows to go
Name-----number
Dave 555-1212
Dave 555-1212
John 536-2343
Smith 423-2312
needs to become
Name-----number
John 536-2343
Smith 423-2312
I would need a formula that figured out that Dave with number 555-1212 was a duplicate and delete BOTH rows,
I have a spreadsheet to track events (servicing, repairs, fuelling, etc) for my car - dates in Col A, events in B, mileage in C, prices in D.
I want to return the mileage when the next service is due, based on the mileage of the last service. Using VLOOKUP set to FALSE returns the first service mileage; set to TRUE, it returns random values. The events in Col B repeat in a random order (e.g. fuel, fuel, service, fuel, repairs, fuel, fuel, service).
I have a spreadsheet I use for creating golf score cards. I select the golfers from a drop down list and display a message if a golfer has already been selected. The ranges I want to use are A7:A15, A25:A33, A43:A51, A60:A68. My code is below and I am getting an error saying 'compile error, argument not optional' and UNION is highlighted.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Union(Range("A7:A15, A25:A33, A43:A51, A60:A68"))) Is Nothing Then
With WorksheetFunction
[Code]....
Wondering if there is a way, script-wise or formula-wise that will look through a table and find duplicates but not just any duplicates, it has to match a certain criteria.
For example, I have a column for UPC and Vendor#. I have my table sorted by vendor (all 159 of them in one table) I want to find out whether a UPC appears more than once for a vendor.
UPC --- Vendor
11254 135
11251 135
11251 135
11254 9345
11251 9345
Here, as you can see, UPC 11251 appeared more than once for vendor 135. Can this be done through conditional formatting ? or a script?
I have a lengthy excel spreadsheet in which I catalog all of my Karaoke song collection. I would like to be able to automatically locate (not delete) cells in which there are duplicate song titles. For my purposes just finding these items quickly so I may deal with them in a manner I've already developed is all I want to do. VBA, Macro or whatever would be appreciated. I'm a relative novice with macros and VBA.
For example row 12 may have the song title "Crazy" in column C and later row 1200 may have the same song title. Manually searching the spreadsheet for dupliates is way too time consuming so I think it can be seen why I'm looking to unlock the power of excel to do this for me.
Is it possible, and does anyone know the formula to find someones name and add the data to the right of it? Example
A1 John B1 32:26:00 C1 500
A2 Bill B2 23:45:44 C2 452 D2 John E2 36:45:11 F2 520
I have many names and they are scattered throughout the sheet. I know how to add them if the are in a single column but not if they are scattered.
In my summary I would have the names ...