yogi_Find The String That Most Closely Matches One Of The Referenced Strings For The Largest Number Of Corresponding 1s

                                          Google Spreadsheet   Post  #1245
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 16, 2013
user Chris Mancuso (http://productforums.google.com/forum/?zx=n80b883gpakv#!category-topic/docs/spreadsheets/xfTY5o4UL9A)
Trying to find the closest match of a large value [string] in a column
Hello

I have a list of sensor data that is kept as a string of 500 numbers (1's or 0's only) in a column:

example:

0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100010000001000100000000000

0000000000000000000000000000000000000000000000000000000000000000000001010000010101111111100111010010100000100000001010

0000000000000000000000000000000000000000000000000000000000000000000000000000000000001100011000110111101011011111111111

1111111101110011010111111111110100100001010010000100001000001110001000000001010000010000000000000000000000000000000000

I am hoping to be able to input a string of the same size in a cell in another sheet and find the closest match from the column and return its position. Preferably, it would return the top 10 or so matches if possible.

I tried vlookup, but it doesnt handle a number that large. MATCH can only reliably return exact matches, which isn't likely to happen.

Any advice appreciated. I am a newbie at spreadsheet programming.

Thanks!

---

Hi again,

Here is the link for a sample sheet: https://docs.google.com/spreadsheet/ccc?key=0Ag9fKh6HAN_BdHdvSGNnNlFibnExeWZwTUNiVHVBQUE#gid=0

In cell E1 I am hoping to display the sample number from column A from the corresponding closest match. 

The values kept are in binary and are exactly 500 bits, this is the output from a sensor reading taken over a second, and I am hoping to make a reference database of scans so that unknown samples can be guessed with some accuracy.

Thanks again.

Chris

-------------------------------------------------------------------------------------------------------------------------------------

following is a solution using a number of helper (computed columns) shown with light brown background.