Array that combines multiple rows and multiple columns

LSarge

New Member
Joined
Mar 11, 2016
Messages
5
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:

Staff name

<tbody>
</tbody>
123456789
ARBUCKLE; Johnny
07/03/2016

<tbody>
</tbody>
8/03/2016

<tbody>
</tbody>
10/03/201612/03/2016
ARBUCKLE; Johnny
08/03/2016

<tbody>
</tbody>
16/03/2016

<tbody>
</tbody>
18/03/2016

<tbody>
</tbody>

<tbody>
</tbody>
20/03/2016

<tbody>
</tbody>

<tbody>
</tbody>
21/03/2016

<tbody>
</tbody>
22/03/2016

<tbody>
</tbody>
24/03/2016

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>


I'd like to end up with

Staff name123456
ARBUCKLE; Johnny8/03/201610/03/2016
12/03/2016

<tbody>
</tbody>
16/03/201618/03/201620/03/2016
21/03/2016

<tbody>
</tbody>

<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!!)
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Hi,

Assuming that your data is as below, where the entries for a given name will always be in consecutive rows, and with your choice of start and end dates in B9 and B10 respectively



ABCDEFGHIJKLM
1Name12345678924/03/201624/03/201624/03/2016
2Dan07/03/2016 08/03/201610/03/201612/03/2016







3Dan08/03/201616/03/201618/03/201620/03/201621/03/201622/03/201624/03/201624/03/201624/03/2016


4Mike28/02/201602/03/201603/03/201605/03/201608/03/201610/03/201616/03/2016




5Mike08/03/201610/03/201620/03/2016








6Mike16/03/201619/03/201620/03/201621/03/201623/03/201625/03/201626/03/201628/03/201629/03/2016


7Tim21/03/201630/03/2016









8












9Start08/03/2016










10End21/03/2016










11












12NameFirst RowLast RowCount123456789
13Dan12708/03/201610/03/201612/03/201616/03/201618/03/201620/03/201621/03/2016

14Mike35608/03/201610/03/201616/03/201619/03/201620/03/201621/03/2016


15Tim66121/03/2016








<tbody>
</tbody>

Formulas:

B13:

=MATCH(A13,$A$2:$A$7,0)

Copy down to B15

C13, array formula**:

=MATCH(1,0/($A$2:$A$7=A13))

Copy down to C15

D13, array formula**:

=SUM(IF(FREQUENCY(IF(INDEX($B$2:$B$7,$B13):INDEX($J$2:$J$7,$C13)>=$B$9,IF(INDEX($B$2:$B$7,$B13):INDEX($J$2:$J$7,$C13)<=$B$10,INDEX($B$2:$B$7,$B13):INDEX($J$2:$J$7,$C13))),INDEX($B$2:$B$7,$B13):INDEX($J$2:$J$7,$C13)),1))

Copy down to D15

E13, array formula**:

=IF(COLUMNS($A:A)>$D13,"",MIN(IF(INDEX($B$2:$B$7,$B13):INDEX($J$2:$J$7,$C13)>=$B$9,IF(INDEX($B$2:$B$7,$B13):INDEX($J$2:$J$7,$C13)<=$B$10,IF(COUNTIF($D13:D13,INDEX($B$2:$B$7,$B13):INDEX($J$2:$J$7,$C13))=0,INDEX($B$2:$B$7,$B13):INDEX($J$2:$J$7,$C13))))))

Copy down and across to M15.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
 

LSarge

New Member
Joined
Mar 11, 2016
Messages
5
Thanks XOR, I'll put it into my sheet and see what I can come up with. I think I said too much in my original post about the start and end dates, I've already created what I need for that, it's more the reducing the multiple rows into one which I can see you have done successfully, so I should be able to use your Excel.

On the off chance, is there a way to do this without requiring the "key" column sorted? If we disregard the TO/FROM dates and just take it as read that I have the data sets, I just need to put them all into one row, but not have a specifically ordered list to work from.

I'll work with mine in the background but if you have an answer already then please feel free to post!

Thanks again for your help, very much appreciated.

All the best,

Luke
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
On the off chance, is there a way to do this without requiring the "key" column sorted?

Hi Luke,

Potentially, but I'm not sure what this "key" column to which you refer is? I don't seem to have given such a field in my example.

Can you clarify?

Cheers
 

LSarge

New Member
Joined
Mar 11, 2016
Messages
5

ADVERTISEMENT

Potentially, but I'm not sure what this "key" column to which you refer is? I don't seem to have given such a field in my example.

Cheers


Apologies, by "key" I just meant the staff name column. Therefore, could we have, for example - here's how the data source looks now:

Dan12/06/2016
15/06/201616/06/2016
Mike15/03/201616/03/201617/03/201618/03/2016
Dan15/05/2016
Tim14/04/201615/04/201616/04/201617/04/201618/04/201619/04/201620/04/201621/04/2016
Mike15/05/201616/05/201617/05/201618/05/201619/05/201620/05/2016
Dan09/09/201610/09/201611/09/201612/09/201613/09/201614/09/201615/09/2016
Tim01/09/2016

<colgroup><col><col span="8"></colgroup><tbody>
</tbody>

What I would like it to result in is:
Dan
12/06/2016
15/06/201616/06/201615/05/201609/09/201610/09/201611/09/201612/09/201613/09/201614/09/201615/09/2016
Mike
15/03/2016
16/03/201617/03/201618/03/201615/05/201616/05/201617/05/201618/05/201619/05/201620/05/2016
Tim14/04/2016
15/04/201616/04/201617/04/201618/04/201619/04/201620/04/201621/04/201601/09/2016

<tbody>
</tbody>

The results will be held on a separate page from the actual data source.

Does that make sense?

Thanks again XOR, much appreciated.

Luke
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Ok, I see what you mean now. Thanks.

But can you just clarify whether a given date can occur more than once for a given person? I was led to believe from your very first post that this was a possibility, though in your latest example each date associated with a given person is unique.

Not having to deal with potential duplicates would certainly make the formula-work a lot easier.

Regards
 
Last edited:

LSarge

New Member
Joined
Mar 11, 2016
Messages
5

ADVERTISEMENT

There shouldn't be any duplication as a person can't effectively be on holiday twice on the same day (in the real world) however obviously the person keying in the data could make a mistake, but I think I could parse that prior to this so that the array shouldn't need to deal with it.

Therefore, the dates for each person will be unique!

Cheers
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Ah. Much easier!

Assuming the first (source) table is in Sheet1!A1:I7 (with names in A1:A7) and that you put Dan, Mike and Tim in Sheet2!A1:A3, then first enter this formula, used to count the number of dates associated with Dan, in Sheet2!B1:

=SUMPRODUCT((Sheet1!$A$1:$A$7=Sheet2!$A1)*(Sheet1!$B$1:$I$7<>""))

Copy down to B3.

Then this formula in Sheet2!C1:

=IF(COLUMNS($A:A)>$B1,"",AGGREGATE(15,6,Sheet1!$B$1:$I$7/((Sheet1!$A$1:$A$7=Sheet2!$A1)*(Sheet1!$B$1:$I$7<>"")),COLUMNS($A:A)))

Copy across and down as required.

Cheers
 

LSarge

New Member
Joined
Mar 11, 2016
Messages
5
Ah. Much easier!

Assuming the first (source) table is in Sheet1!A1:I7 (with names in A1:A7) and that you put Dan, Mike and Tim in Sheet2!A1:A3, then first enter this formula, used to count the number of dates associated with Dan, in Sheet2!B1:

=SUMPRODUCT((Sheet1!$A$1:$A$7=Sheet2!$A1)*(Sheet1!$B$1:$I$7<>""))

Copy down to B3.

Then this formula in Sheet2!C1:

=IF(COLUMNS($A:A)>$B1,"",AGGREGATE(15,6,Sheet1!$B$1:$I$7/((Sheet1!$A$1:$A$7=Sheet2!$A1)*(Sheet1!$B$1:$I$7<>"")),COLUMNS($A:A)))

Copy across and down as required.

Cheers

Hi XOR,

Sincere apologies for the delay in coming back, I've only just had a chance to look at this now.

You sir, are a genius! Worked perfectly and not a curly bracket in sight, I'm deeply impressed. I've not used the aggregate function before so this is new to me but does the job exactly as planned. I'm getting a few #NUM errors coming up in cells where there is no data but I'm sure that's just an issue with me rewriting the formula to fit my sheet, I'll have a play with it. But I'm getting results where I expect them so again, my deepest thanks for your time.

All the best,

Luke
 

Watch MrExcel Video

Forum statistics

Threads
1,122,795
Messages
5,598,083
Members
414,210
Latest member
Janisogor

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top