We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Sort two ranges without blank rows

Medium Priority
31 Views
Last Modified: 2020-05-18
Hi
I wanted to sort data without missing rows
I have enclosed a sheet with explanations.
Most columns have formulas that need modifying.
Many Thanks
IanCOMBINE-AND-SORT.xlsx
Comment
Watch Question

byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.
Ian Bellretired

Author

Commented:
No, sorry. only 2019 Excel Pro
I used formulas sorted on 'Race' but want to modify formulas enclosed to also include 'Date'.
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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)),"")
COMBINE-AND-SORT.xlsx
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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
Ian Bellretired

Author

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

Author

Commented:
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
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

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

Author

Commented:
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
Thanks COMBINE-AND-SORT (2).xlsx
Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
To get the formula results to move from row 5 to row 3, all I had to do was copy the formula from the formula bar for T5 into the formula bar for T3, hit Enter, then copy that formula down.

Since you don't need to see the values in column S, I adjusted its width to match the columns to its left.

I noticed you were only looking a race results through row 89. Since you had data through row 200, I changed the formulas accordingly. You could make the formulas look past the end of your data if you like. That way, no formula changes are needed if you have a different number of rows of data. If you like that approach, change the 200 to 2000.
COMBINE-AND-SORT--2-.xlsx

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Ian Bellretired

Author

Commented:
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.
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.
Ian Bellretired

Author

Commented:
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.
Ian Bellretired

Author

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

Author

Commented:
oops I've noticed the sort on 'race' is not quite right
see below
 
Date Meeting Race Selection Bet URP Model Betfair No
1/9/19 Worcester 2.1 Invincible Don 2
CHCP 5.444 3
1/9/19 Worcester 2.1 Midnight Gem 2
CHCP 5 2
1/9/19 Brighton 2 Solesmes 5
FHSS 28.97 6
1/9/19 Cork 2.2 Unknown Pleasures 2
FSSL 5 2
1/9/19 Cork 2.2 Lemista 1
FSSL 5.825 3
1/9/19 Worcester 2.4 Go Another One 1
CSTK 1.371 1
1/9/19 Worcester 2.4 Notnow Seamus 2
CSTK 4.5 2
1/9/19 Brighton 2.3 Spanish Angel 3
FSSS 1.937 1
1/9/19 Cork 2.5 Chasing The Dawn 5
FSSL 2.8 1

byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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)),"")

Ian Bellretired

Author

Commented:
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.


byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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?
Ian Bellretired

Author

Commented:
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 ? 
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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 
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

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

Author

Commented:
Cheers sleep well
Ian Bellretired

Author

Commented:
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.
Ian Bellretired

Author

Commented:
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.
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.
Ian Bellretired

Author

Commented:
I'm sold on the Power Query route
Ian Bellretired

Author

Commented:
Should I start a new question on the Power Query ? 
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

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

Author

Commented:
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
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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
Ian Bellretired

Author

Commented:
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
Ian Bellretired

Author

Commented:
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
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.
Ian Bellretired

Author

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

Author

Commented:
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
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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)
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),"")
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),"")
Integrate-Cell-Ranges.xlsx
Ian Bellretired

Author

Commented:
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.
Cheers Match Formula.xlsx

byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

Ian Bellretired

Author

Commented:
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.
Ian Bellretired

Author

Commented:
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.
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

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

Author

Commented:
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
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

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

Author

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.