Log In
Register
Home
April 23, 2015
Offline
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
VIP
April 21, 2015
Offline
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
April 23, 2015
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
Hi Alan
You can also consider using Data Validation to prevent duplicate entries.
https://www.myonlinetraininghu.....it-entries
Hope this helps.
Sunny
VIP
April 21, 2015
Offline
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
April 23, 2015
Offline
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
April 23, 2015
Offline
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
July 16, 2010
Offline
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
April 23, 2015
Offline
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
July 16, 2010
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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.
April 23, 2015
Offline
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
April 23, 2015
Offline
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
1 Guest(s)
