User Defined function for comparing 2 array and return approximate match

Originally Posted by porucha vevrku

Please give an example of data (manager, officer, etc., data layout in columns and rows), not original of course, and the expected result.

Thank you for helping me out.. I have attached a file with List on the left and a sample compare list on the right column. The title is a part of a big list. I hope this will give you an idea of the data set.

Many thanks in advance.

Originally Posted by porucha vevrku

Please give an example of data (manager, officer, etc., data layout in columns and rows), not original of course, and the expected result.

Sorry the I forgot to put in the output column. Please find attached.

The compare list might be another page, so user-defined (titlelist,array to compare) or something in that line I am hoping to make.

Thanks again.

Is really the output array supposed to have multiple duplicate entries (manager, manager, manager, ..., officer, officer, officer, officer, etc.) ?

OK, try it, but this applies to this data layout, as in the attachment:
Look at the notes in the code (e.g.: For duplicates 'Exit For' commented / for unique specimens 'Exit For' uncommented)

Please Login or Register  to view this content.

Thank you the reply and help.

This i think maybe because I am not doing it right, it does not run through the entire compare list for each title cell. Plus it seems to match corresponding cell or i.e. a1 to f(i), then a2 to f(i)+1. Can you help me to tweak that please? and is there anyway we could let to make this as a user defined function { compare (list, compare list) } so that it can take in the compare list as a selection when working?? Just a thought if you can help, but the above code is a huge help if you can help to fix it to run for entire compare list for each title.

Capture.PNG

Thanks for the help.

Arun

1. If you are deleting some cell in "small input range", then value of this cell is "" (empty string, "nothing", but this is also a "value"), then in the structure:

Please Login or Register  to view this content.

we have: "If some_value Like "*" Then", which causes the empty value to be entered to the array variable (which is then entered into the worksheet).

It's easy to prevent it, but why are you actually deleting data in this area ?

2. You will never know how many cells will be with the result (unless you count them before), so you will be forced to mark more cells than necessary.

3. With the "unique" option you will have to select an output area equal to the "small input area"

4. With the "duplicate" option you will have to select an output area equal to the "bigger input area"

5. The formula will be entered as an array, i.e. with CSE (ctrl+shift+enter) key combination

Ok, try:

Please Login or Register  to view this content.

Form of the formula (in your case):

compare1Darray("smaller range","bigger range",unique)

1. For "unique" values
=compare1Darray(F2:F28,A2:A106)
or
=compare1Darray(F2:F28,A2:A106,"True")

2. For "duplicate" values
=compare1Darray(F2:F28,A2:A106,"False")

Thank you porucha vevrku. Will try that.