View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.newusers
Ron Rosenfeld[_2_] Ron Rosenfeld[_2_] is offline
external usenet poster
 
Posts: 1,045
Default Cut Numbers Problem

On Sat, 14 Jul 2012 16:11:01 +0000, GER9999 wrote:

I am having trouble with this part of your suggestion:

where I1:I10 contain the letters above

Where is I1:I10 in the formula you gave?


=SUMPRODUCT(MATCH(MID(A1,{1,2,3,4,5},1),$I$1:$I$10 ,0)-1,10^{4,3,2,1,0})


$I$1:$I$10 refers to those cells using the Absolute Reference mode. See HELP for Absolute Reference for discussion as to different addressing modes. There is relative, mixed, and absolute.


I'm lost as to where to put
the letters T,A,N,D,U,W,R,I,G,M


Assuming you are using the A1 cell referencing mode, I1 refers to a particular cell on the sheet. It is the cell located in Column I / Row 1

So navigate to cell I1 and enter T.

I1: T
I2: A
I3: V
I4: D
I5: U
I6: W
I7: R
I8: I
I9: G
I10: M

You can put those letters in any ten contiguous cells that are in the same column.
You could also substitute the range reference in the formula with an array constant. I thought it was simpler to put the letters in a column than hard code them into the formula, but you could certainly do the latter:

=SUMPRODUCT(MATCH(MID(A1,{1,2,3,4,5},1),{"T";"A";" V";"D";"U";"W";"R";"I";"G";"M"},0)-1,10^{4,3,2,1,0})

Again, this assumes your 5 letter group is in A1. You will need to change the A1 reference depending on where your group is actually located.