# Rank the Date Based on Two Columns

1. ## Rank the Date Based on Two Columns

I have a worksheet that is finding a soccer team's home and away fixture dates in ascending order and displaying them in a separate table. The problem is I can only do it based on home or away matches separately, when ideally I want to find a way of combining a team's home and away matches by ranking based on their date.

Remember, as there is a column for the home team and another for the away side, it means that any kind of formula needs to take this into consideration. Sometimes the team will appear as the home side and other as the away team. I've been using an AGGREGATE conditional statement for the home and away teams separately:
Formula:
`Please Login or Register  to view this content.`
Formula:
`Please Login or Register  to view this content.`

However, an #N/A error value is produced when I use both columns together:
Formula:
`Please Login or Register  to view this content.`

I've attached an example that shows this.

2. ## Re: Rank the Date Based on Two Columns

Have not looked at your file, but is you want to rank based on either of the 2 date (it cannot be for both, right?), maybe create a helper that extracts whichever date is there, then rank based on the helper?

3. ## Re: Rank the Date Based on Two Columns

I'm aware of other methods that can be used to achieve this, but I'm trying to find a formula that can rank the date based on a team appearing in the home or away column. I just want a formula that can do this without the need for any additional helper columns.

4. ## Re: Rank the Date Based on Two Columns

Have you manually mocked up a few rows in the workbook? If not, please do so and reattach.

5. ## Re: Rank the Date Based on Two Columns

Yes, my attached workbook makes it clear what I'm trying to do.

6. ## Re: Rank the Date Based on Two Columns

Well, no, it doesn't, otherwise I wouldn't have asked, but if you say so, I'll have another look ...

7. ## Re: Rank the Date Based on Two Columns

This doesn't show me what you want:

Excel 2016 (Windows) 32 bit
N
O
P
Q
5
Home Fixture Date
Away Fixture Date
Overall Fixture Date
6
1
09/08/2019
17/08/2019
#N/A
7
2
24/08/2019
31/08/2019
#N/A
8
3
14/09/2019
22/09/2019
#N/A
9
4
05/10/2019
28/09/2019
#N/A
10
5
27/10/2019
20/10/2019
#N/A
11
6
09/11/2019
02/11/2019
#N/A
12
7
30/11/2019
23/11/2019
#N/A
13
8
04/12/2019
07/12/2019
#N/A
14
9
14/12/2019
21/12/2019
#N/A
15
10
28/12/2019
26/12/2019
#N/A
16
11
01/01/2020
11/01/2020
#N/A
17
12
18/01/2020
21/01/2020
#N/A
18
13
01/02/2020
08/02/2020
#N/A
19
14
22/02/2020
29/02/2020
#N/A
20
15
07/03/2020
14/03/2020
#N/A
21
16
21/03/2020
04/04/2020
#N/A
22
17
11/04/2020
18/04/2020
#N/A
23
18
25/04/2020
02/05/2020
#N/A
24
19
09/05/2020
17/05/2020
#N/A
 Sheet: Sheet1

Cells containing formulae producing errors are NOT manually mocked up results.

8. ## Re: Rank the Date Based on Two Columns

Sorry about that. I've reattached the workbook to show that column with the values that should be displayed. You'll see that I've used the SMALL function to extract the home and away dates in ascending order. However, I just want to find a formula that does not depend on the two columns beside it.

9. ## Re: Rank the Date Based on Two Columns

So to be clear, you are wanting to filter to all of Liverpool's fixtures in the 2019-20 season - is that it?

10. ## Re: Rank the Date Based on Two Columns

That is correct. I've successfully done it for the home and away games separately, but the challenge is obviously made harder when you combine the two.

11. ## Re: Rank the Date Based on Two Columns

Have a look at the attached. It is done with Power Query. If it's what you want, I can talk you through how to do it. The resulting table can be set to refresh automatically if you wish, or a bit of code can be added to refresh it when the filter data changes. I could also show you how to create a parameter query that would obviate the filter box altogether, if you are interested.

12. ## Re: Rank the Date Based on Two Columns

Thanks for the suggested solution, but I really want to find a formula-based solution.

13. ## Re: Rank the Date Based on Two Columns

OK, well that's your loss. Feel free to get back to me if you change your mind. Power Query is a Pandora's Box: you really should not pass it over.

Out of interest, why are you so intent on it being a formula solution? You may have a misconception about my suggestion.

14. ## Re: Rank the Date Based on Two Columns

``Please Login or Register  to view this content.``
With this formula in column G you get a number .
Use filter (everything but blanks) to show only your selected team.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1