comparison between sheets

[mickygoalie]

Hi All,
I hope you can help me, I have had a search, but can't find anything to help me on this. I don't really understand VBA..

I have a large amount of data from different sources that I am trying to compare, and I'm looking for a quicker way to do this.

Worksheet name: BO Details

A B C
ANTHONY TAYLOR BIRMINGHAM CPC001
ANDREW BRIGGS BASINGSTOKE CPC002
MICHAEL SLATER BIRMINGHAM CPC001
MICHAEL SLATER BIRMINGHAM CPC002
MICHAEL SLATER UTTOXETER CPC001

Worksheet name: Provider Details

A B C
ANTHONY TAYLOR BIRMINGHAM CPC001
ANDREW BRIGGS BASINGSTOKE CPC002
MICHAEL SLATER BIRMINGHAM CPC002

I would like to see confirmation that the details match and which details are missing between the 2 worksheets

Thanks in advance for any help

 

[schielrn]

You could use a helper column to concatenate the 3 cells and it may be a little more efficient than the arrawy formula I used, but they will net the same result. Also, this can become really slow depening on the number of rows you are talking about.

Excel Workbook
	A	B	C	D
2	ANTHONY TAYLOR	BIRMINGHAM	CPC001	In Other Worksheet
3	ANDREW BRIGGS	BASINGSTOKE	CPC002	In Other Worksheet
4	MICHAEL SLATER	BIRMINGHAM	CPC001	Not in Other Worksheet
5	MICHAEL SLATER	BIRMINGHAM	CPC002	In Other Worksheet
6	MICHAEL SLATER	UTTOXETER	CPC001	Not in Other Worksheet
BO Details

#VALUE!

Excel Workbook
	A	B	C	D
2	ANTHONY TAYLOR	BIRMINGHAM	CPC001	In Other Worksheet
3	ANDREW BRIGGS	BASINGSTOKE	CPC002	In Other Worksheet
4	MICHAEL SLATER	BIRMINGHAM	CPC002	In Other Worksheet
Provider Details

#VALUE!

Hope that helps.

 

[mickygoalie]

Hi
Thanks for getting back to me on this, it has been driving me mad all day....

This doesn't seem to be working tho, it is just returning 'Not In Other Worksheet' even for ones that I know definately are. It is also taking along to calculate using Excel 2010.

I will have another go later, but if you could think of anyother suggestions I would appreciate it

Thanks

 

[schielrn]

Are you confirming it with Control+Shift+Enter and not just enter?

Otherwise in column d for both sheets you can put:

=A2&B2&C2

And then do the match function using column D and you do NOT need to confirm with control+shift+enter