IF and VLOOKUP formulas to get started

[Pottie8]

Hi All

I hope to receive as much guidance and help as possible on this platform

https://drive.google.com/drive/folders/0B9pqW92R9U4xLWkxek1zTGFZLUk?usp=sharing

I have attached the data and destination images and would just like so help with formulas to:

1. In destination enter user name just the first time even if it is in data more than once.
2. continue on the second line with the first instance of any different name etc.
3. populate column values based on data sheet.

If any further info is needed, please let me know.


Regards,
Pottie8

 

[Pottie8]

Hi Again

Just to note that I have managed to "convert" all text using datevalue and timevalue formulas.

Would you mind to share the EXACT formula you used to achieve result above?


Regards,
Pottie8*

 

[Marcelo Branco]

To convert text to dates (numbers) is not enough just to change the format to Date.
Try
Select the column dates
Data > Text to Columns
Delimited
Finish

Do the same for column Time
check again with =ISNUMBER(..)

M.

 

[Marcelo Branco]

Assuming named ranges in Worksheet (names = headers)

array formula in B6 copied down
=INDEX(results,MATCH(1,IF(students=A6,IF(dates+Time=MIN(IF(students=A6,dates+Time)),1)),0))
Ctrl+Shift+Enter

M.

 

[Pottie8]

Hi Marcello

Hope you are well. Thank you for all the input.

I have managed and it seems this is working, however I have been struggling for a few days as I get random blank cells inbetween, and can't figure out why...

It seems there is no pattern to this (but there must be) and the ones that does reflect is correct... Any further ideas?

I have shared a copy of the sheet as is on my google drive if you dont mind peaking and tweaking?

TestSheet.xlsx - Google Drive


Kindest Regards,
Pottie8

 

[Marcelo Branco]

I downloaded your file and found some problems
1. Column D (Total Time) in Worksheet is text
Use Data > TextToColumns to convert to numbers

2. The formulas in Results are not correct
Try
B6 copied down
=IFERROR(INDEX(Worksheet!$G$2:$G$999,MATCH(1,IF(Worksheet!$E$2:$E$999=A6,IF(Worksheet!$F$2:$F$999=$B$4,IF(Worksheet!$B$2:$B$999+Worksheet!$D$2:$D$999=MIN(IF(Worksheet!$E$2:$E$999=A6,IF(Worksheet!$F$2:$F$999=$B$4,Worksheet!$B$2:$B$999+Worksheet!$D$2:$D$999))),1))),0)),"")
Ctrl+Shift+Enter

Do the same in F6 changing $B$4 to $F$4
=IFERROR(INDEX(Worksheet!$G$2:$G$999,MATCH(1,IF(Worksheet!$E$2:$E$999=A6,IF(Worksheet!$F$2:$F$999=$F$4,IF(Worksheet!$B$2:$B$999+Worksheet!$D$2:$D$999=MIN(IF(Worksheet!$E$2:$E$999=A6,IF(Worksheet!$F$2:$F$999=$F$4,Worksheet!$B$2:$B$999+Worksheet!$D$2:$D$999))),1))),0)),"")
Ctrl+Shift+Enter

M.

 

[Pottie8]

I downloaded your file and found some problems
1. Column D (Total Time) in Worksheet is text
Use Data > TextToColumns to convert to numbers

2. The formulas in Results are not correct
Try
B6 copied down
=IFERROR(INDEX(Worksheet!$G$2:$G$999,MATCH(1,IF(Worksheet!$E$2:$E$999=A6,IF(Worksheet!$F$2:$F$999=$B$4,IF(Worksheet!$B$2:$B$999+Worksheet!$D$2:$D$999=MIN(IF(Worksheet!$E$2:$E$999=A6,IF(Worksheet!$F$2:$F$999=$B$4,Worksheet!$B$2:$B$999+Worksheet!$D$2:$D$999))),1))),0)),"")
Ctrl+Shift+Enter

Do the same in F6 changing $B$4 to $F$4
=IFERROR(INDEX(Worksheet!$G$2:$G$999,MATCH(1,IF(Worksheet!$E$2:$E$999=A6,IF(Worksheet!$F$2:$F$999=$F$4,IF(Worksheet!$B$2:$B$999+Worksheet!$D$2:$D$999=MIN(IF(Worksheet!$E$2:$E$999=A6,IF(Worksheet!$F$2:$F$999=$F$4,Worksheet!$B$2:$B$999+Worksheet!$D$2:$D$999))),1))),0)),"")
Ctrl+Shift+Enter

M.

You sir are a genius! THANK YOU THANK YOU THANK YOU!!!!!! Give that man a whiskey!!! Thank you!

 

[Marcelo Branco]

You sir are a genius! THANK YOU THANK YOU THANK YOU!!!!!! Give that man a whiskey!!! Thank you!

You are welcome. Glad to help.

M.