How to compare the excel value displayed in different formats

[krkarthik_info]

Hi All,

I have attached an excel report. I need to compare the data from Sheet1 with Sheet2 and provide the mismatch details.

For E,g.,

Compare the data's from column Compound ID, Compound and Formula  from Sheet1 with Sheet2 with data from Column Compound Id, Compound and Formula.

Similarly Compare the data's from column Spectrum ID, Filter and RT from Sheet1 with Column Spectrum Id, Filter and Retention Time data in Sheet2.

In the attached, I have given the data only for two compounds. But in actual I will have lots of compounds.

Looking for a pattern to compare the report and a possible solution.

Thanks in advance.

Thanks,

Karthik K R

[joseph_michaud]

We have How To articles describing how to read values in Excel spreadsheets

Compare Excel files

Quickly Compare Excel Files

Search for Text in MS Excel

Looking at your Excel file, I'd say that reading the data is going to be the easy part.  It looks like the work is just going to be accounting for the different formatting on the two sheets and the fact that some values have been rounded.

[Colin_McCrae]

You already asked this?

https://community.smartbear.com/t5/Desktop-Testing/How-to-compare-the-cell-value-in-excel-file/m-p/1...

As was said on the previous thread, and by @joseph_michaud above, you're going to have to normalise/standardise the text components before you can compare them. There isn't a magic bullet that's going to do it for you I'm afraid.

[krkarthik_info · Accepted solution]

Hi Colin,

For this post or problem statement "How to compare the excel value displayed in different formats", I have come up with a logic to find the comparison.

Initially I thought its going to be difficult, but later on I found a pattern to compare the values and write the mismatches to the different excel file.

Regarding the post "How to compare the cell value in excel file?", its about comparing the cell value having normal string and superscript and subscript text. Even for this post, I have come up with a workaround to compare by finding the unicode characters.

Thanks for the suggestions.

Thanks,

Karthik K R