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]

 

[Peter_SSs]

Your sample seems to provide nothing by way of clarification to either your thread title or your description. Without that I certainly cannot provide a suggestion.

Also, are you looking for a formula or a macro solution?

 

[mercmannick]

Your sample seems to provide nothing by way of clarification to either your thread title or your description. Without that I certainly cannot provide a suggestion.

Also, are you looking for a formula or a macro solution?

Hi Peter

any solution really

what i am after is being able to take the sched start time from first row for job no 100342 (29/03/2011 08:00)

and transfer that information to col i on another sheet by way of formula currently using

(=IF(ISERROR(VLOOKUP(TRIM($C7),SCHEDULER!$B:$J,3,0)),"",(VLOOKUP(TRIM($C7),SCHEDULER!$B:$J,3,0))) )

all this does is pick up first entry 29/03/2011 08:00 which is ok on col i i need end time for job no 100342 which is (29/03/2011 18:00)

so in affect i have 1 row with start time and end time

Many Thanks

 

[mercmannick]

Excel Workbook
	A	B	C	D
1	riref	sched_machine	sched_start	sched_stop
2	100342	MOLLART	29/03/2011 08:00	29/03/2011 11:00
3	100342	MOLLART	29/03/2011 12:00	29/03/2011 14:00
4	100342	MOLLART	29/03/2011 15:00	29/03/2011 16:00
5	100342	MOLLART	29/03/2011 17:00	29/03/2011 18:00
SCHEDULER (2)

Excel Workbook
	C	D	E	F	G	I	J
90	100342	*	RX9X	Apr	EFS	31/03/2011 18:00	31/03/2011 18:00
Mini Plan (2)

What i am after is Col J all i can get with VLOOKUP is start time i need to be able to get emd time of last operation which is in SCHEDULER TAB cell D5


Thanks Nick

 

[Peter_SSs]

See if you can adapt something like this. Note that the G3 formula is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Excel Workbook
	A	B	C	D	E	F	G
1						Job	100342
2	100342	MOLLART	29/03/2011 8:00	29/03/2011 11:00		Start	29/03/2011 8:00
3	100342	MOLLART	29/03/2011 12:00	29/03/2011 14:00		Stop	29/03/2011 18:00
4	100342	MOLLART	29/03/2011 15:00	29/03/2011 16:00
5	100342	MOLLART	29/03/2011 17:00	29/03/2011 18:00
6	103986	MOLLART	29/03/2011 19:00	30/03/2011 5:00
7
Start Stop

 

[ravi4ever]

Also, you can try this to acheive the same..

<TABLE style="WIDTH: 355pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=474 border=0 x:str><COLGROUP><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" span=2 width=111><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=49 height=17>riref</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=99>sched_machine</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 83pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=111>sched_start</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 83pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=111>sched_stop</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 78pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=104>Result</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>100342</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">MOLLART</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40631.333333333336">3/29/11 8:00 AM</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40631.458333333336">3/29/11 11:00 AM</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40631.75">3/29/11 6:00 PM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>100342</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">MOLLART</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40631.5">3/29/11 12:00 PM</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40631.583333333336">3/29/11 2:00 PM</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40631.75"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>100342</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">MOLLART</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40631.625">3/29/11 3:00 PM</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40631.666666666664">3/29/11 4:00 PM</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40631.75"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>100342</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">MOLLART</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40631.708333333336">3/29/11 5:00 PM</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40631.75">3/29/11 6:00 PM</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="40631.75"></TD></TR></TBODY></TABLE>

formula in E2
=LOOKUP(2,1/($A$2:$A$1000=A2),$D$2:$D$1000)

 

[mercmannick]

See if you can adapt something like this. Note that the G3 formula is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Excel Workbook
	A	B	C	D	E	F	G
1	*	*	*	*	*	Job	100342
2	100342	MOLLART	29/03/2011 8:00	29/03/2011 11:00	*	Start	29/03/2011 8:00
3	100342	MOLLART	29/03/2011 12:00	29/03/2011 14:00	*	Stop	29/03/2011 18:00
4	100342	MOLLART	29/03/2011 15:00	29/03/2011 16:00	*	*	*
5	100342	MOLLART	29/03/2011 17:00	29/03/2011 18:00	*	*	*
6	103986	MOLLART	29/03/2011 19:00	30/03/2011 5:00	*	*	*
7	*	*	*	*	*	*	*
Start Stop

Hi thanks for reply Peter

how do i amend Formulas to look at different sheet ?

{=IF(G2="","",MAX(IF(A2:A20=G1,D2:D20)))}


Thanks

 

[Peter_SSs]

how do i amend Formulas to look at different sheet ?

If my table is on a sheet called 'Start Stop' then the formulas would become

Excel Workbook
	F	G
1	Job	100342
2	Start	29/03/2011 8:00
3	Stop	29/03/2011 18:00
Results

 

[mercmannick]

Hi peter

using

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

and bringing back all zeros am i missing something

Thanks

 

[ravi4ever]

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

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

Also instead of using B:B or E:E, you should define a particular range..

{=IF(C7="","",MAX(IF('SCHEDULER'!$B1:$B65536=C7,'SCHEDULER'!$E1:$E65536)))}