Table help

[Rix123]

I have a table with my staff roster on it. Names down the side, days of the week on the top.

Depending on the different departments the staff are working on, means the shifts can be on 1 to 4 different rows.

What would be the best way to manipulate the data so all of a staff members shifts for the week were on one row?

I have tried VLookUps but only returns the first row of any person. Which misses shifts for any secondary rows!

I have tried getting the first shift of any day into the first row, but if a person (person 2 say) has a day off (no shift at all) then it puts person number 3s shift, onto person 2s row.

Can anyone help me with a fix/formula for this please?

[#2]

@Rix123   It might require some juggling of the table you have, but unless I'm mistaken, you could use the DGET database function to extract the data for each person for each day. If you upload your table with the staff roster, I could take a look and see if I can structure it....or if you're adventurous, you can go for it.

[#3]

@mathetesThanks for the suggestion.

I have attached a mock up of the kinda thing i'm looking at, GDPR n all that can have peoples names and such about.

This is basically how the report is created. every week i then manually move all the shifts so that each staff member only has one line.

I'm looking to set up an excel sheet so i can dump in the raw data, then have it automatically move all data for each staff member to be on one line.

Any help would be incredible!

[#4]

@Rix123  OK, I've used DGET in this attached revised spreadsheet. It does work, but as I say in one of the comment boxes, it's quite labor intensive to establish. There are ways (using Find....Replace) to accelerate that work, copying formulas and revising the cell references to the criteria ranges.

As is often the case with Excel, there is more than one way to achieve your objective.  I have a strong hunch that INDEX and MATCH could also be used, perhaps a lot more readily. I'm less familiar with them, and will have limited time the rest of this week, so perhaps another person with expertise in those functions can chime in here.

[#5]

@Rix123 

Try this array.

[#6]

@Rix123 

Offering you a third option in case you are open to VBA. The attached workbook contains a macro that scans your data row-by-row. It copies and pastes the entire next row if the employee name is the same (skipping blanks) on to the current row.

It loops until the "Next name" is blank and then exits the routine.

Press the "Merge Roster" button and see if the outcome is what you need. I tested adding some employees and it seems to work just fine without the need to adjust any coding.

Text edited 8 Nov 2019:

@Rix123 I changed the code a bit so that the roster is sorted by Name first. New employees can be added without having to update any code or formula. 

New workbook with correct code attached to replace an incorrect upload I did earlier.

[#7]

Thanks for the offer @Riny_van_Eekelen 

Only issue is this seems to have not saved/loaded properly.

When i open it, i just get two sheets with the original information on. Nothing else, no buttons!

[#8]

Looks brilliant @Patrick2788 

My issue is that when i try to move the final roster, so i can have more staff on the sheet, the formula seems to go a bit mad. Turned the results to 0, instead of the found results!

[#9]

@Rix123 

Can you upload a sample? It sounds like the references need to be sorted out.

[#10]

@Rix123 

Power Query could work - assign named range to roster with some gap for extension (or even better to use table), query it; unpivot other than name columns; pivot on dates without aggregation; load result back to Excel sheet.

Attached is the sample.

[#11]

@Patrick2788   

I like this, that a single formula can be used: =IFERROR(INDEX(B$1:B$8,SMALL(IF($A$1:$A$8=$A13,IF(B$1:B$8<>"",ROW($B$1:$B$8))),1),1),"")

It's elegant, but hard to read.

I get that SMALL returns the specified (nth) smallest value within a given array...could you put into words what's happening in this formula, where you've nested an IF within the SMALL, in order to get the first argument for the INDEX function. It would help me, and I'm sure the original poster, learn how to create such an elegant solution.

P.S. Since first writing that, I've put a bit of time into seeing if I can do that "translation" myself. So here goes: basically all of that is filling the cell where the formula resides with a blank until it gets to the first instance (SMALL) of a cell in that column (in the reference roster) where the name in column A still matches and there's text. At which point it returns the row and column number to INDEX, and fills the cell with that non-blank text.

I love it! An elegant (albeit hard to read) solution. 

[#12]

@mathetes 

The Small array is essentially finding the row number based on multiple criteria (Staff member and cells not blank).  The INDEX serves as the container for the array.

[#13]

@Patrick2788  Yep. Thanks for the lesson. I'd not seen the SMALL function before. My intro to Excel was decades ago, and I retired 17 years ago so although I've continued to be an active user (mostly for tracking finances and investing), I've only recently purchased an up-to-date manual to catch up on the many functions and capabilities added in the interim.

[#14]

@Rix123   Are you still having the issue where copying the formula to the larger roster wasn't working. I do think that the solution offered by @Patrick2788 was the most suited to your need. Much better than mine, if only because of the greater ease with extending it.

His solution, that formula, should work if you just adjust the dimensions of the array references to the larger sizes of your actual roster.