Excel - Check For Duplicate Data When Populating A Cell?

Jan 27, 2014

I have a big database of customers, each one has a unique reference number. They are spread accross a bunch of different tabs (21 in total to be exact)

One issue I have had is staff entering a customer whos already in the database, causing a duplicate entry. I dont need excel to tell us where the entry is, just to give some kind of indicator it already exists. I thought I may be able to use data validation/conditionality to turn the cell fill Red when it already exists in the data base.

The reason I think that method would be best, is that the sheets that data is entered on are seperate that the master sheet then pulls the data through from. Its the master sheet that would need to indicate a duplicate has been entered, as thats the only sheet where the entire database can be viewed.

View 2 Replies


Excel 2013 :: Populating Column Without Duplicate Values

Jul 14, 2014

I've got a column of client company names and because it is based from accounts, the companies names appear many times. I just need to populate a new column with unique names, so I have found a formula to do this:

=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

However when I do this, I CtrlShiftEnter and drag to copy that down, and it's just a copy of the original list - client names are appearing on my new list multiple times. I don't see how this doesn't work... I'm using 2013.

View 1 Replies View Related

Excel - Duplicate Cell Search And Further Analyze Data Using VBA

Jul 1, 2014

I have an excel sheet with data from Column 'A' to 'S'.

I want to identify duplicate entries in Column 'J' and from those identified columns I want to further search Column 'K', highlight the matching text and then fill text in Column 'L' should have the missing data after matching the 'A' and 'K' cells.excel_duplicate_entries.png

View 9 Replies View Related

Check For List Of Duplicates In Sheet1 And Place The Duplicate Data In Sheet2?

Mar 13, 2014

I need macro that will check for list of duplicates in sheet1 and place the duplicate data in sheet2.
I know how to check duplicates in only one column.But now my sheet having lot of columns.

View 14 Replies View Related

Auto Populating Excel From Data In Another Sheet

Aug 28, 2013

I am trying to auto-populate a table with data looked up from another sheet. The functions I have used are: Data Validation, vlookup, ISerror and if functions.

Cell B4 in sheet2 uses Data Validation to pick data from sheet1 (in this case "tax") and auto populate the table with the information- Job type, Name, employee type and Job title. If i change from Tax to HR, the table should get auto-populated with the correct information.

I have setup vlookups but I am unable to get it working. I am a novice at using vlookups (I learn it just 2 days ago) and am struggling to get it work. I am not even sure if vlookup is the right tool to get this job done.

View 12 Replies View Related

Populating Cells When There Are Duplicate Conditions

Oct 22, 2008

if the state, city and company name are the same, I need to make sure the address gets populated in the address column. I have included an attached sheet to clear this up.

For example,

Row 1 is Crazy Horse Saloon in Anchorage, AK and has an address and phone number
Row 2 is Crazy Horse Saloon in Anchorage, AK and does not have an address and phone number

I need it to be able to realize that the State, City, and Business all match and populate the missing information.

View 5 Replies View Related

Excel 2007 :: Populating Data Based On Two Criteria?

Mar 28, 2014

I'm trying to populate a table based on two different criteria in a another table. On is based on a date and the other the number of a unit. I'm trying to get the data in sheets Week 1 and Week 2 into the format in sheet Example. Is there a way to create a series of functions, filters, etc that I could use to create a macro to do multiple sheets or at least a whole sheet at once? I've been trying to think through it with my limited knowledge of filters and functions

Here is the file with an example and I'm using Excel 2007: Example.xls

View 2 Replies View Related

Excel 2010 :: Date Not Populating Cell

May 1, 2014

I have a userform which requires the user to enter the date a training course was completed. Initially I used DatePicker as I was running Excel 2010 and had that working, however the workbook I am creating will be used on about a dozen machines, all of which have different versions of Excel. To avoid needing calendar Add-ins to be downloaded and installed for this feature I decided to go the vba created calendar route. I am using one I found on this forum: [URL] .....

It works well for me using the example spreadsheet provided in the post but I am having some difficulty incorporating it into my code. I believe I have imported the appropriate forms and modules because I can get the date populate to the Label Box on my userform but when I try to put the Date into the spreadsheet along with other data such as name and course duration, the name and course duration populate but the date cell remains blank and execution completes without error.

I have tried playing around with the data type thinking I had some issue using the .Value attribute with a String but .Text gives me an error as does converting from String to Number format.

I will attach the workbook but for a quick look, the portion of the code I believe that is not working is in here:

[Code] .....

Training Classes Example Workbook.xlsm

View 3 Replies View Related

Excel 2010 :: Auto-populating Calendar Based On Conditions From Data Table On Another Sheet?

Apr 26, 2014

I'm trying to auto populate a calender style sheet in Excel 2010 based on data from a Work Schedule sheet. The work schedule sheet contains a list of jobs, with each row representing a different job. There is a column for the start date (e.g. 25/04/14) and a column for the end date (e.g. 26/04/14). There are other columns which select resources such as people and vehicles. Each resource may appear on any one of several columns for each row, e.g. Site Operative 1, Site Operative 2 etc.

On the calendar sheet, in which one cell represents one day (e.g. 25/04/14), all the dates are shown along the top going right and all the resources are shown on the left going down.

On the calender sheet, in every cell I want a formula that will look at the Work Schedule sheet and see if that particular resource is being used on that particular date. If it does, the cell can display information from another cell such as the job number or job name to which the resource is assigned; if it doesn't, the resource isn't being used so it can display "Free" or "Available".

View 2 Replies View Related

Populating Userform With Cell Data When Initializing?

Jul 8, 2012

I am trying to populate a text box in a user form when initializing the form. I have reviewed many posts in this forum regarding this problem, but have been unable to resolve. My code looks like this:

Private Sub frmFeed_Initialize()


View 6 Replies View Related

Extracting Data In One Cell And Populating Another Sheet?

Apr 26, 2013

I want to create a sort of complex logging system for my work.

As of right now i use a simple temple. Column A has the job number, column B has drawing numbers. Each cell in column B has multiple drawings. So for example cell B2 might contain 101, 102, 103, 104 and so on or something like 101...104. Depending on the situation. The ... means drawings 101 thru 104. What i want to do is have a second sheet that has Column A be the job number but column B has just one drawing #, so an example would be . A1 = 10939 and B1 = 105, 107, 109..111. Sheet 2 would then have A1 = 10939, B1 = 105, A2 = 10939, B2 = 107, A3 = 10939, B3 = 109, A4 = 10939, B4 = 110, and so on. Is something like this possible.

View 4 Replies View Related

Adding Data With Userform: Check For Duplicate Before Adding

Feb 14, 2007

I have a userform that I'm using to add data to a worksheet, with the following

Private Sub CommandButton1_Click()
Dim OutSH As Worksheet
Set OutSH = Sheets("Sheet1")

OutSH.Cells(nextrow2, 1).Value = Surname.Value
OutSH.cells(nextrow2,2).value = ID.value
OutSH.cells(nextrow2,3).value = Date.value
I need to ensure that duplicate entries are not made for the same person on the same date. The ID is unique to each person.

IF statement that can check for a duplicate and then come up with a dialouge box with some custom text, and then exiting the sub?

View 9 Replies View Related

Populating A Cell With Data From Another Cell In A Different Workbook?

Dec 13, 2013

I am entering data into one workbook 1 (WB1) that contains a worksheet for every month. I manually extract some of the data from WB1 and put it into workbook 2 (WB2), which also contains a worksheet for every month. Is there a way to populate WB2 from WB1? For example, I want cell B5 from WB1 to automatically populate B5 in WB2, and so on. I have to update this every day and would like to way to automate it to cut down on data entry errors since they need to match. And both of these files are stored in SharePoint for others to access if need be.

I have attached a sample of the workbooks (yet only have one month in each of these wb).

View 2 Replies View Related

Excel 2013 :: Condense Duplicate Data To One Row Data In Same Column In New Row

Mar 12, 2013

IIn my example I have result data from the 2013 USPGA Tour, ordered by player (column A) with subsequent columns detailing their finishing position in each event.

I simply want to condense each player's performance data into one row - as doing it by hand after each event is very time-consuming.

Is there a way of using a simple formula, macro or pivot table (or whatever) to merge each player into one row, but keep each column in the same position (ie to correspond to each weekly event).

View 4 Replies View Related

Duplicate Excel Spreadsheet With Extra Data?

Apr 28, 2014

I am trying to set up a spreadsheet that pulls all the data from an existing spreadsheet on a networked drive. The spreadsheet on the drive gets updated occasionally by about 10 different people. I am trying to set up a duplicate of it on a local machine so that a single extra column can be added that no one else can see.

So far I am able to get the data from the spreadsheet using data connections, but it won't copy across the comments or any of the colour formatting. The colour is not as important, but the comments are vital. We use Microsoft office 2007 here, on windows 7 enterprise.

View 4 Replies View Related

Excel 2007 :: Combining Data In Duplicate Rows

Aug 15, 2008

I have a long (about 180 000 rows) list of part numbers, descriptions, prices and quantities. There are many duplicate part numbers. I need to go through and find the duplicates, add all their quantities into one cell and delete the remaining duplicates. I have used conditional formatting to find the duplicates easier but cannot use the remove duplicates button as it will delete the whole row and I need the quantity information in each row.

Any formula or macro I can set up to search the part number column (F) for duplicates and add the figures in the quantity column (J) and THEN delete the duplicate part number rows?

Maybe I need to put the new combined information in another sheet?

View 8 Replies View Related

Excel 2010 :: Delete Row With Duplicate Data In 2 Columns?

Apr 21, 2014

Basically, I have a sheet and I would like to delete the entire row if the data in column G is the same as that in column H. The data is text if that matter. I've tried to figure out the VBA code for it, but my knowledge is severely limited. The spreadsheet is excel 2010.

View 3 Replies View Related

How To Check Duplicate Contacts

Aug 29, 2013

I have a workbook with a list of new contacts in one sheet, and a list of old contacts on another sheet.

I want to run a duplicate check throughout the workbook to highlight and delete any contacts on my new list, that have already been contacted on my old list.

Also, I need the the duplicate checker to check for non specific names, regardless of punctuation, upper/lower case, etc.

I need to to highlight all of the following variations as possible duplicates which i can then review & delete manually;

Apple Corp,
Apple Ltd

What I can use to do this?

View 3 Replies View Related

Check Duplicate, Missing Number

Jun 18, 2009

Hi guys/gurls.. is there a way i can get the following check against range for orders which give result as invoiced(if within the range), duplicated(if 2 or more than 2 are on the orders list) & missing (which on not in the orders when checked against the range).

View 6 Replies View Related

Duplicate Check On Export Of Records

Dec 20, 2008

I have a spreadsheet that will export records when I "click" a button, to a spreadsheet on a network drive.

I would like to attach some code that will check to see if a duplicate record is going to be written to the network drive, and if so, alert the user that they are about to create a duplicate . If it is a duplicate, give the user the option to overwrite the existing record.

Ex. I export the results for account 12345.....Account 12345 is now written to an outside sheet.

User2 logs in and works with customer 12345, w/out knowing that someone has already worked with this account, presses the export button.....This is the trigger point for what I want to happen. At this point when it locates a dupe in the outside spreadsheet it will prompt the user too make some chouces .

find attached the code I would like to attach this to...

HTML option Explicit

Sub TransferData(Optional Dummy As Long)

Dim Row As Long
Dim TargetRow As Long
Dim Path As String
Dim Prompt As String
Dim Title As String
Dim Cel As Range
Dim Wkb As Workbook

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

Set Cel = wsCustomers.Range("C:C").Find(What:=Range("C4").Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Cel Is Nothing Then
Prompt = "There is no match for the Customer Number (" & Range("C4").Value & ")."
Title = "Process Aborted"
MsgBox Prompt, vbCritical, Title
GoTo ExitSub:
End If
Row = Cel.Row

Set Cel = wsSetup.Range("A:A").Find(What:=wsCustomers.Range("A" & Row).Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Cel Is Nothing Then
Prompt = "There is no Workbook Path setup for this Region (" & wsCustomers.Range("A" & Row).Value & ")."
Title = "Process Aborted"
MsgBox Prompt, vbCritical, Title
GoTo ExitSub:
End If
Path = wsSetup.Range("B" & Cel.Row).Value

If Trim(Path) = "" Then
Prompt = "There is no Workbook Path setup for this Customer Number (" & Range("C4").Value & ")."
Title = "Process Aborted"
MsgBox Prompt, vbCritical, Title
GoTo ExitSub:
End If

On Error Resume Next
Set Wkb = Workbooks.Open(Filename:=Path, UpdateLinks:=False)
On Error GoTo 0
If Wkb Is Nothing Then
Prompt = "The workbook for this Customer Number (" & Range("C4").Value & ") could not be opened." & vbNewLine & vbNewLine & "Path: " & Path
Title = "Process Aborted"
MsgBox Prompt, vbCritical, Title
GoTo ExitSub:
End If

View 10 Replies View Related

Check For Sheets With Duplicate Names

Feb 2, 2009

I have written some code which asks the using for a name, selcet's a sheet, copy's the sheet, rename's the copied sheet then hide's the original sheet and makes the copied sheet with new name active.

My problem is that I need some sort of code to check for sheets with duplicate names and if true ask the user to rename the sheet or maybe delete the sheet.

View 4 Replies View Related

Developing Duplicate Check In Macro?

Jul 19, 2012

I've been assigned to develop a workbook that autopopulates a different workbook but they wanted it so the second macro is never messed with and the information is only entered once. Here is the macro i developed

Sub Monthly()
Dim wbthis As Workbook
Dim wsthis As Worksheet, wsm As Worksheet


Now The area in red is where the macro writes into the form. My thing is that I want to set up a check so there isn't duplicated entries. This is how the check would work. First it would match up any excisiting entries with JobNo and then check to see if the JobDate was the same. If both are the same then it would check Waste or CutTime, either one would work. If they didn't match, then those cells would be overwritten. If nothing matched then it would put in the new entry.

View 1 Replies View Related

Check The Duplicate Name In The Sheet Using VB Macros

Oct 29, 2008

I want to Check the Duplicate Name in the Excel Sheet Using VB Macros
****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11">

If the 1 nd Row Contains Name as " 101 Calif " and the Value For Vb is 77
2 nd Row Contains the Same Name "101 Calif " and the Value for this is 2.

Now I want the Output As Follows

101 Calif and Column 3 values is 79(77+2) and Column 5 Value is 105 and Column 7 Value is 105 and Column 9 is 100 and Column 11 is 3 and Column 13 th Value is % Value . (10+30/2 = 40/2= 20 )

***** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"> Vendor
Actual YTD Annualized
2007 Actual Difference % Change.........................................

View 9 Replies View Related

Copy Sheet - Check For Duplicate Name

Feb 20, 2007

I have a command button that copies a template worksheet and names it with a date that the user chooses. Problem is, the user can choose the same date more than once. So, I need to have the command button check for all the sheet names, and if it finds a duplicate, prompt the user to enter some text to concatenate to the original sheet name, therefore making it's name unique. My existing code is as follows:

Private Sub CommandButton1_Click()

MsgBox "Please be patient. Creating a new Week Ending sheet can take several minutes!"

Call AddNewWE

End Sub

Sub AddNewWE()..............................

View 3 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!!)

BUT, if a user then tries to enter:


I need my userform to register a duplicate entry.

My sub routine is detailed below : .....

View 3 Replies View Related

Automatically Check Each Worksheet For Duplicate Entry

Apr 17, 2008

I have multiple worksheets of computer equipment, each worksheet is a group/department. Column H is the serial number column and the entries have to be unique. I have managed to create the code below which does find duplicates across worksheets.

When error message pops up about which sheet the duplicate already exists on, the duplicate entry is deleted and the cell is blank but the error checks again and reports the blank existing on another worksheet and then it is stuck in a loop. How can I ignore the blank or null.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Integer
I = Sheets.Count
If Not Intersect(Target, Range("H2:H200")) Is Nothing Then
Do Until I = 0
If Application.IsError(Application.Match(Target, Sheets(I).Range("H2:H200"), 0)) Then
MsgBox "That entry already exists in the " + Sheets(I).Name + " sheet"
End If
I = I - 1
End If
End Sub

View 6 Replies View Related

Excel 2007 :: How To Duplicate Rows Based On Cell Value

Feb 26, 2014

What im needing is something that will duplicate each row based on Column F as shown below... However I also need the duplicate row numbers added to Column G and then the original row deleted... I will try and show you an example of the input/output i would like to achieve: (also the first row can be ignored it will all be headers)




View 1 Replies View Related

Macro To Check Data From Word Documents Against Excel Spreadsheet?

Jun 20, 2013

Basically I have manually generated word documents based on a spreadsheet. Now, I need to countercheck the contents of the word documents with another spreadsheet i.e. make sure the courses in the word document for a particular student is same as that reflected on the spread sheet. Is it possible to write a macro for this? I'm a beginner and I'm waaay out of my depth. I have attached sample documents and spreadsheets.

The link to the documents are as follows: Macro to check data from Word documents against an excel spread sheet

View 2 Replies View Related

Excel Checkboxes To Automatically Check If There Is Anything In A Cell?

Aug 13, 2014


So I have an 2 ActiveX button and 2 ActiveX checkboxes. I have it set up so that the Split button will enable when both checkboxes are checked. The ClearAll button has a code/macro in it that will check the "Cleared All" checkbox when the Clearall button is pressed.

My problem is with the "Populated Data" checkbox. I want it to check/uncheck itself if there is anything in Range("A1") of a tab called "Data".

The names of these buttons/checkboxes are

"Cleared All" = ClearCheckBox
"Populated Data" = DataCheckBox
"Split" = SplitButton

View 2 Replies View Related

Excel 2010 :: Pulling Data Based On Multiple Criteria With Duplicate Values

Mar 14, 2012

I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.

I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).

For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?

Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250

Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432

View 3 Replies View Related

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