Array Formula - To determine exact matches

If you provide the sample data with NAME, DATE and TIME then we can pick the last entry by locating the End Time of the day and will be helpful for giving single cells formula without the need of any helper columns.

Hi,

As suggested, I've attached the spreadsheet.

Just wanted to check if you got a chance to review it.

Request you to please advise.

Thank you!

In cell F2

Formula:

Please Login or Register  to view this content.

drag the formula down

The above formula will get the TIME of last card swipe during the day.

Hope this helps!

Thanks for the super quick response Sixthsense. This is working fine, but there is one thing..I'm sorry if I wasn't clear in my request earlier.

The IDs are case sensitive for ex ANILB659U and ANILB659u are seprate and therefore, I also need the ones that I've coloured in yellow in the attached after applying the formula.

Could you please review and advise, and also is it possible to return the word "Export" (Highlighted in yellow) for these ones, while "Batch" for the other (highlighted in green).

Please advise...

Thanks in advance!

Please check your column-A data which is not real timing. Some of them are real times and some of them are text Numbers. (Never Align (Left, Right, Ceter) numbers especially when it is involved in calculation. Because, we forget to notice whether it is real number or text data). To convert the Column-A data to real number in one shot – Select the Column-A and Press Alt+D+E>>Next>>Next>>Fininsh and apply the below formula.

In F2 cell – Array Formula – Should be entered with Ctrl+Shift+Enter

Formula:

Please Login or Register  to view this content.

Drag it down.

Hope this helps!

Thanks Sixthsense,

It is working absoulutely fine.

Just the last bit needs to be sorted I guess...

Is it possible to return the word "Export" (Highlighted in yellow) for these ones, while "Batch" for the other (highlighted in green). Maybe using another Helper column. If yes, request you to please advise.

And, yes, I’ll keep your suggestion regarding the formatting in mind…

Please advise...

Thanks in advance!

File with Time Formatted and the latest formula applied attached. Thank you!

Revised F2 cell Formula – Array - Should be entered with Ctrl+Shift+Enter

Formula:

Please Login or Register  to view this content.

G2 cell Formula

Formula:

Please Login or Register  to view this content.

Hope this helps!

It's pleasure to help!

If you don't mind could you please show your expected output in an attachment which you did earlier.

Sure, just logging in my system. It will be there in the next 5 minss.

Thanks a bunch!

Thank you again!

Here is the sheet with the last of the conditions. Please advise.

Regards,
Jai

We will resume it by today morning, since it's already Midnight 12+. Now it's time for me to go to bed.

Sure, sure...Thanks a bunch for all your help on this! Good night.

I'll lookout for your expertise on this.

Best regards

Hey Sixthsense,

Hope you had a good sleep!

Just wondering if you were able to spare sometime on the last bit of my request..

Request you to suggest...

Kind regards,
Lifeisaspreadsheet

Surely I will get back to you, please wait a little bit time...

Sure, Sure...no probs at all. I'll stay tuned! Thanks:-)

You can replace the G2 cell formula with the below formula or if you wants to keep G2 cell formula, then paste the below formula in H2 cell.

Formula:

Please Login or Register  to view this content.

Drag the formula down.

Hope this helps!

Thank you, Thank you!

I'll run this against my data and get back to you shortly.

I'll mark it as "SOLVED" if all goes as expected. I'm sure it will.
Thanks a bunch Sixthsense:-)

You rock. Cheers!!!

Dear Sixthsense,

This is working beautifully! Don't really know, if I can thank you enough:-)

But my bad...I should have thought of this earlier...I realized it only when I applied this on my data sheet which had some zillion rows….

There are scenarios where a single employee during the day steps out for several Official Work visits in this case on applying the formula I'm getting "Export" as the output in Column H. But, I really require the "Official Work" break with the latest time in such scenarios.

I'm terribly sorry, but this just came up. The rest all is working absolutely wonderfully!!!

Thanks a ton for your efforts on helping me solve this monster out:-)

Attached is the worksheet with the latest Formula Applied in Column H, and current status in Column I.

Thanks a million...

Thanks for your feedback.

Just a quick confirmation is required from you. I am ready to release the final formula for the below condition

I really require the "Official Work" break with the latest time

but before that I would like to know whether the column-F & Column-G results also needs to be arrived by including the above criteria OR you just need formula for H2 cell alone for getting the "Export" Text.

I’m doing further testing and technically everything else seems to be working fine, except for the fact that the sheet has become too heavy. I would say let’s leave the rest as is, as I‘m getting the required results, unless there is a single formula which can take care of all the conditions…

Thanks a ton!

But if the Condition: There are scenarios where a single employee during the day steps out for several Official Work visits in this case on applying the formula I'm getting "Export" as the output in Column H. But, I really require the "Official Work" break with the latest time in such scenarios.

Can be incorporated in the exsisting Formula it will make my life a bit easy I guess...all the other exsisting conditions remaining the same...

In F2 cell - Array Formula, Requires Ctrl+Shift+Enter

Formula:

Please Login or Register  to view this content.

In G2 cell - Array Formula, Requires Ctrl+Shift+Enter

Formula:

Please Login or Register  to view this content.

Refer the attached file for details.

Save your file in .xlsb extension when you are working with huge data.

Please let us know in case of any changes.

AWESOME!!!

I've tested it quite a bit and so far everthing seems to be in order now. I'll do another round tomorrow and dayafter and will get back in case any further asistance is required on this one.

I really appreciate your understanding and support on making this work. I know this one was a bit tricky at least for me

I believe that this will go a long way in helping me streamline some of the stuff and enable me work at a higher efficiency level.

Thank you for your expertise on this monster. You are the “BESTEST”

Best regards…

Dear Sixthsense,

I'm testing my data (large chunk) with this...It seems to be working most of the time.

But, I seem to face an issue. Looks like there is something that either I'm doing incorrectly or missing out on.

I've attached the sheet with some more data (Row 20 onwards). Request you to review Row 23 and Row 28. In this case, only ROW 28 should be exported but, due to some reasons both are getting exported.

Also, when I manually change the status from Official Work to any other say "Tea Break" or even leave it blank in D21 and D28 (highlighted in yellow), the formula seems to start working in the required manner...

Please advise...

Thanks!

Dear Sixthsense,

Hope you had a great weekend!

I had a query while testing the data yesterday, which I posted..

Could you please look into it and advise (at your convenience). It looks like I may be doing something incorrectly (file attached yesterday).

Thanks,
Jai

Thanks for your Greetings

Yes, I have seen your message and I will get back to you shortly.

Thanks! I will lookout for your inputs.

Regards,
Jai

Revised G2 cell Formula - Array Formula Requires Ctrl+Shift+Enter

Formula:

Please Login or Register  to view this content.

Drag it down.

Hope this helps!

Thank you Sixthsense. I'll do a full on test in the next 2 days and will close the thread as "SOLVED"

Thanks for all your efforts. Much much appreciated:-)

Kudos to you!

Thanks for your feedback and don't stop posting until your requirement is fulfilled.

Sure, will get back on this if any further assistance is required or anything else pops up.

Glad to be a part of such a great Forum and be in touch with members like you! Pure genius:-)

Thanks a lot!

Hi Sixthsense,

I'm here today to conevy a "BIG THANK YOU" to you rather than anything else. It was a great experience for me to learn some tricks . The solution you provided has been of immense help and importance. It is working as expected.

However, I'm facing a challange to get the accurate time populated on this sheet. The "Time" actually comes from a different source, and without a unique ID or something to be able to get it on this solution using VLOOKUP or LOOKUP or MAX.

I need to concatenate several fields to get the time, but as that piece is not too consitent it creates a lot of issues. Don't really know how to tackle that without a unique field, moreover there number of enteries in both the reports are different making it all the more challenging.

I'm not sure, if there is another way by which it can be easily populated. The data difference between the 2 reports is quite high on certain days. I just wish this wasn't the case.

Nevertheless, the solution is awesome and quite useful. Just wondering if there is another way out to get the time on this piece to automate the process to a great extent.

Thank you so much. I'm going ahead and closing this thread as "Solved", but do let me know if you think there could be a work around getting the time factor...

Best regards,
Jai

Thanks for your feedback

If the above requirement is connected with the formula which I have suggested then you please continue posting your query in this thread with sample data and expected output. If it doesn't involves all the above critieria's which we have discussed so for, then post a new question so that you will get more hands for finding the solution for your problem.

Please remember the forum rules...

12. Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

Originally Posted by jeffreybrown

Please remember the forum rules...

Please accept my regret and now I realize how much time it has taken for you for making this lengthy post neat and clear. I am seeing lot of your editing work in the above posts and assure you that I won't do it again.

Thank you for reminding the Forum Rule and stopping me from doing this unknowingly.