return the value of unique part numbers with highest counts (top 10 or 20 most entered)

RaffyM

Good afternoon community people.

Need your assistance. I have a worksheet with thousands of rows of different and/or duplicated part numbers and and my team keep on adding rows daily. I'd like to get or return the unique part number with the highest counts the number of times (duplicates) each unique part number is entered. I added helper columns, first for number of count each unique number is entered using countif and second for highest count using rankeq. I was able to get the formula working for both helper columns. However, my difficulties are:

1. example if the highest count of the unique part number 11221133 is 20 (meaning there is 20 rows with this unique part number), and it is rank 1 using rankeq formula, the next highest count of unique part number 22331144 is 21 but my expectation is 2. what formula should I use to get the correct results?
2. my main goal is to get the top 10 or 20 unique part numbers (ex. 11221133) with highest counts. I don't know if having helper columns would help or should I say how to use these helper columns to achieve my goal.

Appreciate if someone could enlighten me on this. Thank you in advance and have a great evening.

RaffyM

Some part numbers are alphanumeric, while other starts with zero (0). When it starts with zero it has ', so I removed both ' and zero. Unfortunately still invalid data type.

Thanks

KPH

Hi @RaffyM

Try

=INDEX(DISTINCT(COLLECT({NCM Part Number}, {Rejection Date}, IFERROR(YEAR(@cell), 0) = 2023)), [Number Helper]@row)

You need to have the part number at the start of your collect as that is the value you ultimately want to return. This is followed by the range and criteria. For the YEAR part, this needs to be wrapped in an IFERROR function otherwise it returns INVALID DATA when it tries to evaluate blank rows.

KPH

That looks good. I have mocked it up to test it and can't see the difference between yours and mine

Are the cross sheet references set up correctly?

NCM Part Number is just the column with part numbers in and Rejection Date is just the column with the dates in, and that column is date formatted?

KPH

Hi @RaffyM

Would this work for you?

Where Sheet A Part Col references the part number column of your sheet with thousands of rows.

You'd just need to extend the Rank column from 6 to 20 rows and drag that formula down.

The formula for copy/paste purposes is:

=INDEX(DISTINCT({Sheet A Part Col}), Rank@row)

It doesn't use your helper columns.

RaffyM

Hello @KPH, thank you for response. I don't have the rank column. Here is the screenshot of my worksheet.

From this worksheet, 86085576 was entered 4 times, could be more while 86069906 was entered twice or more. Same with other part numbers, they could be re-entered again. So, I'd like to have a formula to determine the part number with most entered, in the above worksheet example, it will return 86085576 as top 1, 86069906 as top 2, and so on. Hope my explanation is clear.

Thank you

RaffyM

Hello @KPH, in addition to my earlier response, I added a Rank column and tried the formula you gave but gives me #CIRCULAR REFERENCE result.

=INDEX(DISTINCT([Part Number]:[Part Number]), Rank@row)

Thanks

KPH

Hi @RaffyM

My idea was to create your ranked list in a separate place from the massive list you already have. To do that...

1. Leave the worksheet you have as it is.
2. Create a new sheet.
3. Name column 1 "Rank" and enter 1 in row 1, 2, in row 2, 3 in row 3, etc. until you get to 20 (my example stopped at 6).
4. Name column 2 "Part".
5. Enter the formula into cell 1

=INDEX(DISTINCT({Sheet A Part Col}), Rank@row)

Where Sheet A Part Col references the "Part Number" column of your sheet you shared a preview of, above.

6. Make this a column formula by right clicking and selecting "Convert to column formula"

Your NEW sheet should look a little like this (you will not have the color coding once you convert to column formula and that is fine - I skipped that step as I only have 6 rows and it is easier to read the formula like this)

If you don't want the ranked list to be separate and want the ranking to appear on each row on the original list, I would still do the above as the first step. This identifies the rankings.

Your next step would be to add a VLOOKUP in the original sheet to look up the rank number for each part in this new list and pull it into the original.

RaffyM

Hello @KPH

I made test sheet so I can test the formula, this time it gives #INVALID DATA TYPE

Thanks

KPH

Oh no! That is hard to troubleshoot without accessing your sheets.

Can you check the "Part Number" column in both sheets is Text/Number.

1. Right click on the heading
2. Click "Edit Column Properties"
3. It should look like this:

RaffyM

Yes, they are both Text/Number columns

KPH

OK. I managed to break mine by putting data in the Part Number column of the original sheet with special characters in it - like ) or /

If your data has any of those it should be easy to spot as the cell will be left, rather than right, justified.

I have used yellow to highlight issues. If you have anything like this, alter the data, and you should be good.

RaffyM

Some part numbers are alphanumeric, while other starts with zero (0). When it starts with zero it has ', so I removed both ' and zero. Unfortunately still invalid data type.

Thanks

KPH

The mixture of alphanumeric and numeric part numbers will be causing the issue.

I suggest adding a hidden column to your original sheet (I call it "Part Number (Text)"). Use a column formula to populate that new column with the Part Number as Text not Numbers and then refer to the new column in your second sheet.

So sheet 1 (the big sheet with all your rows in) will look like this:

The formula in the new column is

=[Part Number]@row + ""

This is essentially copying over the part number but putting a single quote mark/apostrophe at the start. This will not be visible and will not interfere with the other formulas that refer to the cell. It just tricks the sheet into thinking of the number as text - see how they are all left aligned in the new column.

Then change the reference in the ranking sheet to use the new column. And you get something like this:

RaffyM

Hello @KPH, thank you for your effort, much appreciate it.

I did what you suggested and no more errors. In fact, before I saw your response I tried similar in my test sheet and it returned all the part numbers.

However, what I noticed was, the formula will return only the first (top 10 or 20 or whatever) distinct part numbers that were entered first. In the screenshot below, 86044697 is Rank 1 because it was the first part number that was entered in the working sheet, followed by 86081841, so on and so forth. Under the Expected PN column, based on its counts, 86060105 should be the rank 1, followed by 86044697 as the second highest count, and so on.

Hopefully I explained it clearly of what I'm trying to achieve.

Thank you

RaffyM

hello @KPH hope you're doing great.

I would need your assistance again related to this topic. the formula that you thought me before was working great, thank you again. But this time, using the same formula I'd like to add criteria of a date range. My goal is to separate the part numbers entered in 2023 and this current year.

Existing formula is: =INDEX(DISTINCT({NCM Part Number}), [Number Helper]@row)

I searched previous similar discussions to get some ideas and the suggestion was to add COLLECT function. The formula I re-created with COLLECT function is

=INDEX(DISTINCT(COLLECT({Rejection Date}, YEAR(@cell) = 2023, {NCM Part Number})), [Number Helper]@row)

But it gives me #invalid operation error.

Hoping you could help me sort this out again. Thank you very much in advance.

KPH

Hi @RaffyM

Try

=INDEX(DISTINCT(COLLECT({NCM Part Number}, {Rejection Date}, IFERROR(YEAR(@cell), 0) = 2023)), [Number Helper]@row)

You need to have the part number at the start of your collect as that is the value you ultimately want to return. This is followed by the range and criteria. For the YEAR part, this needs to be wrapped in an IFERROR function otherwise it returns INVALID DATA when it tries to evaluate blank rows.

RaffyM

Hello @KPH

Thank you for responding to my concern. I tried the formula but it gives incorrect argument set.

In one of my formula I tried before reaching out was the part number is at the beginning but it was also an error. I played around the formula and probably the one I shared to you was the last one I tried.

KPH

Sounds like there is a missing comma or parentheses. Can you take a screenshot of the formula pasted into the sheet?