Value look up and/or conditional formatting not grabbing all duplicates

[BlueDuck]

Hello all,


For a reference the finished product should look like the data in excel file titled "Example".

Essentially, I've been having to manually go through and see if the date in column 4 exists in column 1, if it does I keep it, if it does not I delete it. and vice versa. However, I thought it would be easier to do a VLOOKUP forumula or conditional formatting and sorting for the highlighting values etc.

The problem I am running into is that BOTH conditional formatting and VLOOKUP are not highlight the duplicate values that exist both in column 1&4. Even when i take two example values from column 1 and column 4 that are identical in everything and paste them in a new excel file it still doesn't highlight. I pasted them in a word document side by side and they are identical but still they are not highlighted.

Example (Top value is from column 1 and bottom value is from column 4):

6/2/19 8:30 PM
6/2/19 8:30 PM


Any help at all with this would be greatly appreciated. Thanks

As a side problem, I'm unable to figure out how to attach documents on this forum

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

[Special-K]

There's a yellow banner at the top of the forum that explains how to attach files - the paper clip file attachment option doesn't work.

[BlueDuck]

Hello. I tried doing that. I went to "go advanced" then clicked. Then I clicked on "manage attachments" then clicked again. Then i uploaded the two files and they had a blue hyper link thingy right above the "attachment key" chart. and then I have no idea where to go from there.

[BlueDuck]

Maybe this is it?

[Glenn Kennedy]

From inside manage attachments, use choose file to selct the file(s). Then click "upload" (at the right side of screen), then "close this window" (top right). Then "submit reply"

[BlueDuck]

Yes thank you. Those are the two files I submitted. Here is a photo demonstrating that they are not all highlighted.

[Glenn Kennedy]

There is no CF on the files that you attached. There is no formula either, so it's hard to know what you are doing wrong. On the plus side, now you can upload files!!

[Glenn Kennedy]

Aha. Normally I don't like pictures, but on this occasion it helped...

=ISNUMBER(MATCH(ROUND(1440*A1,0)/1440,ROUND(1440*$D$1:$D$26,0)/1440,0))

either in a column (array entered) or used as the CF formula will do what you neeed. It's an artefact from rounding.

in time functions.Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[BlueDuck]

Ah, I apologize about that. I guess it's not wonder I can't figure out how to do it myself if I can't even use this forum properly.

I've tried doing it two ways

1. VLOOKUP

I insert a new column to the left of column 1 and then input the formula in the file. From there I was going to sort by ascending date and the rows in columns 2 and 3 associated with the data point in column 1(now 2) should stay true.
The new file has that formula I'm using

2. Conditional Formatting
The picture shows that one

[Glenn Kennedy]

See Post 9

[BlueDuck]

I'm going to check and see if that works for the rest of my data, it looks promising though! Thank you for all of your help so far, it will save a ton of time.