Good day folks,
I've been reading the site with interest for a number of years, always usually able to find an answer to my needs. However this one is foxing me and I can't see that it's been done before.
I have a data set which involves multiple occurrences of a unique identifier in one column, then multiple columns of data related to those instances. I'd like an array that can create one row of data for the unique identifier from all of the instances. For example:
<tbody>
</tbody>
I'd like to end up with
<tbody>
</tbody>
I'm giving the user the option to put in a date range (a from and to) which the sheet then calculates (based upon a static list) which dates are applicable to the person (it's effectively a holiday calendar that works out when someone is working on or off shift and then only shows the appropriate working day as a holiday).
I've been toying with INDEX, SMALL, COLUMN - like this:
{=IF(ISERROR(INDEX($AJ5:$BK5,SMALL(IF($AJ5:$BK5<>"",COLUMN($AJ5:$BK5)-COLUMN($AJ$5)+1),COLUMNS($AJ$5:AR5)))),"",INDEX($AJ5:$BK5,SMALL(IF($AJ5:$BK5<>"",COLUMN($AJ5:$BK5)-COLUMN($AJ$5)+1),COLUMNS($AJ$5:AR5))))}
This sorts out the dates being worked into a line of columns (removing spaces from a previous set of data that actually works out the working days). What I need to be able to do now is bring it all together so I have a unique line for each person, rather than multiple lines. The issue I'm running into is how to get an array to look at multiple lines and multiple columns of data and then put them all on one line, with the second line of data starting at the next column after the first row of data has been displayed...
Does that make sense? I feel I've waffled somewhat! I can do this in VBA no problem, but it would be great if this could be bundled into an array and reduce the need of having further static lists of data etc.
Many thanks for any help you may be able to provide.
Luke
(P.S. Sorry for the horrible table formatting!!)
I've been reading the site with interest for a number of years, always usually able to find an answer to my needs. However this one is foxing me and I can't see that it's been done before.
I have a data set which involves multiple occurrences of a unique identifier in one column, then multiple columns of data related to those instances. I'd like an array that can create one row of data for the unique identifier from all of the instances. For example:
<tbody> </tbody> | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | |||||||||||
ARBUCKLE; Johnny |
<tbody> </tbody> |
<tbody> </tbody> | 10/03/2016 | 12/03/2016 | ||||||||||||||||
ARBUCKLE; Johnny |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | <tbody> </tbody> |
<tbody>
</tbody>
I'd like to end up with
Staff name | 1 | 2 | 3 | 4 | 5 | 6 | |||||
ARBUCKLE; Johnny | 8/03/2016 | 10/03/2016 |
<tbody> </tbody> | 16/03/2016 | 18/03/2016 | 20/03/2016 |
<tbody> </tbody> |
<tbody>
</tbody>
I'm giving the user the option to put in a date range (a from and to) which the sheet then calculates (based upon a static list) which dates are applicable to the person (it's effectively a holiday calendar that works out when someone is working on or off shift and then only shows the appropriate working day as a holiday).
I've been toying with INDEX, SMALL, COLUMN - like this:
{=IF(ISERROR(INDEX($AJ5:$BK5,SMALL(IF($AJ5:$BK5<>"",COLUMN($AJ5:$BK5)-COLUMN($AJ$5)+1),COLUMNS($AJ$5:AR5)))),"",INDEX($AJ5:$BK5,SMALL(IF($AJ5:$BK5<>"",COLUMN($AJ5:$BK5)-COLUMN($AJ$5)+1),COLUMNS($AJ$5:AR5))))}
This sorts out the dates being worked into a line of columns (removing spaces from a previous set of data that actually works out the working days). What I need to be able to do now is bring it all together so I have a unique line for each person, rather than multiple lines. The issue I'm running into is how to get an array to look at multiple lines and multiple columns of data and then put them all on one line, with the second line of data starting at the next column after the first row of data has been displayed...
Does that make sense? I feel I've waffled somewhat! I can do this in VBA no problem, but it would be great if this could be bundled into an array and reduce the need of having further static lists of data etc.
Many thanks for any help you may be able to provide.
Luke
(P.S. Sorry for the horrible table formatting!!)