Hi 

I am running report where I am comparing data from  last month to current month and want to see

 1)if any changes happen and  where?

2) if a new account is in old sheet(Last month) not in new sheet(current) how can i identify that?

3) there are multiple account with same name so if I am using vlookup its copying the first value to the rest

any suggestions to do above?

Re: comparing worksheets

If you want to find specific items then vLookup is pretty good way to go.
To actually compare the contents of two worksheets then a evaluation of each cell is usually required.
... If Worksheets(1).Cells(1, 1) <> Worksheets(2).Cells(1, 1) Then...

Chip Pearson has a free workbook ("Compare.xla")that can do a cell by cell comparison at...
http://www.cpearson.com/Downloads/Downloads.aspx
It could save you a lot of work.

My commercial excel add-in "XL Professional" (3 week trial) has a similar utility that you might find more appealing...
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2

'---
Jim Cone
Portland, Oregon USA

Hi,

If we want to compare the data with two similar column/table/sheet, we can use both of formula and VBA code.

Compare column sample:

=IF(ISERROR(MATCH(A2,$C$1:$C$5,0)),"",A2)

Compare table sample:

=COUNTIFS(Table2[GivenName],[@GivenName],Table2[StreetAddress],[@StreetAddress],Table2[City],[@City])>0

This is how the formula looks like with cell references:

=COUNTIFS(Sheet2!$B$3:$B$17, Sheet1!$B3, Sheet2!$C$3:$C$17, Sheet1!$C3, Sheet2!$D$3:$D$17, Sheet1!$D3)>0

I have uploaded the sample to OneDrive, we can refer to:

Download

Hope it's helpful.

Regards,

George Zhao
TechNet Community Support

Hi,

If we want to compare the data with two similar column/table/sheet, we can use both of formula and VBA code.

Compare column sample:

=IF(ISERROR(MATCH(A2,$C$1:$C$5,0)),"",A2)

Compare table sample:

=COUNTIFS(Table2[GivenName],[@GivenName],Table2[StreetAddress],[@StreetAddress],Table2[City],[@City])>0

This is how the formula looks like with cell references:

=COUNTIFS(Sheet2!$B$3:$B$17, Sheet1!$B3, Sheet2!$C$3:$C$17, Sheet1!$C3, Sheet2!$D$3:$D$17, Sheet1!$D3)>0

I have uploaded the sample to OneDrive, we can refer to:

Download

Next,If Microsoft Office 365 or Office Professional Plus 2013 is installed on your computer, we can use Inquire add-in to compare.

https://support.office.com/en-US/Article/What-you-can-do-with-Spreadsheet-Inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42

Hope it's helpful.

Regards,

George Zhao
TechNet Community Support

Hi 

I am running report where I am comparing data from  last month to current month and want to see

 1)if any changes happen and  where?

2) if a new account is in old sheet(Last month) not in new sheet(current) how can i identify that?

 There are multiple account with same name

any suggestions to do above?

• Merged by George.Zhao CHNMicrosoft contingent staff, Moderator 4 hours 11 minutes ago same topic

Is there any difference to your old question?:

https://social.technet.microsoft.com/Forums/office/en-US/e883f853-60d5-4a1b-8a90-c09ba0bcd07a/comparing-2-sheets-in-vlook-up?forum=excel#e883f853-60d5-4a1b-8a90-c09ba0bcd07a

yes that didn't solve what I was looking to do

The above formula is not giving me right information

lets take an example here

sheet 1

A                   B                   

abc inc           20,000           

xyz ltd            30,000

Qrs Co             40,000

wxy pvt           80,000

abc inc             10,000 

sheet 2

A                   B

abc inc           80,000

xyz ltd            40,000

Qrs Co             40,000

MnO inc           60,000

PQR               10,000

abc                 20,000

What I am looking to here is 

1) Identify the change like XYZ from  30k-40k

2) Mno and PQR added to sheet 2

3) wXy not in  sheet 2

4) shows right abc value

Hi,

According to your description, your request may need some macro via VBA code. I'm not familiar with that, thus, I recommend you post this question to MSDN forum:

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

Regards,

George Zhao
TechNet Community Support