Two excel sheet comparison and show the result in a new sheet

[Chito]

Hi,

I am seeing lot of other thread regarding two sheet comparison but could not found what I am looking for. Few points I am looking for are..

Lets say two sheets are there, source & traget, then:

1. Insert a new sheet and show pass/fail with all the records from source and target by shorting out.

1. When a row is not there in source, indicate the row is not in source, similarly from target as well.

Please find the attached excel for example....

[arlu1201]

Try this

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[Chito]

Hi Arlette,

Thanks for quick response, I have tried the above macro and it is giving me the result almost I wanted. I am having few concerns, please suggest me on this...

• "Missing on target or source" indication is not showing, please check the attached excelsheet. If you can include thw Row no# which is missing, that would be great.

• Comparison is limited to only 3 column, if I wish to increase then which part of code I should modify? Or can the code decide based on data?

• Is it possible to indicate or highlight what value oe cell no. is not matching in the case of missmatch?

Thanks so much...

[arlu1201]

• "Missing on target or source" indication is not showing, please check the attached excelsheet. If you can include thw Row no# which is missing, that would be great.

- Will look into this.

• Comparison is limited to only 3 column, if I wish to increase then which part of code I should modify? Or can the code decide based on data?

- The code is not flexible. Currently, it checks the 1st 3 columns.

• Is it possible to indicate or highlight what value oe cell no. is not matching in the case of missmatch?

- How do you want to indicate?

[jindon]

See if the attached is what you wanted.

[Chito]

• Is it possible to indicate or highlight what value oe cell no. is not matching in the case of missmatch?

- How do you want to indicate?

Please ignore this one and could you provide me the code to validate 8 columns if we can not make this flexible?

---------- Post added at 02:12 PM ---------- Previous post was at 02:10 PM ----------

See if the attached is what you wanted.

Yes one works but looking for flexible columns, currently restricted to only 3 column. If you can not make it flexible then happy to get for 8 column. thanks

[jindon]

Yes one works but looking for flexible columns, currently restricted to only 3 column. If you can not make it flexible then happy to get for 8 column. thanks

No idea about what you are trying to do.
If you upload the file and the result that you want, it may help to understand your problem.

[Chito]

No idea about what you are trying to do.
If you upload the file and the result that you want, it may help to understand your problem.

Hi Jindon,
The attachment (Compare Excel Sheet With Code.xlsm‎) which you have provided earlier, that works for me if I want to compare two sheets having only 3 columns.

Now what I am looking for is, to compare any two sheets having same no. of columns but not limited to only 3 columns. The corrent code does not give this flexibility. If you can provide me this flexibility, then well and good. If not, could you please provide me the code which can compare two sheets having 8 column with the same functionality you have provided earlier.

Thanks

[jindon]

So what is the result that you want?

All I need to see is not just 3 columns of data and the result that you have provided already.

[Chito]

Hi Jindon,

Here is the sample data for a 8 column, this is what I am looking for. Again as I said earlier, not limited to only specific column, that would be great.


Thanks for your response.

[jindon]

I don't understand about "Mismatch_Target" and "Mismatch_Source".

Need your logical explanation.

[Chito]

I don't understand about "Mismatch_Target" and "Mismatch_Source".

Need your logical explanation.

I was also thinking the same but did not add explanation to it.
"Mismatch_Target" - I want to indicate that, it is mismatch and particular row (data) is from "Target"
"Mismatch_Source" - Similarly, it is mismatch and likes to indicate that particular row (data) is from "Source"

This is just to differentiate which one is Source & Target when there in mismatch between them. You can add better description to make it easier instead of those two.

[jindon]

What combination of columns makes unique key?

[Chito]

What combination of columns makes unique key?

In the sample excel sheet which I have attached, combination of column A, C, E and G can make the unique key. However, combination all these columns are also unique only, nothing will go wrong when we consider all the columns.

[Chito]

Any luck anyone???