Sorting dates across columns into order

[dpbarry]

Hi Folks.

At our local lifeboat station, I have a spreadsheet that is used to record dates across columns to show when crew have completed a specific revalidation task in order to show they have completed the task within an 18 month timeframe.

As its stands, I have about 30 crew members (rows) and across across 10 columns I have a dates in the format DD/MM/YYYY but they are starting to get out of sequence. They should start from January and work across to December but somehow I've got them out of sync. If push comes to shove, I just manually rearrange them all but was just wonder if there was a way of using a formula to sort them in order.

Kind Regards

Declan

 

[Z51]

Assuming your crew names are in column A, and your dates are in columns B:K, then in column L (e.g. L2 if that's your first row of data) put:

=IFERROR(SMALL($B2:$K2,COLUMN(A1)),"")

Fill that across to column U (10 columns total), then fill down to the last row needed. It will be the dates sorted ascending in each row. You can then select that data and copy > pastespecial on top of B2:K?? and delete the formulas in L2:U??.

 

[dpbarry]

Hi Z51

Thank You. Does exactly what I need and saves me a lot of manual typing.

I'm just trying to work out what the formula is doing. I understand the initial part of the small function providing the array of values but I'm not sure how the Column (A1) bit works to provide the nth smallest value of the date array across the 10 columns.

Can you explain? Is the Column (A1), Columns (B1) etc across to Column(K1) equating to 1, 2 etc across to 10?

Regards

Declan

 

[Z51]

When you drag the formula across columns, the "A1" bit changes to B1, then C1, then D1, etc. The function "COLUMN(A1)" will return 1, since cell A1 is in column 1. "COLUMN(A9920)" would also return 1 for that same reason. "COLUMN(D92)" would return 4, since it's referencing column D, the fourth column.

 

[dpbarry]

When you drag the formula across columns, the "A1" bit changes to B1, then C1, then D1, etc. The function "COLUMN(A1)" will return 1, since cell A1 is in column 1. "COLUMN(A9920)" would also return 1 for that same reason. "COLUMN(D92)" would return 4, since it's referencing column D, the fourth column.

Ahh! Perfect. I understand now.

Once again. Many thanks for your help. Will sort all the columns out tomorrow.

Regards

Declan