Showing Duplicate Data w/Power Query and/or Formulas

Question

Showing Duplicate Data w/Power Query and/or Formulas

Jarrod Willis 1

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.

2 answers

Your answer

Answer 1

Lz._ 9,016

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)

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

Lz._ 9,016 Reputation points

2021-06-30T05:50:29.227+00:00

Hi @Jarrod Willis . Have you had an opportunity to test the above proposal?
Jarrod Willis 1 Reputation point

2021-06-30T15:50:23.363+00:00

Hi there,

just getting back in town. Yes, I have looked at your ideas and it seems they can work. Im having an issue creating the formula you have under the "Overlaps" section. I also cannot see your conditional formatting with the read only file. I uploaded my working file here. Please take a look.
Lz._ 9,016 Reputation points

2021-06-30T16:40:48+00:00
Hi @Jarrod Willis

Nothing is read-only in the file I shared. You have to download a local copy

Again, could you revise the title of this thread to better reflect the challenge? Thanks

It's late here so I won't have a look to what you shared before tomorrow

However, are you serious about opting for the Excel formula solution? It's crazy in term of array calculations and will consequently slow down your model if you have a lot of rows and columns on your timeline. Please look at the Power Query solution in the meantime and let me know by tomorrow
Jarrod Willis 1 Reputation point

2021-06-30T17:46:15.68+00:00

I revised the title... thanks! No rush on taking a look at it.

The only reason I looked at formulas was because I know formulas really well and have never dealt with PQ. It does seem PQ would be easier to manage and for my computer to handle with many pros and columns of data.

Answer 2

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 :)

Lz._ 9,016 Reputation points

2021-07-06T06:40:58.213+00:00

Hi @Jarrod Willis . When you're back in town, feel free to provide feedback. Thanks

Share via

Showing Duplicate Data w/Power Query and/or Formulas

2 answers

Your answer