Comparing two sets of data and making sure they are the same

[faisalm2]

Hi

Basically I am doing a reconciliation (comparing two sets of data). The two sets of data consists of name, dates and completion dates of the course. How do I compare the two and make sure they are the same as each other. Please help!!!!!

 

[63falcondude]

Here is one way. This is a scaled down example but the same logic can be used with any number of columns.

Excel 2010
	A	B	C	D	E	F	G
1	Data A				Data B
2	Letters	Numbers	In Data B?		Letters	Numbers	In Data A?
3	A	1	Yes		B	2	Yes
4	B	2	Yes		D	4	Yes
5	C	3	Yes		A	1	Yes
6	D	4	Yes		C	3	Yes
Sheet1

Cell Formulas
Range	Formula
C3	=IF(COUNTIFS(E:E,A3,F:F,B3),"Yes","No")
G3	=IF(COUNTIFS(A:A,E3,B:B,F3),"Yes","No")

 

[faisalm2]

Hi Falcondude

I know this method but i don't think its ideal for me. As I want it to point out the differences if possible. As the countif method shows there is a difference I want a method where I can compare the same person on both sheets there start and end dates and make sure they are the same and the money they have to pay to us?

 

[faisalm2]

The data is on seperate spreadsheets btw

 

[63falcondude]

If you want to specifically highlight values on the first data set that are different than the corresponding values on the second data set, try this:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Data A					Data B
2	Name	Start Date	End Date	Owe Amount		Name	Start Date	End Date	Owe Amount
3	A	1/16/2017	2/20/2017	$0.00		C	1/2/2017	3/1/2017	$200.00
4	B	3/14/2016	3/30/2017	$100.00		B	3/14/2016	3/30/2017	$100.00
5	C	1/2/2017	4/1/2017	$200.00		D	5/20/2017	6/1/2017	$150.00
6	D	5/20/2017	6/1/2017	$100.00		A	1/16/2017	2/20/2017	$0.00
Sheet1

Highlight B3:D6 > Conditional Formatting > New Rule > Use a formula

=NOT(COUNTIFS($F:$F,$A3,G:G,B3))

Format: Fill red > OK > OK

The same logic can be applied if the data set is on a different sheet.

 

[faisalm2]

That is quite understandable, but what if there are two same dates, e.g. for C and B in end date but one is correct and the other is wrong but excel highlights both ? or will it not do that as they are unique

 

[faisalm2]

Also how did you put it into the formula to look up for each and every line?

 

[faisalm2]

Is it possible to show you the two spreadsheets and try to explain what I mean ?

 

[63falcondude]

what if there are two same dates, e.g. for C and B in end date but one is correct and the other is wrong but excel highlights both ? or will it not do that as they are unique

The formula looks at the name and then checks that the other values are the same in both data sets for that name.

Right now, there is only Conditional Formatting applied on the first data set (Data A) and not the second one (Data B).

Conditional Formatting can be applied to Data B in a similar manner if you wanted to.

Also how did you put it into the formula to look up for each and every line?

The row number in the CF formula is relative (i.e. not absolute) so the formula for
row 3 will be =NOT(COUNTIFS($F:$F,$A3,G:G,B3))
row 4 will be =NOT(COUNTIFS($F:$F,$A4,G:G,B4))
etc.

Since B3:D6 was highlighted, it will run this formula from B3 to D3, B4 to D4, B5 to D5, and B6 to D6.

 

[faisalm2]

I'm getting a better understanding now, If you can please show me how to do it on my spreadsheets that would be really really appreciated. I haven't got them on my computer and they are on my laptop which is being repaired should be back tommorow. If you are willing I can attach them tommorow and you can show me the right path!! Honestly help is really appreciated.