LinkBack URL
About LinkBacksvery simple, I would like to know how the match function could be used example:
125
457
706
match 457 = 1
any ideas
very simple, I would like to know how the match function could be used example:
125
457
706
match 457 = 1
any ideas
=COUNTIF(A1:A3,457)Originally Posted by stewart08
very simple, I would like to know how the match function could be used example:
125
457
706
match 457 = 1
any ideas
thanks close,
but need the number to reference from a row/colum value and in a un-ordered way.
A1
475
125
457
706
match "A1" = 1
I'm not clear what you are looking for
Could you give an example of what inputs give what output, or attach a sample spreadsheet.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
See enclosed
//Ola
heres a example:
http://img403.imageshack.us/my.php?image=figurelz9.jpg
the formula takes the nums 812 and trys to find a match which is 128 = 1
remember doubles are also used i.e 122 matches 212 = 1
mikerickson, the input is match 891 via 456, 981, 876 =1
hope this helps
Last edited by stewart08; 04-13-2008 at 06:44 AM.
Assuming A1 contains a 3 digit number and B1:B10 also has 3 digit numbers this will count the number of "matches"
=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1:B10,MID(A1,1,1),"",1),MID(A1,2,1),"",1),MID(A1,3,1),"",1)=""))
thanks daddylonglegs, this formula works beautifully.
once again this forum is a "life safer of time"...which I'm grateful for
daddylonglegs, one last word. can three formulas be futheir developed to match double, exact and un-ordered matches (see below).
if it's possible it will grately help in shorting the humanly processing time down.
find 568
789
644
632
782
865
061
(cell1) un-ordered: 1
(cell2) doubles :
(cell3) exact :
-------------------------
find 464
789
644
632
782
865
061
(cell1) un-ordered:
(cell2) doubles : 1
(cell3) exact :
-------------------------
find 632
789
644
632
782
865
061
(cell1) un-ordered:
(cell2) doubles :
(cell3) exact : 1
Last edited by stewart08; 04-13-2008 at 10:03 AM.
I'm not sure I understand what a "double" is. Is that because there are 2 4s in 644?
yes thats right any 3 numbers with two matching ones i.e. 776 767 677.
also If its possible could you add the OFFSET function which the matching size can be changed i.e.
(cell1) match num:
534
(cell2) match size:
3
(5 rows)nums array:
678
009
345
607
986
(cell) find un-ordered match = 1
(cell) find double match =
(cell) find exact match =
hope its not to complex
Last edited by stewart08; 04-13-2008 at 10:45 AM.
if you need any more information let me know....
Hi, Could three formulas be created to match double, exact
and un-ordered matches (with a match "OFFEST" function).
hope it's not to complex as this formula could save me a life time of calculating...
(input-cell1) match number:
534
(input-cell2) match size:
3
(5 rows)nums array:
678
009
345
607
986
(formula) find un-ordered match = 1
(formula) find double match =
(formula) find exact match =
other examples:
match double --------------------------------------------------------------------
(input-cell1) match number:
090
(input-cell2) match size:
5
(5 rows)nums array:
678
009
345
607
986
(formula) find un-ordered match =
(formula) find double match = 1
(formula) find exact match =
match exact --------------------------------------------------------------------
(input-cell1) match number:
607
(input-cell2) match size:
5
(5 rows)nums array:
678
009
345
607
986
(formula) find un-ordered match =
(formula) find double match =
(formula) find exact match = 1
Last edited by stewart08; 04-13-2008 at 03:25 PM.
Threads merged.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks