Highlighting Data differences where 1 value is the same

[mt006]

Hi

Currently I compare two sets of exported data in Excel.

I do this simply by using a Countif formula on a unique Database ID then sorting the Data so any lines which are unique move to the top of the list

However I want to expand this

Where the Countif formula declares a duplicate I want to run an additional check on one of the other cells typically in a Date/Time format.

This is to pick up where the time value has changed from the older export to the newer data.

In example

[TABLE="width: 165"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]815917[/TD]
[TD]08.04.2017 - 01:00[/TD]
[/TR]
[TR]
[TD]802244[/TD]
[TD]08.04.2017 - 01:00[/TD]
[/TR]
[TR]
[TD]801562[/TD]
[TD]08.04.2017 - 01:00[/TD]
[/TR]
[TR]
[TD]801562[/TD]
[TD]08.04.2017 - 01:00[/TD]
[/TR]
[TR]
[TD]815917[/TD]
[TD]08.04.2017 - 01:30
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
So in this example there are 2 columns - ID and Date/Time

802244 would move to the top to be looked as its unique
801562 nothing would happen
815917 would flag as time discrepancy but wouldn't move as its

Is there an easy way to implement this without coding a macro - each sheet in its first iteration is a version of the original export sheet- the data is not added to a different sheet

 

[wideboydixon]

You'll need a couple of helper columns I think:

Excel 2016 (Windows) 32 bit
	A	B	C	D
1	ID	Date/Time	Occurrences	Inconsistency
2	815917	08.04.2017 - 01:00	2	1
3	802244	08.04.2017 - 01:00	1	0
4	801562	08.04.2017 - 01:00	2	0
5	801562	08.04.2017 - 01:00	2	0
6	815917	08.04.2017 - 01:30	2	1
Sheet2

Cell Formulas
Range	Formula
C2	=COUNTIF($A:$A,$A2)
D2	=COUNTIFS($A:$A,$A2,$B:$B,"<>"&$B2)

Then sort by Occurrences ascending and Inconsistency descending.

WBD

 

[mt006]

Never heard of Countifs before.

Looks good and seems to work in testing

So this formula basically works as 2 stage check that were A=A return total number of Bs?

so in

A - B
A - A
A- B
A - C

=COUNTIFS($A:$A,$A2,$B:$B,$B2) would return 2

 

[wideboydixon]

Yes I think so. I was using it to find rows that match on ID but mismatched on Date/Time. COUNTIFS is the same as COUNTIF but allows multiple criteria over ranges with the same dimensions.

WBD