Sunday, June 16, 2013

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   Jun 16, 2013
user Chris Mancuso (!category-topic/docs/spreadsheets/xfTY5o4UL9A)
Trying to find the closest match of a large value [string] in a column

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:



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.

Hi again,

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.
following is a solution using a number of helper (computed columns) shown with light brown background.