I am trying to do a comparison in Excel between 6 very large spreadsheets (approximately 1 million rows each) to try to find duplicate entries of the same files between two different devices. Essentially, there's a column for file path, and a column for the file size for each device. The major problem that I'm encountering is that the file paths are often different.
For example, one of the entries in Column A may be "2017 backup/To Sort/MT Cleanup/mt/2018-04-08.jpg", and in Column B it might have a file size of 5177Kb, In a different cell in Column C there may be an entry for "2017 SummerPhotos/To Sort/MT Cleanup/mt/2018-04-08.jpg" with Column D also showing a file size of 5177Kb.
Is there a tool that can do a comparison by partial cell name (as in matching the last two or three sections of the path), or do a comparison of the entries by the file size?