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

RaffyM

Hi @KPH

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

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?

RaffyM

NCM Part Number is reference to helper column of a Part Number column, because the original Part Number column is alpha numeric value. We used this formula =[Part Number]@row + "" to eliminate any 0 or '.

Rejection Date is date formatted column. It is used though in a conditional formatting, would it affect?

RaffyM

I reviewed the Rejection Date column and some dates were wrongly entered. After fixing, the formula works.

Thank you again @KPH for helping out, appreciate your time.

Until next time. 😉😊

KPH

That's great! Well done.

RaffyM

Hello @KPH sorry for bothering you again and for always asking you here. I hope you don't mind it at all.

After fixing the formula for the distinct part numbers for date range, now I'd like to count how many times that distinct part numbers entered within a year. I tried 3 different formulas, but all not responding correctly.

Currently, the formula without date criteria is working perfectly.

When I add the date in the formula (same as what you've suggested when using distinct/collect formula, and it's working), the result is blank.

I changed the date formula a bit (similar to what I am using in other sheets, and it's working), the result is also blank.

I even expanded the formula for the date criteria, but this one is giving me incorrect argument set.

When I counted manually, 86082593 was entered 5 times. Could you take a look and tell me what is wrong?

Appreciate all your help, thank you very much in advance. ☺️

KPH

Firstly, I wanted to explain the difference between the two different date criteria you tried. The one I suggested with IF(ERROR(YEAR(@cell),0) and the one you have somewhere else with IF(ERROR(YEAR(@cell),"")

These are doing the same thing. They are checking that a year can be returned from the cell in the range column and if it cannot, they are returning something (as returning nothing will prevent the rest of the formula from working). The only difference is one returns a 0 and the other returns "".

Secondly, the incorrect argument set is due to the ,"" between the orange and green parenthesis. Not sure what the intention was, but it doesn't belong there.

Thirdly, your actual question. In the sheet that you are referencing do you have one column with the part numbers (that you call Part Number) and another with the dates, in a date formatted column (that you call Rejected Date)? It isn't the Rejection Date we used previously is it?

RaffyM

Hi @KPH

Yes, I have two referenced columns. First is Part Number (text/number column) and second is Date Rejected (date formatted column). Actually, I made two different names for the date column, the other one was Rejection Date. I deleted the latter and still not working.

The error probably, there were two names for single referenced column. After deleting the other one, it works now. Thanks for the good catch.

Have a great afternoon ahead. 😊

KPH

👍️