Search similar values

[lionginass]

Hello,

I have an issue.
There are column of numbers

12345
23456
66666
77777
98543

All of these numbers were entered manually.

The last number was mistyped. It should be 96543 instead of 98543.
So my questions is: Is it possible to find similar values in array formula or vba? i cannot find any info in google about that.
Lets say if 4 numbers match, formula returns TRUE or 1. Dont need exact location

Help with a hint where to find info about this or how to google formula?


Thank you

 

[Toadstool]

Hi Lionginass,

I don't see how you could identify a typing mistake without further information.

There is, however, an old accounting trick if your books don't balance and you suspect a transposition error (the most common type of entry error).
Subtract the expected total from the actual and divide by 9. If it's exactly divisible by 9 then it's probably a transposition error and the result of the division tells you where.

In this example the expected total is 278,787 but I'm getting 276,087. Subtracting gets 2,700 which is divisible by 9 and returns 300, so this tells me the transposition is between the thousands and hundreds digits, in this case I entered 95843 instead of 98543.

Lionginass.xlsx
	H	I	J	K	L
1	Good	Bad		Good SUM	278,787
2	12345	12345		Bad SUM	276,087
3	23456	23456		Subtract	2,700
4	66666	66666		Divide by 9	300
5	77777	77777
6	98543	95843
Sheet1

Cell Formulas
Range	Formula
L1	L1	=SUM(H2:H6)
L2	L2	=SUM(I2:I6)
L3	L3	=L1-L2
L4	L4	=L3/9