Lotus wrote:Instead of:
Robert Smith, Sales, 01/01/2023, 05/01/2023
I'd have:
Robert Smith, Sales, 01/01/2023
Robert Smith, Sales, 02/01/2023
Robert Smith, Sales, 03/01/2023
Robert Smith, Sales, 04/01/2023
Robert Smith, Sales, 05/01/2023
Does anybody know the simplest way of doing this in Excel? Or if it's even possible without loads of manual work?
The reason I want it on separate rows is to combine it with another data set that's already in that format, and put into a Pivot (and that other data set is already listed with each day split out, unlike this one).
The same employee could be represented more than once in the data (if, for example, they've taken more than one chunk of time off through the year), if that makes a difference.
Thanks for any pointers anyone can provide.
Assuming they aren't already in separate columns, text to columns will split out the dates, (so you have four cells separated by the commas, with 05/01/2023 in cell A4)
then insert X number of lines (in this case 4) between person X and person Y on leave event N (so you have five total lines, with column C1 having the value 01/01/2023),
then paste the end date from the cell in the third column on the fifth line (so in cell C5, before the next person),
then do a formula in C2 "=C1+" and drag that down or double click the square in the bottom right to fill series between C2 and C4 (filling the three previously empty cells with sequential dates between cells C1 and C5, which you already have),
then select call range A1:B1 and drag that down (or right click the little square when both are selected) to copy the Robert Smith | Sales values from these cells down to match the dates
Still a bit manual but pretty easy & excel does most of the heavy lifting