Thread: matching 8 cells to their counterparts

Hi,

I have a table which is 8 columns wide by approximately 1000 (+) rows. I am struggling with the following;

if I type in seven numbers somewhere I need to get returns on a full 8 cell match? (and possible partial, but exact matches to 4+out of 8 numbers) All the numbers in individual cells are no larger than 99 and (in theory) are arranged from left to right in ascending order.

Can anyone point me in the right direction?

I don't want to use auto filter as I am just looking for a reference that I am searching the correct number. Does that make sense?

I have as advised below now added an attachment.

Cheers

Si.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Doing this will ensure you get the result you need

!
possibly

hey - it's not me - it's a canned response

Apologies for delay in response.

My instinct would be to use a column adjacent to the data rows to ease the process of filtering the results... using your example file:

L2: =SUMPRODUCT(COUNTIF($B2:$I2,$B$28:$I$28))+(ROWS(L$2:L2)/1000)
copied down

In terms of listing the results - exact/close I would list all in one dataset but use the value in L to ensure that the data is listed in order of closeness in desc order... ie closest matches first.

Let's say then (again using your sample) that we hold in C31 the count of records to be returned...

C31: =COUNTIF($L$2:$L$12,">1")

Then I can populate the table of results as follows

A35: =IF(ROWS(A$35:A35)>$C$31,"",MOD(LARGE($L$2:$L$12,ROWS(A$35:A35)),1)*1000)
copied down as far as required.

B35:
=IF(ISNUMBER($A35),INDEX(B$2:B$12,$A35)*(COUNTIF($B$28:$I$28,INDEX(B$2:B$12,$A35))>0),"")
copied across and down as far as required (ie across numbers matrix - up to and incl. column H)
To hide the 0's apply a custom format to this range of: #;;

J35:
=IF(ISNUMBER($A35),INDEX($J$2:$J$12,$A35),"")
copied down as far as required

So you should then find that using your sample if you begin to add a close match in row 4 the results table updates as you enter the values - resorting the data so closest is listed first etc...

Originally Posted by opsman

I think I get the logic, but what is in my head doesn't compute to what's in your formula... can you explain the formula for L2, I think I can work through it from there?

The formula essentially conducts a COUNTIF for each value in a given row against the predetermined range of 8 values... it SUMS the results... then to that result it adds the row number being processed / 1000 such that each value listed in L is unique ... ie if you had 2 rows containing 5 matches you can distinguish between the two sets of values via the use of ROW... no 2 in L values will be the same (assumes you have less than 1000 rows of data... if you have more than that increase the divisor such that the ROW/divisor is always less than 1, a safe divisor may be 1000000)

Originally Posted by opsman

Secondly, and not wishing to irritate you after being unbelievably helpful, I got a circular reference error which I got around by changing the iteration, will that slow the function down?

Not sure how you're generating circulars... you may need to post an example.

Originally Posted by opsman

Finally, I put some random numbers in the table before considering moving it to the main file and was quite shocked with the high ammount of returns I got. Is there anyway of limiting it for example to the higher half of returns or for 4 or 5+ matches only??

Yes, simply change C31 COUNTIF criteria from >1 to say >=4 etc... where the 4 dictates the min. count of matches required to be listed

Originally Posted by opsman

.. hope the weather is better in suffolk than in North Wales!

Unless you're drowning, no, not presently!
(I'm not drowning incidentally - it's just raining, hard...)