Is there a simplified method for sorting extracted data??

[new@excel]

I apologize in advance if my title doesn't match what I am trying to accomplish. I am so close to perfecting (with the help of ChemistB & Dan_SJ) a schedule that will make things a lot easier for many. For the most part it works the way I want it to but it requires many help columns. I actually use a full help sheet for the sorting calculations on my Master Schedule workbook because it has a full week not just one day like my sample attached below. I am having a hard time explaining it on this post, but I did add text in my sample trying to explain. Please take a look and let me know if there might be a simplified method. Thank you!

[FDibbins]

Hi

I personally dont have a problem using helper columns, even if there are a lot of them. Looking atthose helpers, and their formulas, there is probably a way to condense them, but the resulting formula would be a night-mare to edit.

It might be a bit easier to see your concern if you include more than just 1 day's worth of data?

[Lemice]

Hello,

Here is a way, using Array formula. You don't have to use any helper column.

Sorry for the mess on format, I was dragging the formula ...

But as FDibbins said, there's nothing wrong with having extra helper columns (unless you have more than 10 of them).

[new@excel]

Thank you both FDibbins and Lemice for your quick responce and time. FDibbins, I tried once before to attatch the whole workbook but I couldn't due to size. I might try again but I think we are very close to being solved. Lemice, again thanks for your time! Your AWESOME Array formula just about wraps up this episode. Only one minor detail, "duplicates" in your solution under both AM and PM concessions NAME 9 and NAME 14 appear twice, everything else works without a hitch. Is that possible to fix? Thanks again!

[FDibbins]

If Lemice is giving you what you need, great If not, I didnt mean for you to upload the whole file, just add maybe 1 more day of data?

[new@excel]

Thanks FDibbins,! I only attached one day of data because I figured the rest of the week would be pretty much exactly the same with the only changes being column references. But I will attach another sample with more data asap. Thanks again!

[Lemice]

Here is the sample file with the fixed formula.

The good news is that, duplicated data is no longer a problem. However, there's a bad news too - now it's a ... mess, it doesn't sort by time / name / shift anymore, it will just grab whatever name come up first from the top to the bottom of the list on your first worksheet.

I actually tried to edit my own formula and gave up at first (lol), then I decided to take out the sorting part of it, and here we go.

So, yeah, it won't look as elegant as when it's sorted, but now you don't have to worry about false data!

[new@excel]

Thanks Lemice! I am wondering if a simple match or lookup function just for the staff member/name cells. Does that make sence and will it work? I am working on it now.

[Lemice]

Yes it make sense, because the name is unique in the sample, so you can actually just do a simple index match / vlookup base on the name to get other data.

[new@excel]

Oooops! Sorry Lemice I just noticed how much of a mess you meant. For some reason I was thinking only the names were not sorted and thought could use lookup/match to extract just the name matching the shift and hours. Maybe just one helper column to manipulate the row values and use as the small(reference) in your original sample?? I am still tinkering with it though. I have a hard time coming up with the proper formulas but I am able to modify them.

[new@excel]

Hey FDibbins, here is another copy of my sample with the added days (Saturday and Sunday). Unfortunately I didn't change the references/ranges int the formulas on the daily shift sheets because the results would have been just the same and I was trying to post this asap. Thanks again FDibbins!

[Lemice]

I was really uncomfortable with the failure to sort value, so here is another shot at it.

In the attached file, you will see no helper column (because they are not needed), duplicated data being handled, and the data being sorted by the Shift column.

However, I still cannot deal with sorting Staff name / member when Shift time is equal, so if there are Staff / Member having the same shift, whoever nearer to the top of the list will be grabbed first. I will try to handle that also if I can tomorrow.

[new@excel]

Thank you very much Lemice for your much appreciated time and effort!!!! I don't think that will be necessary sorting the staff names as long as the names match the shifts and positions. So if there are multiple employees starting at the same time in the same position it won't matter who's name is first. But thanks anyways. I am gonna go ahead and mark this solved. I am really grateful for people like you Lemice, FDibbins, ChemistB, Dan_SJ and SixthSence who all make time to help others and to have a site like this to do so. I can't thank you all enough, but Thank you!!!!

[Lemice]

I still am not happy with the results though, I still want them to at least sort by the name column also ...

But at first I want to make sure that it's working at least. Let me know if there's something wrong with it.

[Lemice]

Finally, here is a version where it's sorted automatically when the data is being pulled in, sort by Shift time first, then by Name

In the sample, you will see Name 10 stands before Name 9, here is why
With real name, for example Iron Man and Iron N, of course Iron Man will be chosen to stand before Iron N, because everything up to "Iron " is the same, but "M" stands before "N" alphabetically, similarly "1" stands before "9", making Name 10 stand before Name 9.

I'm very confident with this version, however considering the time I am posting this is nearly 2am Sunday morning, I won't say there is absolutely no flaw in it. But finding that flaw will be hard, I ensure you!

Edit: Talking about flaw, I forgot to change the formula in "hrs" column accordingly ...
I have changed the file.

[new@excel]

Hey Lemice, all I can say is wow!!!!! Everything seems to be working to perfection so far as I knew it would. I will let you know if I run into any problems and I will try to share the final result as soon as I am done editing. Seriously! Thank You!

[Lemice]

Glad that I could help you.

And also, thank you for the feedback!

Have a great day!