Comparing workbooks

In Excel under Options . . . Customize Ribbon you can select the 'Compare and Merge Workbooks' tool.

When this tool is activated you will be able to compare multiple versions of an Excel workbook.

It flags cells that have been edited and rows that have been inserted.

Replicating Entries Across Worksheets

Don't miss that in MS Excel you can replicate entries on one worksheet across multiple worksheets by selecting the cells you want to copy, selecting the worksheets the data should be sent to, and then going to Home . . . Fill . . . Across Worksheets.

You'll have the option of either copying the contents of the cells, just their format, or both.

Excel formula to compare how similar text is two cells is

Speeding Lunatic posted the below VBA code here, which you can use to check how similar text in one cell is to text in another cell. Put the code in a new module for your workbook and then enter the following formula:

=(MAX(LEN($A$1), LEN(A2)) - levenshtein($A$1,A2))/MAX(LEN($A$1), LEN(A2))

A1 is your source cell and A2 is the cell you are checking against it. Naturally you can pull down the formula in order to check how similar subsequent entries are. This formula is particularly good for finding where variations of a person's name appear in a column.

Function levenshtein(a As String, b As String) As Integer Dim i As Integer Dim j As Integer Dim cost As Integer Dim d() As Integer Dim min1 As Integer Dim min2 As Integer Dim min3 As Integer If Len(a) = 0 Then levenshtein = Len(b) Exit Function End If If Len(b) = 0 Then levenshtein = Len(a) Exit Function End If ReDim d(Len(a), Len(b)) For i = 0 To Len(a) d(i, 0) = i Next For j = 0 To Len(b) d(0, j) = j Next For i = 1 To Len(a) For j = 1 To Len(b) If Mid(a, i, 1) = Mid(b, j, 1) Then cost = 0 Else cost = 1 End If min1 = (d(i - 1, j) + 1) min2 = (d(i, j - 1) + 1) min3 = (d(i - 1, j - 1) + cost) d(i, j) = Application.WorksheetFunction.Min(min1, min2, min3) Next Next levenshtein = d(Len(a), Len(b)) End Function