Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Find a First Occurence in a Column

As a manufacturer, we need to keep track of what items we made, how much, and
when. So, to simplify the complex task, as we have hundreds of products over
many years, look at the example below:

A B C
1 Date Red Bicycles Blue Bicycles
2 1/3/10
3 1/4/10 12
4 1/5/10 4
5 1/6/10 2
6 1/7/10
7 1/8/10 8

So, in effect, I'd like to find out that we first made 12 Red Bicycles on
1/4/10 and that the last time we made Red Bicycles was on 1/6/10 and made 2
of them. Same thing with Blue Bicycles, first made 4 on the 5th and last
time we made blue ones was on the 8th with 8 made.

Any suggestions would be appreciated and I'll click "yes it helped" to any
of your posts that help.

-Kevin

  #2   Report Post  
Posted to microsoft.public.excel.misc
Eva Eva is offline
external usenet poster
 
Posts: 197
Default Find a First Occurence in a Column

Hi
This formula checks data in column C (Bicycles) and returns last occurances

=INDIRECT("c"&MAX(IF(C2:C100,ROW(A2:A10))))

Apply same to Red Bicycles Blue
--
Click yes if helped
Greatly appreciated
Eva


"Kevin Barrios" wrote:

As a manufacturer, we need to keep track of what items we made, how much, and
when. So, to simplify the complex task, as we have hundreds of products over
many years, look at the example below:

A B C
1 Date Red Bicycles Blue Bicycles
2 1/3/10
3 1/4/10 12
4 1/5/10 4
5 1/6/10 2
6 1/7/10
7 1/8/10 8

So, in effect, I'd like to find out that we first made 12 Red Bicycles on
1/4/10 and that the last time we made Red Bicycles was on 1/6/10 and made 2
of them. Same thing with Blue Bicycles, first made 4 on the 5th and last
time we made blue ones was on the 8th with 8 made.

Any suggestions would be appreciated and I'll click "yes it helped" to any
of your posts that help.

-Kevin

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 269
Default Find a First Occurence in a Column

A B C
1 Date Red Blue
2 1/3/2010
3 1/4/2010 12
4 1/5/2010 4
5 1/6/2010 2
6 1/7/2010
7 1/8/2010 8
8
9 Red First Blue First
10 1/4/2010 1/5/2010
11 12 4
12 Red Last Blue Last
13 1/6/2010 1/8/2010
14 2 8

For A10 use an array formula
{=INDEX($A$2:$A$7,MATCH(TRUE,B2:B70,0))}
Type the formula without { and } and use Ctrl+Shift+Enter to enter and the
curly brackets will appear - you cannot type these yourself and have the
formula work.
You must Ctrl+Shift+Enter whenever you edit the formula
This can be copied over

For A11 use a simple VLOOKUP with a MATCH for column number (so you can copy
w/o changing the column)
=VLOOKUP(B10,$A$2:$C$7,MATCH(B1,$A$1:$C$1,0))
This can be copied over

For A13 another array formula (same note as for A10)
{=LOOKUP(2,1/(B2:B70),$A$2:$A$7)}
This can be copied over

For A14 another VLOOKUP
=VLOOKUP(B13,$A$2:$C$7,MATCH(B1,$A$1:$C$1,0))
This can be copied over


--
If this helps, please remember to click yes.


"Kevin Barrios" wrote:

As a manufacturer, we need to keep track of what items we made, how much, and
when. So, to simplify the complex task, as we have hundreds of products over
many years, look at the example below:

A B C
1 Date Red Bicycles Blue Bicycles
2 1/3/10
3 1/4/10 12
4 1/5/10 4
5 1/6/10 2
6 1/7/10
7 1/8/10 8

So, in effect, I'd like to find out that we first made 12 Red Bicycles on
1/4/10 and that the last time we made Red Bicycles was on 1/6/10 and made 2
of them. Same thing with Blue Bicycles, first made 4 on the 5th and last
time we made blue ones was on the 8th with 8 made.

Any suggestions would be appreciated and I'll click "yes it helped" to any
of your posts that help.

-Kevin

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Find a First Occurence in a Column

Try these...

For red bicycles

For the first date you specifically made 12 red bicycles:

=INDEX(A2:A7,MATCH(12,B2:B7,0))

If you just want to find the first date you made *any* red bicycles:

Array entered** :

=INDEX(A2:A7,MATCH(TRUE,ISNUMBER(B2:B7),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

For the last date you made *any* red bicycles:

=LOOKUP(1E100,B2:B7,A2:A7)

Format all of the formulas cells as Date.

Use the same technique for the blue bicycles, just change the column
references as needed.

--
Biff
Microsoft Excel MVP


"Kevin Barrios" wrote in message
...
As a manufacturer, we need to keep track of what items we made, how much,
and
when. So, to simplify the complex task, as we have hundreds of products
over
many years, look at the example below:

A B C
1 Date Red Bicycles Blue Bicycles
2 1/3/10
3 1/4/10 12
4 1/5/10 4
5 1/6/10 2
6 1/7/10
7 1/8/10 8

So, in effect, I'd like to find out that we first made 12 Red Bicycles on
1/4/10 and that the last time we made Red Bicycles was on 1/6/10 and made
2
of them. Same thing with Blue Bicycles, first made 4 on the 5th and last
time we made blue ones was on the 8th with 8 made.

Any suggestions would be appreciated and I'll click "yes it helped" to any
of your posts that help.

-Kevin



  #5   Report Post  
Posted to microsoft.public.excel.misc
Eva Eva is offline
external usenet poster
 
Posts: 197
Default Find a First Occurence in a Column

This formula finds you first occurance.
for both formulas please enter:Ctrl+Shift+ener
=INDIRECT("c"&MIN(IF(C2:C100,ROW(A2:A10))))
--
Greatly appreciated
Eva


"Paul C" wrote:

A B C
1 Date Red Blue
2 1/3/2010
3 1/4/2010 12
4 1/5/2010 4
5 1/6/2010 2
6 1/7/2010
7 1/8/2010 8
8
9 Red First Blue First
10 1/4/2010 1/5/2010
11 12 4
12 Red Last Blue Last
13 1/6/2010 1/8/2010
14 2 8

For A10 use an array formula
{=INDEX($A$2:$A$7,MATCH(TRUE,B2:B70,0))}
Type the formula without { and } and use Ctrl+Shift+Enter to enter and the
curly brackets will appear - you cannot type these yourself and have the
formula work.
You must Ctrl+Shift+Enter whenever you edit the formula
This can be copied over

For A11 use a simple VLOOKUP with a MATCH for column number (so you can copy
w/o changing the column)
=VLOOKUP(B10,$A$2:$C$7,MATCH(B1,$A$1:$C$1,0))
This can be copied over

For A13 another array formula (same note as for A10)
{=LOOKUP(2,1/(B2:B70),$A$2:$A$7)}
This can be copied over

For A14 another VLOOKUP
=VLOOKUP(B13,$A$2:$C$7,MATCH(B1,$A$1:$C$1,0))
This can be copied over


--
If this helps, please remember to click yes.


"Kevin Barrios" wrote:

As a manufacturer, we need to keep track of what items we made, how much, and
when. So, to simplify the complex task, as we have hundreds of products over
many years, look at the example below:

A B C
1 Date Red Bicycles Blue Bicycles
2 1/3/10
3 1/4/10 12
4 1/5/10 4
5 1/6/10 2
6 1/7/10
7 1/8/10 8

So, in effect, I'd like to find out that we first made 12 Red Bicycles on
1/4/10 and that the last time we made Red Bicycles was on 1/6/10 and made 2
of them. Same thing with Blue Bicycles, first made 4 on the 5th and last
time we made blue ones was on the 8th with 8 made.

Any suggestions would be appreciated and I'll click "yes it helped" to any
of your posts that help.

-Kevin



  #6   Report Post  
Posted to microsoft.public.excel.misc
Eva Eva is offline
external usenet poster
 
Posts: 197
Default Find a First Occurence in a Column

This formula will give you the date of last occurance

=INDEX(Table,MAX(IF(C2:C100,ROW(A2:A10))),1)

This formula will give you the date of first occurance

=INDEX(Table,MIN(IF(C2:C100,ROW(A2:A10))),1)

For both formulas enter:ctrl+Shift+Enter
--
Click yes if I helped
Greatly appreciated

Eva


"Kevin Barrios" wrote:

As a manufacturer, we need to keep track of what items we made, how much, and
when. So, to simplify the complex task, as we have hundreds of products over
many years, look at the example below:

A B C
1 Date Red Bicycles Blue Bicycles
2 1/3/10
3 1/4/10 12
4 1/5/10 4
5 1/6/10 2
6 1/7/10
7 1/8/10 8

So, in effect, I'd like to find out that we first made 12 Red Bicycles on
1/4/10 and that the last time we made Red Bicycles was on 1/6/10 and made 2
of them. Same thing with Blue Bicycles, first made 4 on the 5th and last
time we made blue ones was on the 8th with 8 made.

Any suggestions would be appreciated and I'll click "yes it helped" to any
of your posts that help.

-Kevin

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find Last Occurence bill78759 New Users to Excel 6 April 27th 09 04:08 AM
find 2nd occurence Totti Excel Discussion (Misc queries) 6 November 18th 08 02:42 AM
Find the row number of name occurence [email protected] Excel Worksheet Functions 9 October 3rd 08 08:34 AM
Find Last Occurence in a Range with VBA jlclyde Excel Discussion (Misc queries) 2 October 1st 08 07:04 PM
Trying to FIND lowercase or uppercase of target occurence u473 Excel Worksheet Functions 3 August 23rd 07 11:08 PM


All times are GMT +1. The time now is 02:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"