Condense rows based on alphabetic value, Display Max & Min values in other columns

[FAHall]

Hey Guys,

I build a weekly schedule for about 300 staff each week in excel. Employees work on multiple projects, and every employee has a row for each project.

As a result, each employee has multiple rows.

I have attached a stripped down sample of what we use.

What I need is to compress each person into a single row with their earliest start time and latest end time listed. The task they are working on is not important for this particular use.

For instance, Alex's Tuesday schedule would show 3:00pm and 11:00pm in a single row.


Is there a formula, filter, or macro that can rapidly convert a schedule of 300+ employees from multi-row entries into 1 row per employee with the data I need?

Thanks in advance for any help you can offer,

-Alex

[stanleydgromjr]

FAHall,

Is there a formula, filter, or macro that can rapidly convert a schedule of 300+ employees from multi-row entries into 1 row per employee with the data I need?

Can you manually create what the results should look like on Sheet2 for the data shown on Sheet1?

[FAHall]

I attached what you asked for Stanley.


I've also worked out another possible solution for this. It involves manually running an advanced filter, and then using formulas to pull the data.

My formula is kind of clunky, but if you can add what I need, it will solve my problem. Here's what I've got:

=MIN(IF('Labor Hours'!AT10:AT1217=B5,'Labor Hours'!H10:H1159))


My problem with this formula is that I need it to only pull H10:H1159 if the cell value isn't blank.

It's pulling blanks as 12:00AM, which doesn't work.

How can I add a second criteria to my IF function to use only nonblank cells?

[Marcol]

Does this workbook do what you need?

[FAHall]

Does this workbook do what you need?

Nope. Looks like that pulls earliest start and latest end time for the whole week. I need it for each day.

Fancy formulas though.

I'm trying to give the staff a schedule that is easier to read. Rather than breaking down each of their tasks, they just need to know what time they need to be in the building.

[Marcol]

Okay, try this.
With Sheet2
In A4

=LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX(Sheet1!$A$5:$A$1000,MATCH(TRUE,INDEX(ISNA(MATCH(Sheet1!$A$5:$A$1000,$A$3:$A3,0)),0),0))))

Drag/Fill Down as required.

In B4 this array formula

=IF($A4="","",IF(SUMIF(Sheet1!$A$5:$A$1000,$A4,Sheet1!B$5:B$1000)=0,"",
IF(B$3="Start",MIN(IF(--(Sheet1!$A$5:$A$1000=$A4),IF(Sheet1!B$5:B$1000>0,Sheet1!B$5:B$1000))),
IF(B$3="Finish",MAX(IF(--(Sheet1!$A$5:$A$1000=$A4),Sheet1!B$5:B$1000)),""))))

Confirm with Ctrl+Shift+Enter not just Enter.
Drag Across to Column V, then Down as required.

Hope this helps.

[FAHall]

AWESOME!!!!

This fixed it perfectly. Thank you so much!

-Alex

[Marcol]

Happy to have helped.