Compare two Excel highlighting differences within the cells.

[x0141745]

Good afternoon,

I need to compare frequently different updated versions of the same worksheets sometimes with thousands of lines.
Sometimes the cells change, some lines are removed and others added and I can manage that without major issues.

The problem is when they introduce small changes within the cells (usually in some descriptions with more than 100 characters) and I can detect the cell change but not exactly where or what changed.
Sometimes it is only a space or an accent and this is a huge time-consuming check.

How can we compare highlighting simultaneously the cells and the differences within them?

Thanks in advance

 

[kevin9999]

Can you provide a copy of the two sheets in question using the XL2BB add in, or alternatively, share your file via Google Drive, Dropbox or similar file sharing platform? Are both sheets in the same workbook?

 

[x0141745]

Can you provide a copy of the two sheets in question using the XL2BB add in, or alternatively, share your file via Google Drive, Dropbox or similar file sharing platform? Are both sheets in the same workbook?

Hi,
Sorry for the late response.
Attached one example (side by side rev1 and rev2 of the same document and an using XL2BB (hopefully in the correct way)

As you can see sometimes there are small changes in the text (this text in the final version will be used in a contract) that must be carefully verified.
Usually we have 1500 to 2000 lines (and maybe 100 changes) but sometimes we have more than 20000 lines (and around 2000 changed!). Despite we can identify the cells that changed sometimes is very difficult to notice exactly where.


REV1

2-150-MQT_Rev01.xlsx
	C	D	E	F							M	N	O
83	C 3.1.1.1		Escavação geral em terreno de qualquer natureza (consultar relatório geotécnico) para obtenção de cotas e plataformas, uso de martelo sempre que necessário, incluindo carga e transporte de material sobrante a vazadouro ou depósito provisório autorizados, eventual entivação, bombagem da água afluente à escavação e regularização do fundo da caixa, bem como eventuais indemnizações e taxas associadas, conforme Caderno de Encargos.	m3							38.716,81
MQT

Cell Formulas
Range	Formula
M83	M83	=IF(ISTEXT(F83),SUMIF($B:$B,C83,$L:$L),0)
O83	O83	=M83*N83

REV2

2-150-MQT_Rev02.xlsx
	C	D	E	F							M	N	O
82	C 3.1.1.1		Escavação em terreno de qualquer natureza (consultar relatório geotécnico), para obtenção de cotas e plataformas, incluindo remoção, entivações, bombagem de águas e todos os trabalhos necessários e complementares (limite geométrico), assim como todos os trabalhos, equipamentos e materiais necessários à sua perfeita execução.	m3							38.716,81
MQT

Cell Formulas
Range	Formula
M82	M82	=IF(ISTEXT(F82),SUMIF($B:$B,C82,$L:$L),0)
O82	O82	=M82*N82

 

[kevin9999]

If you want to highlight the exact place in either text that differs from the other, then I'm afraid that's beyond my ability to help. Hopefully someone else on the forum can provide you with a solution. Good luck.

 

[rlv01]

This seems like something you would need to consider VBA for. Or else a 3rd party add-in.

Dose for Excel Features - Zbrainsoft

Discover powerful Excel tools with Dose for Excel. Browse workbooks quickly, eliminate duplicates, and simplify tasks with over 100 new functions.

zbrainsoft.com