is it possible to set labels to pairs? for instance, I want the following pairs to be named the number "1":
00
19
28
37
46
55
05
14
23
69
78
Last edited by Jordans121; 03-03-2010 at 12:14 AM.
Pardon?....
When I put a certain pair in a column(00) I want another column to say "1"
It's still unclear. Can you please explain what you want. Post an example work with what you have and what you want.
Do you mean "when you enter one of the listed values in a cell"?? Then you want another cell on the same row to display a "1"??When I put a certain pair in a column(00)
With a list of your values in E1 to E11, and your search term in B2
=IF(ISBLANK(B1),"",IF(ISNA(MATCH(TEXT(B1,"00"),E1:E11,0)),"",1))
Example of what I want in attachment
try this in J4, copy right and down
=IF(SUM((($O$4:$O$14&""=TEXT(F4,"00"))*1)+(($P$4:$P$14&""=TEXT(F4,"00"))*2)+(($Q$4:$Q$14&""=TEXT(F4,"00"))*3)+(($R$4:$R$14&""=TEXT(F4,"00"))*4)+(($S$4:$S$14&""=TEXT(F4,"00"))*5))=0,"not found",INDEX($O$1:$S$1,SUM((($O$4:$O$14&""=TEXT(F4,"00"))*1)+(($P$4:$P$14&""=TEXT(F4,"00"))*2)+(($Q$4:$Q$14&""=TEXT(F4,"00"))*3)+(($R$4:$R$14&""=TEXT(F4,"00"))*4)+(($S$4:$S$14&""=TEXT(F4,"00"))*5))))
Can you please send me an example?
it is saying "not found"
In J4, and copy to right
You also need to make sure the numbers in the table are formatted as text. The attached has the table formatted as text. The pairs are auto-generated based on the number entered into the A column.="V"&(N(NOT(ISNA(MATCH(F4,$O$4:$O$14,0))))*1+N(NOT(ISNA(MATCH(F4,$P$4:$P$14,0))))*2+N(NOT(ISNA(MATCH(F4,$Q$4:$Q$14,0))))*3+N(NOT(ISNA(MATCH(F4,$R$4:$R$14,0))))*4+N(NOT(ISNA(MATCH(F4,$S$4:$S$14,0))))*5+N(NOT(ISNA(MATCH(RIGHT(F4,1)&LEFT(F4,1),$O$4:$O$14,0))))*1+N(NOT(ISNA(MATCH(RIGHT(F4,1)&LEFT(F4,1),$P$4:$P$14,0))))*2+N(NOT(ISNA(MATCH(RIGHT(F4,1)&LEFT(F4,1),$Q$4:$Q$14,0))))*3+N(NOT(ISNA(MATCH(RIGHT(F4,1)&LEFT(F4,1),$R$4:$R$14,0))))*4+N(NOT(ISNA(MATCH(RIGHT(F4,1)&LEFT(F4,1),$S$4:$S$14,0))))*5)/IF(LEFT(F4,1)=RIGHT(F4,1),2,1)
teylyn's formula does not take the re-arranging of the digits into account. Using the pair of 15, it will look for 15 but not 51 in the table.
OK. Thanks. This is what I needed
How do I paste the formula. I keep gettin V0
Last edited by Jordans121; 03-03-2010 at 12:01 AM.
Can you paste the formula for 5,000 rows for me? because I dont know how to do it. Thanks
Jordans, the formula I supplied works in the file that you posted.
yes it works but I dont know how to paste it
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks