Sort two ranges without blank rows

Are you using Microsoft 365 (formerly called Office 365)? It matters, because Microsoft recently added a nifty SORT function. Excel 2019 doesn't have it now, and won't be getting It in the future.

No, sorry. only 2019 Excel Pro
I used formulas sorted on 'Race' but want to modify formulas enclosed to also include 'Date'.

Without the SORT function, you can use variations on this formula. The variation consists of changing the column referent in the INDEX at the beginning of the formula. If you have the dynamic arrays feature (Microsoft 365), it is a regular formula. If you have an older version of Excel, it is an array-entered formula. Note that the AGGREGATE function needs Excel 2010 or later.

=IFERROR(INDEX(D$14:D$100,MATCH(AGGREGATE(15,6,($D$14:$D$100+$F$14:$F$100/10+ROW($D$14:$D$100)/10000)/($D$14:$D$100<>0),ROWS(T$14:T14)),($D$14:$D$100+$F$14:$F$100/10+ROW($D$14:$D$100)/10000),0)),"")

Select allOpen in new window

COMBINE-AND-SORT.xlsx

If you are willing to use two auxiliary columns, you can make the formula simpler and much faster.

I added formulas to columns L and S in worksheet AuxiliaryColumns in the attached workbook.
COMBINE-AND-SORT.xlsx

Thanks I'll look at it in a little while as I have an Excel program running regression

Thanks it seems okay but not in alignment with the other two ranges
I have modified the other two to start in row 3
Can you please amend to start your formulas in row 3
Cheers

Formulas moved so they are in row 3 and then copied down.
COMBINE-AND-SORT.xlsx

Didn't show in row 3 but rather row 16 as before.
I've attached the amended sheet can you please move from row 5 to row 3
ThanksCOMBINE-AND-SORT (2).xlsx

I'm having trouble copying to my workbook. I pasted the range incl. col. L in the same location but doesn't work.
I prefer the formulas where I can copy down as many rows as I need. At the moment it is max 20,000 rows.

The formulas are designed to be copied down. Once you get them tuned in for the first row, just copy them down for the rest of the rows.

If you are having trouble installing the formulas in your real workbook, please post that workbook (or email it to me). You will definitely need to change the formulas so they handle 20000 rows of data, but that is easily done.

I've just woken and with a clear head starting to thinking like a man.
I am in the process of opening a new sheet in my workbook and adding the sheet you provided and will copy the other two ranges and with a bit of luck all should be fine. I should know in a few minutes. Thanks for your patience to date much appreciated.

Works like a charm byundt, thanks for your perseverance. Now I hear the kitchen calling me to cook breakfast.
Cheers
Ian

oops I've noticed the sort on 'race' is not quite right
see below
 

I used a "tie-breaker" technique to determine the sort order. To do it, I added the date, Race/10 and row #/10000. I also assumed that the date column contained only dates (no times). The idea is that the values should be sorted by date. Within the same date, the values should be sorted by Race. Within the same date and Race, the values should be sorted by row number.

Each value in column L needs to be unique. Furthermore, the full range of possible race numbers divided by 10 should never exceed 1 (the difference in dates between one day and the next). Likewise, the maximum row number divided by 10,000 should never exceed the 0.1 minimum difference between Race numbers.

When I built this formula, I was assuming that Race/10 would never exceed 1 and that row #/10000 would never exceed 0.1. For sure, the second assumption is not true, because you have 20,000 rows.

Please check the possible race numbers. I want to know if any of them are 10 or more. I also want to know if there are ever two or more digits after the decimal point. It is probably easiest to make this check by applying an AutoFilter to that column and looking in the AutoFilter dropdown.

If my Race number tie-breaker is still OK, the formulas for L3 and S3 could be changed to:

=IF(D3="","",D3+F3/10+ROW()/1000000)
 
=IFERROR(AGGREGATE(15,6,L$3:L$20000,ROWS(T$3:T3)),"")

Select allOpen in new window

Sorry I didn't check properly. I noticed that the right hand range is not included.
The idea was to incorporate both left and right ranges as explained on original sheet I sent
"using column ranges  D:K   AND   AC:AJ "  
Sorry to be a pain.

I saw the blanks in columns AC:AJ and assumed they were for results, not inputs. My bad.

Unfortunately, the type of formula I am using doesn't cater to merging values from two tables and sorting the results. I can do it with Power Query, but not with formulas.

Would you rather put the data from columns AC:AJ in columns D:K or deal with Power Query?

I'd rather put the AC:AJ data into range D:K if that's possible but how to go about it ?
As I have closed the question perhaps I can open a new one re combining data what do you think ? 

Attached workbook uses Power Query to combine and sort both tables using Sheet1 and worksheet Power Query. No formulas are needed.
COMBINE-AND-SORT--2-.xlsx 

It's 2 AM for me. I'll revert to your question in the morning.

Cheers sleep well

Power query looks interesting. I had better get to grips with it. I will be running it daily to a much lower number of rows around 1000 rows or less. I await you response in the next day or so.

I've sorted the 'Race'(time) format by converting text to hr:mn so should be ok now.
Except still not sorting properly on date(time) element. I am quite happy going the Power Query route.

If you go the formula route, I need to see a workbook that doesn't sort correctly.

If you are going the Power Query route, all you need to do when you get new data is to add the data to worksheet Auxiliary Columns, then refresh the Power Query tables on Sheet1 and Power Query worksheets.

I'm sold on the Power Query route

Should I start a new question on the Power Query ? 

Unless the Power Query discussion gets a lot more complicated, let's continue it here.

Okay, I have attached a new sheet with final cell location. Can you please use this for the Power Query.
Also please remember for some reason the time order (race) is still not sorting correctly.
Sort-PowerQuery.xlsx

Sort-PowerQuery.xlsx

When I built the Power Query, Excel thought your data ended in row 220.

I think the workflow would be:

1. Paste data in worksheet Data, either in columns D:K or AC:AJ
2. On worksheet Sheet1, rightclick a cell in Table1 and choose Refresh
3. On worksheet Power Query, rightclick a cell in Table3_2 and choose Refresh

1. Paste data in either range ? so I can paste both DFRM data and UKHR in the one range ?
2. No Sheet 1 do you mean sheet 2 ?
3. I cannot see table3_2 in Power Query tab

I think it is beyond me at this stage. Its not as easy as I thought.
I will stick to the one set of formulas which you kindly supplied and to which I closed the question.
You've been very patient to which I'm thankful.
Cheers

My goof. I meant Sheet2.

The name of the table on Sheet2 is Table1_2. The name of the table on sheet Power Query is Table3_2. It really doesn't matter what they are named though, because there is only one table on each of those worksheets.

Your raw data needs to go on worksheet Data. The Power Query tool accepts data in either (or both) columns D:K and AC:AJ.

Once you add new data, you need to refresh the query. You do this by rightclicking one cell in the table and choosing Refresh.

Thanks, I'm sure it will become clear once I get past the 'struggle' stage.
Thanks again for all your help.

After some fiddling I managed to integrate both ranges using your formula method
with some small modifications.   see attached.
However it sorts on Race(time) only and not a combination of Date and Race(time)
If it can't be done I'll just do a manual sort on both sort orders Integrate=Cell-Ranges.xlsx

I added a worksheet "Sort by date then race" that I think does what you want.

To accomplish it, I tweaked the formula in column AK used to build a tie-breaker:

=IF(AC11="","",AC11+AE11+ROW()/10000000)

Select allOpen in new window

I then put a formula to return the index number of the desired row using those values in column S:

=IFERROR(MATCH(AGGREGATE(15,6,AK$11:AK$40000,ROWS(T$11:T11)),$AK$11:$AK$40000,0),"")

Select allOpen in new window

This then let me put formulas like the following to return the betting information in the desired date & race order:

=IFERROR(INDEX(AC$11:AC$40000,$S11),"")

Select allOpen in new window

Integrate-Cell-Ranges.xlsx

That's fantastic. It works like a charm. A big thank you.
I have one more formula to complete the project and have attached with explanations.
I can open a new question if you wish. But here's a chance to have a look.
CheersMatch Formula.xlsx

The formula will be easy once I understand it.

I believe the desired formula uses the name of the horse to find a match in column AS, and then returns the value in column AT. No attempt is made to match the date.

I can do a lookup formula or an average formula for the value. For Passion, there are two different values in column AT. See rows 57 and 4058. I concatenated both values in the attached workbook.
Match-Formula.xlsx

A horse may have several starts and the Rating figure URP will probably change each time.
The date is the key. The URP is a ranking number for the day. Row 57 and row 4058 are different dates
and would have a different URP and if same is pure coincidence.
As an example a snooker player may finish 5th in a tournament on Jan 3rd and 14th in another on Feb 16th.
It would be pure coincidence if he finished 5th in both tournaments.
Where a horse is mentioned twice on same day it means the same race and URP will be the same. The reason for it being mentioned twice is there are two databases involved which are left and right on the previous sheet I sent and I had to place both in the same area. One being rows 11-20,000 the other 20,002 to 40,000.
The URP is only available for the top database hence the reason why the numbers only go down to row 20,000 max.
A layman's formula would look like this  Lookup date and horse and return adjacent RatingsPosition.  
Hence the date and horse would need matching.

I suppose you could join Date and Horse to make a helper and do same for the AR and AS columns and use a lookup formula. That is another possibility as I crawl back between the sheets.

I can use AVERAGEIFS to average the values for rows where the horse name and date match.
Match-Formula.xlsx

I need to populate all the cells in column Y with the exact number assigned to the horse on a certain day as shown in columns AR & AS

I don't know why I was using dates in column AI. The formula should have used AR.
Match-Formula.xlsx

That does the trick nicely. We got there in the end despite my prolonging the project with extra requirements.
Thank you for your generous efforts much appreciated.
Ian