I have some number groups. I have also 5 numbers. I want to find numbers of
lines in groups, include these 5 numbers together? Can I do it only by using
one cell and formule? I explained my question in detail by added file. Thanks.


http://www.turboupload.com/vxyi4wnu1..._line.xls.html

--
Message posted via http://www.officekb.com

I'm not quite sure what you are looking for, and I didn't download the
workbook (I don't download things from strangers, nothing personal).
If you are trying to identify a certain sequence of numbers appearing
in a longer list of numbers, try something like the following array
formula:

=IF(SUM(--(OFFSET(B3,0,0,ROWS(WhatNumbers),1)=WhatNumbers))= ROWS(WhatNumbers),"series
here","")

Here, B3 is the first cell of the longer list of numbers that you are
going to search for the occurrence of a sequence. WhatNumbers is a
named range containing the digits in the sequence you want to find.
These digits should be in a single column spanning 5 rows. Enter this
formula in C3 and fill down as far as you have data in the large list.
If your sequence numbers to be found, the WhatNumbers range, are in
one row spanning 5 columns, use the following array formula.

=IF(SUM(--(OFFSET(B3,0,0,COLUMNS(WhatNumbers),1)=TRANSPOSE(W hatNumbers)))=COLUMNS(WhatNumbers),"series
here","")

Enter this in C3 and fill down as far as you have data in the large
list. Both of these formulas return "series here" in the first cell in
which the sequence in WhatNumbers is found in the larger list. So, for
example, if your sequence in WhatNumbers was 66510, the "series here"
will appear on the same row as the first 6 as found in the larger
list.

Change "series here" to whatever you want.

Since these are Array Formulas, you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula
and whenever you edit it later. If you do this properly,
Excel will display the formula in the Formula Bar enclosed
in curly braces { }. (You do not type the curly braces -
Excel includes them automatically.) The formula will not work
properly if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots
more information about array formulas.

If this is not what you're looking for, please provide more details
and example in the newsgroup post, not in a file.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sun, 12 Apr 2009 18:05:32 GMT, "alikirca20" <u51090@uwe wrote:

I have some number groups. I have also 5 numbers. I want to find numbers of
lines in groups, include these 5 numbers together? Can I do it only by using
one cell and formule? I explained my question in detail by added file. Thanks.


http://www.turboupload.com/vxyi4wnu1..._line.xls.html