Showing Duplicate Data w/Power Query and/or Formulas

Jarrod Willis 1 Reputation point
2021-06-25T13:04:57.41+00:00

109399-image.png

See attached screenshot. We have a work schedule for three different properties, multiple contractors. On the screenshot, I have filtered for on contractor. As you can see, he is scheduled to be at two different addresses on the same day - which isn't possible. I need a way for excel to tell me "Hey, this person is set to more than one property address on the same date." Whether that's highlighting the cells a different color, or giving me a message to change one or more dates, im not sure. I thought this would be conditional formatting, but then was told it would be more power query, which I have never used.

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
35,749 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Lz._ 8,986 Reputation points
    2021-06-26T18:31:52.137+00:00

    Hi @Jarrod Willis

    Could you revise the title of this thread so it better reflects the challenge/ambition please?
    => Can help those who search the site before posting - Thanks

    Ah, Project management with Excel is always a challenges, isn't it :)? In this file to download, 2 options:

    • Excel formulas
    • Power Query

    Both take the same approach:

    • When there's no overlap, value = 1
    • When there's an overlap, value = 2

    Then 2 Conditional Formatting rules for Value=1 and Value=2. Overlaps are in orange-light below (pic. truncated)

    PIC

    Useless to say that if it was me I would opt for the PQ option


  2. Lz._ 8,986 Reputation points
    2021-07-01T06:35:36.893+00:00

    Hi @Jarrod Willis

    • Thanks for updating this case title although IMHO "overlap" would be better. FYI, I insist on case's title as this helps those who search for existing solutions
    • I would suggest you take 5 minutes to read Use Tables not Ranges
    • Revised your workbook with Excel formulas only given you do not seem to opt for the PQ option
    • Used as much as possible the "standard" references (i.e. $D9 instead of Schedule[@[Req. Info]:[Req. Info]])
    • Added Data Validations on [Property Address] & [Contractor] to limit the risk of typos & extra blanks

    FYI: With Tables, when you use structured references like Schedule[Property Address] that reference is relative (as i.e. D9 is). To make the column absolute (I.e. $D9) you have to use syntaxt Schedule[[Property Address]:[Property Address]] - not very intuitive and must be written manually => When you have 2 mins it would be smart of you to UpVote Use F4 key to lock reference in a table (Thanks)

    I guess that's it :)