To All Excel Experts

Sir there are big possibiities of having the same last name... how bout having the nearest match if the name is too long

Perhaps we can use the last name and only the first name (e.g. Hipolito, Marcos). We can use this to get that part: LEFT($A4&" ",FIND(" ",$A4&" ",FIND(",",$A4&" ")+2)) and add "*" to make it a wildcard search.

remember this formula sir NBVC
=IFERROR(VLOOKUP(TRIM(SUBSTITUTE(J4,"¦","")),$BA$1:$BB$15,2,0),IFERROR(TIMEVALUE(SUBSTITUTE(J4,"¦","")),""))

using that and the LEFT() to find part name... the formula becomes:

In B4 (time in):

=IFERROR(VLOOKUP(TRIM(SUBSTITUTE(INDEX(QPRTCON522321!B:B,MATCH(LEFT($A4&" ",FIND(" ",$A4&" ",FIND(",",$A4&" ")+2))&"*",QPRTCON522321!$A:$A,0)),"¦","")),$BA$1:$BB$15,2,0),IFERROR(TIMEVALUE(SUBSTITUTE(INDEX(QPRTCON522321!B:B,MATCH(LEFT($A4&" ",FIND(" ",$A4&" ",FIND(",",$A4&" ")+2))&"*",QPRTCON522321!$A:$A,0)),"¦","")),""))

in C4 (time out):

=IFERROR(VLOOKUP(TRIM(SUBSTITUTE(INDEX(QPRTCON522321!C:C,MATCH(LEFT($A4&" ",FIND(" ",$A4&" ",FIND(",",$A4&" ")+2))&"*",QPRTCON522321!$A:$A,0)+1),"¦","")),$BA$1:$BB$15,2,0),IFERROR(TIMEVALUE(SUBSTITUTE(INDEX(QPRTCON522321!C:C,MATCH(LEFT($A4&" ",FIND(" ",$A4&" ",FIND(",",$A4&" ")+2))&"*",QPRTCON522321!$A:$A,0)+1),"¦","")),""))

then select B4:C4, and copy across and down the rest of the table.

This only looks at the first 2 rows for each name in the QPRT.. sheet. I don't know how you want to include the 3rd row.
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Ok, I think I understand the 3rd row now.

Try these formulas instead....

In B4:

=IFERROR(VLOOKUP(TRIM(SUBSTITUTE(INDEX(QPRTCON522321!B:B,MATCH(TRIM(LEFT($A4&" ",FIND(" ",$A4&" ",FIND(",",$A4&" ")+2)))&"*",QPRTCON522321!$A:$A,0)+2),"¦","")),$BA$1:$BB$15,2,0),IFERROR(TIMEVALUE(SUBSTITUTE(INDEX(QPRTCON522321!B:B,MATCH(TRIM(LEFT($A4&" ",FIND(" ",$A4&" ",FIND(",",$A4&" ")+2)))&"*",QPRTCON522321!$A:$A,0)),"¦","")),""))

in C4:

=IFERROR(VLOOKUP(TRIM(SUBSTITUTE(INDEX(QPRTCON522321!B:B,MATCH(TRIM(LEFT($A4&" ",FIND(" ",$A4&" ",FIND(",",$A4&" ")+2)))&"*",QPRTCON522321!$A:$A,0)+2),"¦","")),$BA$1:$BB$15,2,0),IFERROR(TIMEVALUE(SUBSTITUTE(INDEX(QPRTCON522321!B:B,MATCH(TRIM(LEFT($A4&" ",FIND(" ",$A4&" ",FIND(",",$A4&" ")+2)))&"*",QPRTCON522321!$A:$A,0)+1),"¦","")),""))

Select both B4 and C4 together, then copy them across the rest of the columns. Then copy down the rest of the rows.

Now if you have Day-off, you should get D-off returned, etc...
 
Last edited:
Upvote 0
I adjusted second formula in above post... i had mis-referenced it. It is fixed now.
 
Upvote 0
Ok, I think I understand the 3rd row now. Try these formulas instead.... In B4: =IFERROR(VLOOKUP(TRIM(SUBSTITUTE(INDEX(QPRTCON522321!B:B,MATCH(TRIM(LEFT($A4&" ",FIND(" ",$A4&" ",FIND(",",$A4&" ")+2)))&"*",QPRTCON522321!$A:$A,0)+2),"¦","")),$BA$1:$BB$15,2,0),IFERROR(TIMEVALUE(SUBSTITUTE(INDEX(QPRTCON522321!B:B,MATCH(TRIM(LEFT($A4&" ",FIND(" ",$A4&" ",FIND(",",$A4&" ")+2)))&"*",QPRTCON522321!$A:$A,0)),"¦","")),"")) in C4: =IFERROR(VLOOKUP(TRIM(SUBSTITUTE(INDEX(QPRTCON522321!B:B,MATCH(TRIM(LEFT($A4&" ",FIND(" ",$A4&" ",FIND(",",$A4&" ")+2)))&"*",QPRTCON522321!$A:$A,0)+2),"¦","")),$BA$1:$BB$15,2,0),IFERROR(TIMEVALUE(SUBSTITUTE(INDEX(QPRTCON522321!B:B,MATCH(TRIM(LEFT($A4&" ",FIND(" ",$A4&" ",FIND(",",$A4&" ")+2)))&"*",QPRTCON522321!$A:$A,0)+1),"¦","")),"")) Select both B4 and C4 together, then copy them across the rest of the columns. Then copy down the rest of the rows. Now if you have Day-off, you should get D-off returned, etc...
what will be the formula if i have extracted different cutoff of dates and the extracted data are like in this format 8:31 but in the formula bar it is 8:31:00 am
 
Upvote 0
Thank sir...

Ur very great...

How bout the succedding date like July 2 in cell D4? how it will capture the data?

As I mentioned, select B4 and C4 together, then copy those two across to the right. You should automatically get the July 2, July 3, etc results. Then copy row 4 down to get results for other people.

what will be the formula if i have extracted different cutoff of dates and the extracted data are like in this format 8:31 but in the formula bar it is 8:31:00 am

I don't understand exactly what you mean here. Please elaborate with examples.
 
Upvote 0
Thanks Sir... very much appreciated...

GOD BLESS


As I mentioned, select B4 and C4 together, then copy those two across to the right. You should automatically get the July 2, July 3, etc results. Then copy row 4 down to get results for other people.



I don't understand exactly what you mean here. Please elaborate with examples.
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top