(Msg. 1) Posted: Mon Dec 08, 2008 4:38 pm
Post subject: Formula to find the last number in a series Archived from groups: microsoft>public>excel>worksheet>functions (more info?)

Hello,

I have the following list of number and need to find the last number that
starts with 1006? How do I do that? Also if I search for numbers beginning
with 1008 I need to know that there is none present.

(Msg. 2) Posted: Mon Dec 08, 2008 6:55 pm
Post subject: Re: Formula to find the last number in a series [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Numbers in col J
=SUMPRODUCT((LEFT(TRIM($J$2:$J$22),4)="1006")*1)
if numbers in col L
=SUMPRODUCT((LEFT(TRIM($J$2:$J$22),4)=TEXT(L4,"0000"))*1)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"OX_Gambit" wrote in message

> Hello,
>
> I have the following list of number and need to find the last number that
> starts with 1006? How do I do that? Also if I search for numbers
> beginning
> with 1008 I need to know that there is none present.
>
> 1005001
> 1005002
> 1006001
> 1006002
> 1006003
> 1007001
> 1007002
>

(Msg. 3) Posted: Mon Dec 08, 2008 7:02 pm
Post subject: Re: Formula to find the last number in a series [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

OX_Gambit wrote:
> Hello,
>
> I have the following list of number and need to find the last number that
> starts with 1006? How do I do that? Also if I search for numbers beginning
> with 1008 I need to know that there is none present.
>
> 1005001
> 1005002
> 1006001
> 1006002
> 1006003
> 1007001
> 1007002
>

If, by "last number" you mean the highest number and they may not be sorted
ascending, try this array-entered formula:

=MAX((LEFT(A1:A7,4)="1006")*A1:A7)

This formula will result in 0 for "beginning with 1008" with your data.

(Msg. 4) Posted: Mon Dec 08, 2008 7:52 pm
Post subject: Re: Formula to find the last number in a series [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Maybe this:

=LOOKUP(2,1/(LEFT(A1:A7,4)="1006"),A1:A7)

>Also if I search for numbers beginning with 1008
>I need to know that there is none present.

The above formula will return #N/A.

--
Biff
Microsoft Excel MVP

"OX_Gambit" wrote in message

> Hello,
>
> I have the following list of number and need to find the last number that
> starts with 1006? How do I do that? Also if I search for numbers
> beginning
> with 1008 I need to know that there is none present.
>
> 1005001
> 1005002
> 1006001
> 1006002
> 1006003
> 1007001
> 1007002
>

(Msg. 5) Posted: Mon Dec 08, 2008 8:59 pm
Post subject: Re: Formula to find the last number in a series [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Q1: use formula
=INDEX(A1:A10,MAX(IF(INT((A1:A10)/1000)=1006,ROW(A1:A10),0)))
As this is an array formula, commit it with CTRL+SHIFT+ENTER (not just
ENTER)
Change A1:A100 to suit your needs, for not use full column reference like
A:A unless you have Excel 2007

Q2: use array formula
=IF(MAX(IF(INT((A1:A10)/1000)=1008,ROW(A1:A10),0)),"found","not found")

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"OX_Gambit" wrote in message

> Hello,
>
> I have the following list of number and need to find the last number that
> starts with 1006? How do I do that? Also if I search for numbers
> beginning
> with 1008 I need to know that there is none present.
>
> 1005001
> 1005002
> 1006001
> 1006002
> 1006003
> 1007001
> 1007002
>
>

(Msg. 6) Posted: Wed Dec 10, 2008 4:13 pm
Post subject: Re: Formula to find the last number in a series [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

What if the list of numbers are numbers not text?

"T. Valko" wrote:

> Maybe this:
>
> =LOOKUP(2,1/(LEFT(A1:A7,4)="1006"),A1:A7)
>
> >Also if I search for numbers beginning with 1008
> >I need to know that there is none present.
>
> The above formula will return #N/A.
>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "OX_Gambit" wrote in message
> > > Hello,
> >
> > I have the following list of number and need to find the last number that
> > starts with 1006? How do I do that? Also if I search for numbers
> > beginning
> > with 1008 I need to know that there is none present.
> >
> > 1005001
> > 1005002
> > 1006001
> > 1006002
> > 1006003
> > 1007001
> > 1007002
> >
>
>
>

(Msg. 7) Posted: Wed Dec 10, 2008 4:22 pm
Post subject: Re: Formula to find the last number in a series [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

What does the 1/(LEFT(A1:A7,4) do to the text?

"T. Valko" wrote:

> Maybe this:
>
> =LOOKUP(2,1/(LEFT(A1:A7,4)="1006"),A1:A7)
>
> >Also if I search for numbers beginning with 1008
> >I need to know that there is none present.
>
> The above formula will return #N/A.
>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "OX_Gambit" wrote in message
> > > Hello,
> >
> > I have the following list of number and need to find the last number that
> > starts with 1006? How do I do that? Also if I search for numbers
> > beginning
> > with 1008 I need to know that there is none present.
> >
> > 1005001
> > 1005002
> > 1006001
> > 1006002
> > 1006003
> > 1007001
> > 1007002
> >
>
>
>

(Msg. 8) Posted: Wed Dec 10, 2008 4:28 pm
Post subject: Re: Formula to find the last number in a series [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

OX_Gambit wrote...
>What if the list of numbers are numbers not text?

Did you try the formula before deciding it couldn't work?

All times are: Eastern Time (US & Canada) Goto page 1, 2

Page 1 of 2

You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum