• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count Unique Numbers in a List

W

William Hulme

New Member
Hi everybody,

I'm constructing a sales related spreadsheet and need to record the number of unique numbers in a list.

To assist with any understanding of my query, the list consists of order numbers which are unique to customers.

The spreadsheet will be used to monitor fulfilment failures when an item could not be sent.

So all the order numbers will be in column A, column B is a product code, column C is the item description that could not be sent and column D the quantity.

Order numbers are 7 digits long.

So, if i have a list of 10 items that could not be sent to customers and 1 customer ordered 2 items then the count would be 9 because they will feature twice in the list and as such there are 9 unique order numbers.

I've played around but cannot seem to work it out.

I've attached the spreadsheet which opens on a tab Wk1D1 and its the cell next to the Orders Affected title that I am looking to populate.

Does anybody have any suggestions?

TY

Will
 

Attachments

  • LTY Tracker testv1.xlsx
    378 KB · Views: 13
Hi Chihiro!

Thank you for taking an interest in my question.

Having zero exposure to array formulae I've opted to try your second suggestion
which works perfectly.

I've not used sum product so will definitely need to get familiar with it so I can work out how your formula works.

Thanks again for your assistance. It's very much appreciated.

Kind regards

Will
 
Chihiro,

I do have a quick question about the formula if you can possibly assist again.

The data I put on the spreadsheet running from A6:A25 is just dummy data that I was playing with to try to get the outcome you provided.

In reality, the number of orders to be assessed by the formula will fluctuate daily.

With this in mind please can you tell me if there is a way to get the formula to allow for differing sized lists?

Kind regards

Will
 
Hello again Bosco!

That works perfectly and accommodates increases in size. Thank you once again for your assistance! You can't be far off Excel Ninja status!

Also Chihiro - thank you for your input. There's so many sheets because to reduce the likelihood of errors it will be used for people to copy and paste data similar to that I included. For my part, the more things I can get to run from that the better, hence my second question.

To you chaps it probably looks quite basic and cumbersome and there's probably more simple and efficient ways of tackling the task if you know how but that said I can understand how it works (apart from the latest formula you both assisted with) but with practice I'll improve!

Thanks again!

Kind regards

Will
 
Hi Haseeb,

Thank you for your solution.

It works perfectly too... I'm spoilt for choice now!

As with the sumproduct function in the last suggestion I'm not familiar with sign or frequency functions in yours so will have to learn more about these and their uses.

It's funny really, I can hold my own in work with Excel but get challenged by some of the formulae needed to perform special tasks but you guys make me feel like a complete novice. With that in mind If, do you have any thoughts as to if there is any benefit using your suggestion over the previous one and vice versa?

Kind regards and thanks again!

Will
 
You must log in or register to reply here.
Back
Top