Nov 04 2019 04:23 PM
Nov 04 2019 07:27 PM
@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.
Nov 05 2019 02:20 AM
@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!
Nov 05 2019 06:33 AM
@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.
Nov 05 2019 11:09 AM - edited Nov 08 2019 12:11 AM
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.
Nov 05 2019 11:42 AM
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!
Nov 05 2019 11:44 AM
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!
Nov 05 2019 12:49 PM
Can you upload a sample? It sounds like the references need to be sorted out.
Nov 05 2019 01:55 PM
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.
Nov 06 2019 04:59 AM - edited Nov 06 2019 05:23 AM
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.
Nov 06 2019 05:10 AM
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.
Nov 06 2019 05:27 AM
@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.
Nov 06 2019 06:18 AM
@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.