How to pick first entry on vlookup and the last entry in diffrent columns

[mercmannick]

Hi

i have Data imported in from SQL DB and it has multiple entries of a product No.

What i need to do on a seperate excel sheet is in Column I have the first entry on the Row
in Column J have the last entry from a vlookup is this possible

enclosed is a sample of what i am trying to do.

Thanks

[code
riref sched_machine sched_start sched_stop
100342 MOLLART 29/03/2011 08:00 29/03/2011 11:00
100342 MOLLART 29/03/2011 12:00 29/03/2011 14:00
100342 MOLLART 29/03/2011 15:00 29/03/2011 16:00
100342 MOLLART 29/03/2011 17:00 29/03/2011 18:00
103986 MOLLART 29/03/2011 19:00 30/03/2011 05:00
code]

 

[mercmannick]

All sorted thanks peter

{=IF(TRIM(C7)="","",MAX(IF(SCHEDULER!$B2:$B25000=TRIM(C7),SCHEDULER!$E2:$E25000)))}

have trailing zeros in col C

Thanks

 

[Peter_SSs]

yes you forgot to cover sheet name in 'sheetname'!

{=IF(C7="","",MAX(IF('SCHEDULER'!$B:$B=C7,'SCHEDULER'!$E:$E)))}

If the sheet name does not contain spaces (as is the case with SCHEDULER), then the ' ' marks are not required around the sheet name.

 

[mercmannick]

If the sheet name does not contain spaces (as is the case with SCHEDULER), then the ' ' marks are not required around the sheet name.

Ok thanks Peter

i have another little issue which i didn't foresee, it is doing exactly what i asked but.

i need it to only look at machine operations not just first operation and last operation start and stop times. as you will see from below

Excel Workbook
	B	C	E	F	G	H	I
2	M/C	ROLL No.	Mat	DATE	CUSTOMER	SCHED START	SCHED STOP
7	CR8	104356	DH4 * * * * * * * *	Apr	SMS * * * * * * * * * * * * * * * * * * * * * * *	29/03/2011 08:00	29/03/2011 16:00
8		105589	16VS * * * * * * * *	Apr	SKININGROVE	04/04/2011 10:00	05/04/2011 11:00
9		105564	AS5 * * * * * * * *	Apr	ANCHOR	30/03/2011 11:00	31/03/2011 10:00
Mini Plan (3)

this is my data im pulling from SQL

Excel Workbook
	A	B	C	D	E
5137	5215155	104356	CRAVEN8	29/03/2011 08:00	29/03/2011 16:00
5138	5215173	105589	CRAVEN8	29/03/2011 17:00	30/03/2011 01:00
5139	5215182	105590	CRAVEN8	30/03/2011 02:00	30/03/2011 10:00
5140	5215189	105564	CRAVEN8	30/03/2011 11:00	30/03/2011 13:00
5141	5215195	105564	CRAVEN8	30/03/2011 14:00	31/03/2011 10:00
SCHEDULER

As you can see i somehow need to just look at current Machine for stop time

could be anything up-to 5 operations on a machine so would need to look at machine first then COL B then end time in COL E

is there a way round doing this (by the way it works on this machine as its final operation is on this machine)

Thanks

 

[Peter_SSs]

Sorry, I can't really make sense of what you want. Can you post a slightly larger sample of SCHEDULER sheet together with the expected results and explanation of why those are the expected results.

Also, when using Excel jeanie, please

- use a copy of your workbook (or a dummy workbook) with just normal size font, not that very large font.

- note that you can use the ‘Analyse range (Forum)’ field near the top left of the Excel jeanie screen to restrict the number of formulas generated. In that field, you can use Ctrl+Click/Drag to select multiple disjoint ranges if required. There is generally no need to display multiple formulas that are basically the same, it just fills up the board. In fact, if these formulas are not producing the results you want, remove them and insert (manually) the results you do want.

 

[mercmannick]

Sorry, I can't really make sense of what you want. Can you post a slightly larger sample of SCHEDULER sheet together with the expected results and explanation of why those are the expected results.

Also, when using Excel jeanie, please

- use a copy of your workbook (or a dummy workbook) with just normal size font, not that very large font.

- note that you can use the ‘Analyse range (Forum)’ field near the top left of the Excel jeanie screen to restrict the number of formulas generated. In that field, you can use Ctrl+Click/Drag to select multiple disjoint ranges if required. There is generally no need to display multiple formulas that are basically the same, it just fills up the board. In fact, if these formulas are not producing the results you want, remove them and insert (manually) the results you do want.

Hi Peter

Excel Workbook
	B	C	D	E	F	G
2	M/C	ROLL No.	START	STOP	SHOULD BE START	SHOULD BE ST0P
7	CR8	104356	13/04 15:00	22/04 12:00	29/03 11:00	29/03 19:00
8		105589	06/04 07:00	22/04 13:00	29/03 20:00	30/03 04:00
9		*	*	*	*	*
10	POR1	105566	29/03 11:00	15/04 19:00	29/03 11:00	01/04 04:00
11		105567	01/04 05:00	21/04 03:00	01/04 05:00	06/04 10:00
12	NL4	105563	30/03 11:00	18/04 20:00	30/03 11:00	04/04 11:00
13		105539	04/04 12:00	20/04 18:00	04/04 12:00	06/04 09:00
14		*	*	*	*	*
15	HK2	105564	19/04 01:00	23/04 22:00	29/03 09:00	30/03 07:00
16		105565	22/04 12:00	22/04 17:00	30/03 09:00	31/03 07:00
17		*	*	*	*	*
20	HK1	101972	17/04 17:00	17/04 22:00	29/03 12:00	30/03 08:00
21		101973	18/04 00:00	18/04 05:00	30/03 09:00	31/03 05:00
22		*	*	*	*	*
Mini Plan (4)

here is the results bit you were asking for i will post the SQL data section next

Thanks

 

[mercmannick]

Excel Workbook
	B	C	D	E
5309	105589	CRAVEN8	29/03/2011 20:00	30/03/2011 04:00
5310	105590	CRAVEN8	30/03/2011 05:00	30/03/2011 13:00
9731	105566	POREBA1	29/03/2011 11:00	01/04/2011 04:00
9732	105567	POREBA1	01/04/2011 05:00	01/04/2011 19:00
9733	105567	POREBA1	04/04/2011 07:00	06/04/2011 10:00
SCHEDULER

here is selection of current DATA from SQL

it is all ROLL no based but each roll could go through a host of machines and each machine would or could have multiple operations each with its own start and stop time

Many Thanks

Nick

 

[mercmannick]

Anyone help on this issue Please

Thanks Nick

 

[Aladin Akyurek]

Excel Workbook
	B	C	D	E
5309	105589	CRAVEN8	29/03/2011 20:00	30/03/2011 04:00
5310	105590	CRAVEN8	30/03/2011 05:00	30/03/2011 13:00
9731	105566	POREBA1	29/03/2011 11:00	01/04/2011 04:00
9732	105567	POREBA1	01/04/2011 05:00	01/04/2011 19:00
9733	105567	POREBA1	04/04/2011 07:00	06/04/2011 10:00
SCHEDULER

here is selection of current DATA from SQL

it is all ROLL no based but each roll could go through a host of machines and each machine would or could have multiple operations each with its own start and stop time

Many Thanks

Nick

What are the desired results so we don't need to read the whole thread?

 

[mercmannick]

Hi Aladin Akyurek

the Sheet in post 15 is the results i am getting and the next two columns is the results i need

Many Thanks

 

[Aladin Akyurek]

Hi Aladin Akyurek

the Sheet in post 15 is the results i am getting and the next two columns is the results i need

Many Thanks

I now think I shouldn't have entered into such a lengthy thread...

However, you could make everybody happy if you provide very small sample along with the desired results with no formulas at all. Possible?