Pulling data into a cell with numerous Vlookup/conditions... this may be impossible

[helpmeplease2018]

Hi

I am not even sure my title is correct. I have a Labor tracker for my restaurant and when I pull all the data from the online portal it appears in a jumble mess (see from cell AN on wards).

I am trying to get the time in and out information into the cells I made, based on a day, name and job role of someone.

I am looking for it to search the persons name from (AN), the day they worked from (AS), the job title from (AN) and then pull the times in/out they worked that day from (AT/AU). And copy the times in/out into the DAYS correlating in my table based on days Mon - Sun . I am not even sure this is possible with this many conditions!

Note I would like to do this for all employees and sometimes on one shift their job role may change from Prep Cook to Line Cook.


Let me know if you have any questions!?

[dflak]

Obviously you are copying the information in Column AN from somewhere. The data must be in a consistent format

If I were to design a program to look for the key word "In Date" in column AT, then I know the name can be found 10 rows up and 6 columns over. I have to be able to depend in "In Date" being always in Column AT and the name being in Column AN, ten rows up. Can you confirm this? If so, we can use VB to "normalize" the data and a pivot table to report it.

[helpmeplease2018]

Hi.

Thanks for getting back to me. Yes I just copied and pasted all the data from a cvs file. So the location will be locked in as you mentioned. Also just a note about multiple job roles and if that is doable to?

Cheers!
Mateusz

[dflak]

I tried to make this as bullet-proof as possible. What you should do is clear out all data on the Raw Data Sheet and then Copy / Paste your data into column AN. Then on the control panel sheet, click on the Get Data Button.

This action will read the raw data, put it in the table on the Data Sheet, and then refresh the pivot table on the Pivot Sheet. The pivot table is your report.

I added some features to remove duplicate or overlapping data, and added a column and a slicer so you can select a week beginning date. If you select multiple dates, the report will just get bigger horizontally.