[Solved] How can I eliminate duplicates in a list and list in descending order?

[tallnsknny]

Hello again,

I'm close to finish my spreadsheet, but I am stuck on a new problem. I use Excel 2016 so some new functions don't exist for me. I've highlighted in orange on the attached spreadsheet a list of 8 years many of them are duplicates. These 8 numbers are based on referencing cell BH3 and then looking for dates related to that name. I want to eliminate the duplicates in this list and also list them in descending order. I also want to be able to have the results show up in cells BH4, BH5 & BH6. So right now, the file shows the following dates in order: 1994, 1980, 1987, 1994, 1980, 1997, 1997, 1997 I would like to show any year only once and list them in descending order, therefore the results in BH4 would be 1997, BH5 would return 1994 and BH6 would return 1980 everything after that in column BH would be showing up as "--". Is there a series of formulas that can do that? Thank you so much for your help. It's beyond my limited Excel abilities.

Mike

 

[shamimarita]

Hello,
You can achieve this using a combination of Excel formulas to eliminate duplicates, sort the list in descending order, and display the results as desired. Since you're using Excel 2016 and some newer functions like UNIQUE aren't available, here’s an alternative approach:

Extract Unique Values: Use an array formula to extract unique years from your highlighted list (let’s assume the range is A1:A8):
=IFERROR(INDEX($A$1:$A$8, MATCH(0, COUNTIF($BH$3:BH3, $A$1:$A$8), 0)), "--")

Sort in Descending Order: Modify the formula above to account for sorting. First, determine the largest value using:
=MAX(IF(COUNTIF($A$1:$A$8, $A$1:$A$8)=1, $A$1:$A$8))

• Use this as the base to dynamically extract the next largest unique values.

 

[tallnsknny]

Hello Shamima,

Thank you for your input, but this is not what I am after. As a reminder, I use Excel 2016. I am not trying to identify all of the unique numbers in column BH of the Summary - Port tab (they're highlighted in Orange in the attached file in my initial post up above. What I am trying to identify is if I choose a specific Port Producer and place that name in cell BH3 (you'll notice that BH3 has a drop down list) what are the unique years (excluding duplicates) that Port Producer has listed in the Detailed List tab. The example I show in the attached file from my original Post is a Port Producer called Alves de Sousa. So I want the formula to look for all Alves de Sousa listing in the Detailed List in Column C of the detailed list and then identify all of the unique years in Column B of the Detailed list that relate to Alves de Sousa only and place those years in descending order starting in cell BH4 of the Summary - Port tab. Keep in mind that 1. the name in cell BH3 will change when I want to look up a different name's information and 2. I only want unique years for that Port Producer that is listed in cell BH3, so that means if that Port Producer (for example Alves de Sousa) has 3 listings for 1994, I only want to show 1994 once. Ultimately, using Alves de Sousa as my example, the correct results after the formula was complete would be: cell BH4 would show 1997, cell BH5 would show 1994 and cell BH6 would show 1980 and everything after that in column BH would show as "--". I hope that is a little clearer. Thank you.

Mike

 

[shamimarita]

Hello Mike,

Thank you for clarifying! Based on your description, here's how to achieve the desired outcome.

Array Formula for Unique Years in Descending Order:
Since Excel 2016 doesn't have dynamic array functions like UNIQUE or SORT, you'll need to use an array formula:

In cell BH4, enter this formula:
=IFERROR(LARGE(IF('Detailed List'!$C$2:$C$100=$BH$3, 'Detailed List'!$B$2:$B$100/COUNTIFS('Detailed List'!$B$2:$B$100, 'Detailed List'!$B$2:$B$100, 'Detailed List'!$C$2:$C$100, $BH$3)), ROWS($BH$4:BH4)), "--")

• Filters Column C of the Detailed List to match the selected Port Producer in BH3.
• Extracts corresponding years from Column B while eliminating duplicates.
• Sorts the unique years in descending order.

How to Use:

• Press Ctrl + Shift + Enter after typing the formula, as it's an array formula in Excel 2016.
• Drag the formula down to column BH to populate the results. Any cell without a unique year will display --.

Adjustments: Replace 'Detailed List'!$C$2:$C$100 and 'Detailed List'!$B$2:$B$100 with the actual ranges of your data.

 

[tallnsknny]

Hi Shamima,

Thank you again for your help, but it's still not quite correct. If I take your formula as suggested and plug it into the model and after substituting the actual ranges of the model and using control + shift + enter I get the following results:
BH4 = 997, BH5 = 997, BH6 = 990, BH7 = 990 and BH8, BH9, BH10 & BH11 all = 499.25
The correct answers should be for Port Producer Alves de Sousa: BH4 = 1997, BH5 = 1994, BH6 = 1980 and BH7, BH8, BH9, BH10 & BH11 = "--"

So then I noticed a "/" sign before the COUNTIFS command. I assumed that should be a "," instead so I replaced the "/" with a "," That gave me the following results:
BH4, BH5, BH6 & BH7 all = 1997, BH8 & BH 9 both = 1994 and BH10 & BH11 both = 1980

Do you know what the issue is? I've attached a copy of the model with the formula in the relevant cells but kept the "/" sign in so you can see the results. Let me know your thoughts. I've played with your formula but still can't get the correct results of:
The correct answers should be for Port Producer Alves de Sousa: BH4 = 1997, BH5 = 1994, BH6 = 1980 and BH7, BH8, BH9, BH10 & BH11 = "--"

And I apologize in advance for bothering you about this again.

Mike

 

[shamimarita]

Hi Shamima,

Thank you again for your help, but it's still not quite correct. If I take your formula as suggested and plug it into the model and after substituting the actual ranges of the model and using control + shift + enter I get the following results:
BH4 = 997, BH5 = 997, BH6 = 990, BH7 = 990 and BH8, BH9, BH10 & BH11 all = 499.25
The correct answers should be for Port Producer Alves de Sousa: BH4 = 1997, BH5 = 1994, BH6 = 1980 and BH7, BH8, BH9, BH10 & BH11 = "--"

So then I noticed a "/" sign before the COUNTIFS command. I assumed that should be a "," instead so I replaced the "/" with a "," That gave me the following results:
BH4, BH5, BH6 & BH7 all = 1997, BH8 & BH 9 both = 1994 and BH10 & BH11 both = 1980

Do you know what the issue is? I've attached a copy of the model with the formula in the relevant cells but kept the "/" sign in so you can see the results. Let me know your thoughts. I've played with your formula but still can't get the correct results of:
The correct answers should be for Port Producer Alves de Sousa: BH4 = 1997, BH5 = 1994, BH6 = 1980 and BH7, BH8, BH9, BH10 & BH11 = "--"

And I apologize in advance for bothering you about this again.

Mike

Hello Mike,

You can use the following formula:
=IFERROR(LARGE(IF(('Detailed List'!$C$4:$C$3004=BH$3)*(COUNTIF(BH$3:BH3, 'Detailed List'!$B$4:$B$3004)=0), 'Detailed List'!$B$4:$B$3004), 1), "--")