find which row best matches the criteria

**smky_j** · 09-03-2010, 04:04 AM

Hi!

I have a situation where I need to know which row among sixty rows best matches my target row. The rows contain strings as in the example below...

Item1 Item2 Item3 Item4
Row1 A B C D
Row2 B B A C
Row3 A C B B
Row4 A B C A

Row 1 (target row) is most similar to row4 since three of the items are similar in both rows. I tried using many helper cell to execute all iterations but this proved to be too cumbersome. Is there a way I can have a cell return row best match a target row. Note that all rows are possible target for comparison.

Thanks in advance,

Smokey

**zbor** · 09-03-2010, 04:07 AM

What is more similliar compared to ABCD:

ABBB which have first two on right positions
or
DCBA which don't have right position but have all 4 values

**smky_j** · 09-04-2010, 08:36 PM

Zbor,

Sorry for the late reply, --weekend got me a little tied up.

ABBB is more similar. Like an answer key, a target row is compared for every item with each--say 100 rows in the range. Only in an answer key, you only compare one row after that you're done. My problem is after comparing one row, I move to the next row and compare it with the rest of the 100 rows in the range, and so on until I'm done with all of the rows in the range.

I tried achieving it through many helper cell using a lot if IF statements, even creating a table of matrix to prevent duplicate comparisons. I'm sure there is a better way of doing this but right now, I'm really stumped because some other solution I can think of is just as cumbersome.

Thanks for the help.

**zbor** · 09-05-2010, 03:51 AM

You can add this into next column (row 2,3,4 etc): =SUMPRODUCT(--(B$2:E$2=B3:E3)) and pull down...

You can also by index match pull out row that matching criteria or you can higlight them as in eample.

**zbor** · 09-05-2010, 02:46 PM

Donkeyote, can I ask you here how to make SUMPRODUCT work in this case...

This is idea: =SUMPRODUCT(--(A$1:D$1=A2:D17);ROW(A1:D17)) (to comibe all rows somehow)...

OK.. So I would like to see all rows from 2 to 17 and multiply first row by 1, second by 2 etc... Obviously here is only 17 rows (no matter on A1:D17... it's same as A1:A17)... But if I put A1:A64 it also doesn't work because first part looks like (Evaluating formula) ie:
0\1\1\0;1\1\1\1:0\1\0\1; etc.

In other words it's separated in quarters.. So I would like to know what's syntax in that case

Even if it can be solved in another way I would like to see how to make it work somehow

**DonkeyOte** · 09-06-2010, 02:38 AM

zbor - as much as I am flattered by the personal request I must confess I don't really understand the requirements.

Are you saying you want to multiply the n match results of A1:D1=A2:Dn by 1 to n-1 ?
(note you have 16 match results rather than 17)

eg:

A1: A A C D
A2:...
A3: A B C D

For the iteration of row 3 I presume you are looking to return 6 (1*2, 0*2, 1*2, 1*2), correct ?

If so - given the dimension differences of the arrays (ie n x 4 vs n x 1) use * rather than double unary.
given there's no potential for invalid coercion there's little reason to avoid multiplication (ie we're just multiplying booleans & integers)

Please Login or Register  to view this content.

Does that help ?

If you wanted to avoid need for multiplication (ie persist with double unary) then to get the 2nd array to also conform to n x 4 dimension there are a few possibilities, eg:

Please Login or Register  to view this content.

**zbor** · 09-06-2010, 03:40 AM

Regardless to the issue I just couldn't adjust ranges and I see it was because of double array... So I just wanted to see how it works... 'Cause I had some idea at the time and couldn't get it work

But I see now it doesn't work anyway so no big deal about it

Thx for the help, you helped a lot...

Oh, yes, just for a feedback idea was to give 1's for matches in first row, 2's for second etc...

=SUMPRODUCT(($B$2:$F$2=$B$3:$F$18)*(ROW($A$1:$D$16)))

But I'm giving up now.. Waiting to see if user is satisfied with answer or need some improvements

Thx again!

**smky_j** · 09-06-2010, 06:44 PM

Bless you sirs! I am right now replacing my million helper cells with your suggestions. ..will feedback as soon as I get something right.

Many thanks!

Originally Posted by zbor

Regardless to the issue I just couldn't adjust ranges and I see it was because of double array... So I just wanted to see how it works... 'Cause I had some idea at the time and couldn't get it work

But I see now it doesn't work anyway so no big deal about it

.....

....Thx again!

**DonkeyOte** · 09-06-2010, 06:48 PM

Lightweight helper cells are no bad thing - a multitude of SUMPRODUCTs on the other hand...

find which row best matches the criteria

LinkBack

Thread Tools

Rate This Thread

Display

find which row best matches the criteria

Re: find which row best matches the criteria

Re: find which row best matches the criteria

Re: find which row best matches the criteria

Re: find which row best matches the criteria

Re: find which row best matches the criteria

Re: find which row best matches the criteria

Re: find which row best matches the criteria

Re: find which row best matches the criteria

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions