Count unique values but...

[petko]

Hello,

i need your help. In short way - i have excel file with many sheets. One of them keeps customers and their prefixes (like Lion Value = LV; Plast-oxyl = PO; Iron Auto-Cloud = IAC; etc) and primal suffix of six zeros.
Second sheet shows delivery order (where you put material number, quantity, customers purchase order, delivery date,...) and you also put there a delivery number (following next order number for every single customer).
For example: you know that you made 4x orders for customer Lion Value, so next filled number must be 5.
In this sheet is also automatically generated our internal delivery order "NUM COMMANDE" (which consists from customer prefix + suffix zeros + number of next delivery. So we will get for example LV000005.
Another sheet shows history - it means everything what was ordered in history. Example at the bottom.

My question is - is there any formula or mix of functions which will count unique values (our internal delivery orders = NUM COMMANDE) for every customer (based on prefix) and will tell me that next delivery order must be XX.
Example from bottom: it will count that customer with prefix LV has 4 unique orders in our history, so next delivery order number must be 5 for customer LV. If we look for customer PO, we see that we have 2 unique NUM COMMANDEs, so next delivery will be with 3 = next delivery order must have number 3.
NOTE: I dont want to use any filter or pivot table. Maybe only macro, but prefer function/formula.

Material Purchase Order Num Commande Delivery date Quantity
L0676609AB01 2708/2019 LV000001 21.05.2019 30
L0676610AB01 2708/2019 LV000001 21.05.2019 30
L0676611AB01 2708/2019 LV000001 21.05.2019 60
L0676612AB01 2708/2019 LV000001 21.05.2019 75
KY3214A583AD PO4278/PO4479 PO000001 21.05.2019 18
KY3214A583CD PO4278/PO4479 PO000001 21.05.2019 9
KY3214A583ED PO4278/PO4479 PO000001 21.05.2019 27
KY3214A583GD PO4278/PO4479 PO000001 21.05.2019 36
L0676609AB01 111/3 LV000002 22.05.2019 30
L0676610AB01 111/3 LV000002 22.05.2019 30
KY3214A583AD PO4299/PO7155 PO000002 23.05.2019 18
KY3214A583CD PO4299/PO7155 PO000002 23.05.2019 9
L0676609AB01 6681/1447 LV000003 24.05.2019 30
L0676610AB01 6681/1447 LV000003 24.05.2019 30
L0676611AB01 6681/1447 LV000003 24.05.2019 60
L0676612AB01 500/133 LV000004 24.05.2019 75
L0676678AC01 14-559846 IAC000001 25.05.2019 15
L0676702AC01 14-559846 IAC000001 25.05.2019 30
L0676683AC01 14-559846 IAC000001 25.05.2019 30

Thank you

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[petko]

Hello Pepe,

already done. I put simple file to attachment.

Basically - I need some formula/function in sheet "Order list" in cell J5 to check history (sheet "delivered history") to check how many times we have unique values in column "Num Commande" for customer which is picked in sheet "Order list" in cell D8.
We know that every customer has own prefix.

I put some example data in sheet "Order list", we know that they belong to customer Lion Value. I need to fill delivery number in cell J5 but I dont want to calculate manually how many unique "Num Commande" exist in sheet "delivered history" for this customer.
We can see that we have 4 unique "Num Commande" for customer Lion Value (with prefix LV) in sheet "delivered history". That means cell J5 (in sheet "Order list") should have number "5" (because we already have 4 numbers before + 1 new).

I hope it is clear. If not, tell me.

[FlameRetired]

If I understand correctly please try this formula in J5

Please Login or Register  to view this content.

[petko]

Wow, it works perfectly.

Many thanks !!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[petko]

Done. Thanks.