• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Preventing duplicate entries|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Preventing duplicate entries|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Preventing duplicate entries
sp_PrintTopic sp_TopicIcon
Preventing duplicate entries
Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
1
June 12, 2019 - 10:21 am
sp_Permalink sp_Print

Hi everyone,

Hope someone can help me with this one.  I am looking for an "elegent" solution to a duplicate data question.  In the attached book I have a resource variable "Trucks".  We can't physically ship out the same truck on the same day to two different projects, ideally I want a formula that prevents that from being entered on the sheet or displays a message in the cell stating that it is a duplicate entry.  For example, cell AJ15 has the truck "WL5" allocated, but cell AJ20 also has "WL5" allocated. As the entry in AJ20 is later in the time line than the entry in AJ15 I would want the AJ20 entry to be flagged as a duplicate.

I have been trying to do this with nested IF statements and the OR function but can't get it right.

Any ideas?

Thanks as always,
Alan

Avatar
Frans Visser
Duivendrecht (near Amsterdam) - The Netherlands

VIP
Members
Level 2
Forum Posts: 346
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
2
June 12, 2019 - 6:29 pm
sp_Permalink sp_Print

Hi Alan, I don't know how sophisticated you want this, but a simple solution might be working with a conditional formatting in column B.

In your example file you can select B15:B22 and give it the conditional format =COUNTIFS($B$15:B15,B15)=2 and then for instance make that cell red.

If it recognises the value again it counts 2 and makes your cell red. In your example this is for B20 (the second truck) but also for B22 (because in B21 you also have 24). If it only is for trucks we can adjust the formula.

Is this what you are looking for?

Frans

Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
3
June 12, 2019 - 10:51 pm
sp_Permalink sp_Print

Hi Frans,

An elegant solution indeed!  It works well many thanks - I do have a one supplementary question though:

Ideally I wanted to have the logic applied in the Gantt area, you should be able to select the same truck in column B but only if it allocated to a date range that it is not already allocated to, (i.e. if WL5 is selected on B15 for dates 28th May to 30th May, then it cannot be selected on B20 for those two dates but it can be selected for say 31st May).  Am I right in thinking I can use your logic and simply apply it to the Gantt cells?

Thanks again,

Alan

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
4
June 13, 2019 - 12:24 am
sp_Permalink sp_Print

Hi Alan

You can also consider using Data Validation to prevent duplicate entries.

https://www.myonlinetraininghu.....it-entries

Hope this helps.

Sunny

Avatar
Frans Visser
Duivendrecht (near Amsterdam) - The Netherlands

VIP
Members
Level 2
Forum Posts: 346
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
5
June 13, 2019 - 6:33 am
sp_Permalink sp_Print

I'm afraid it's more complicated than you suppose Alan. Maybe the easiest way is to make a helper column right of column B. In that column (you can hide it after the formula's are placed) you use the countifs, but the outcome is the 2 (second time WL5 appears). Then you check in your Gantt if there is a 2 in that column on that row and give it another color or give an error message 'Duplicate' or let it empty or so. I think you already check on the right dates in row 14 and 19, so that's not the problem?

Frans

Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
6
June 13, 2019 - 9:33 am
sp_Permalink sp_Print

Sunny/Frans, just a quick note for now to say thank you.  I am going to look at both your suggestions in the next couple of days and see what I can get figured out. I'll let you know how it goes.  Thanks again for the help.

Alan

Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
7
June 15, 2019 - 6:34 am
sp_Permalink sp_Print

Hi everyone,

I have been trying to implement at least some of your advice on this subject.  If you take a look at the attached you'll see my efforts in cells D16, E16 and F16.

F16 - I basically took Frans formula which works well, but only if the duplicate value is the only value in the cells it is checking.  I have a little bit of VBA that allows you to select multiple trucks, when I do that the formula in B17 doesn't work.

So I thought I would add another formula that can find the duplicate text even if multiple trucks are selected.

E16 - this has the formula that checks to see if the value from B16 is anywhere else in the range, but I don't think I have this formula correct - I don't want it to check cell B16 but when I change the range to exclude B16 it gives me a #value error.

D16 - the final formula just checking to see if either E16 or F16 have a value that indicates a duplicate but I obviously need to have E16 and F16 working for this to be correct.

Any ideas on how I might get this working?  The aim is to check and somehow display a note if the value in B16 is shown anywhere else in column B.

Thanks,
Alan

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4614
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
June 18, 2019 - 11:54 am
sp_Permalink sp_Print

Hi Alan,

Replace the formula in E16 with this:

=SUMPRODUCT(COUNTIF(B16:B60,"*"&B16&"*"))

The problem you had is FIND can't take an array as the second argument, it only looks in one cell for the text string.

Mynda

Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
9
June 19, 2019 - 9:29 pm
sp_Permalink sp_Print

Thanks Mynda,

As usual your solution works!  One quick follow up - can I change the "range" section (currently shown as B22:B65), to non-continuous references?  I tried to do this using ctrl to select individual cells but it did not work.  Ideally I just need to check the cells that could have trucks added, i.e. B16, B21, B26, B31 - I can't just type these into the formula without getting a too many arguments error.

Cheers,
Alan

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4614
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
10
June 19, 2019 - 9:51 pm
sp_Permalink sp_Print

Hi Alan,

COUNTIF requires a contiguous range, so no, you can't just check specific cells unless you write separate COUNTIFS, but I don't see why you can't just check all of the cells. That would make it a more robust formula.

Mynda

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 688
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
11
June 20, 2019 - 9:08 pm
sp_Permalink sp_Print

If it's only a few cells, you could use:

=SUM(COUNTIF(INDIRECT({"B16", "B21", "B26", "B31"}),"*"&B16&"*"))

 

Also, just FYI, you don't need SUMPRODUCT with the original COUNTIF formula.

Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
12
June 20, 2019 - 10:16 pm
sp_Permalink sp_Print

Hi Mynda,

Thanks for the insight as always.  Maybe I am not thinking this through properly but my issue, or my desire for non-contiguous ranges is due to the fact that I am trying to set up the duplicate check on multiple rows.  For example on row 21 I need the formulas to check to make sure that the entries in cell B21 are not duplicated in B16, B31 or B36.  Is there a way to do this?

Cheers,
Alan

Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
13
June 21, 2019 - 10:29 pm
sp_Permalink sp_Print

Hi Velouria,

Thanks for the formula, that works well.  With all this help I think I am almost there as far as the duplicate entry check is concerned.  I am going to populate the formulas in the relevant cells through the sheet and give it a test run.  I'll likely be back with more questions!

Cheers,
Alan

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
jaryszek: 183
Newest Members:
Ashley Hughes
Herbie Key
Trevor Pindling
Stevan Kanjo
Erin Sheldon
Nikita Bhatia
Sheilah Taylor
Clare Webber
David Jenssen
Dominic Brosnahan
Forum Stats:
Groups: 3
Forums: 24
Topics: 6526
Posts: 28576

 

Member Stats:
Guest Posters: 49
Members: 32810
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.