Extracting specific data from a dataset

[jdhfch]

HI,

Please could someone help me?

I have a list of data from which I need to extract (using formulas) the total number of unique orders, by customer. There could be more than 1 item on an order, I just need the total number of unique order numbers for each customer, please? This is an idea of the dataset:

 

[eiloken]

HI,

Please could someone help me?

I have a list of data from which I need to extract (using formulas) the total number of unique orders, by customer. There could be more than 1 item on an order, I just need the total number of unique order numbers for each customer, please? This is an idea of the dataset:

in that case, you can use pivot table to do it

 

[jdhfch]

That is a great idea.. Thanks. One question though - it counts each line, not each order?

 

[Peter_SSs]

in that case, you can use pivot table to do it

Could you give an example to show the count of unique orders by customer as requested in post 1?
.. and also how the pivot table would produce the results as shown in the OP's example?

@jdhfch
For the future, it would help you get better & faster answers if you gave us the sample data in a form that we can copy to test with.
I suggest that you investigate XL2BB
Also, are you still using Excel 2016 as shown in your profile?

See if something like this would work for you.

24 01 18.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Cust No	Name	Order	Prod	Qty
2	1	Cust 1	Test 1		5
3	1	Cust 1	Test 1		1
4	1	Cust 1	Test 2		2
5	1	Cust 1	Test 2		1		Cust No	Name	No. Orders	Qty	Avg
6	1	Cust 1	Test 2		1		1	Cust 1	4	20	5
7	1	Cust 1	Test 2		1		2	Cust 2	3	17	5.666667
8	1	Cust 1	Test 3		1
9	1	Cust 1	Test 4		3
10	1	Cust 1	Test 4		2
11	1	Cust 1	Test 4		1
12	1	Cust 1	Test 4		1
13	1	Cust 1	Test 4		1
14	2	Cust 2	Test 5		2
15	2	Cust 2	Test 5		2
16	2	Cust 2	Test 5		1
17	2	Cust 2	Test 5		1
18	2	Cust 2	Test 5		1
19	2	Cust 2	Test 6		1
20	2	Cust 2	Test 6		1
21	2	Cust 2	Test 6		1
22	2	Cust 2	Test 6		1
23	2	Cust 2	Test 6		1
24	2	Cust 2	Test 7		1
25	2	Cust 2	Test 7		1
26	2	Cust 2	Test 7		1
27	2	Cust 2	Test 7		1
28	2	Cust 2	Test 7		1
Count Unique Orders

Cell Formulas
Range	Formula
G6:G9	G6	=IFERROR(INDEX(A$2:A$100,MATCH(0,INDEX(COUNTIF(G$5:G5,A$2:A$100)+(A$2:A$100=""),0),0)),"")
H6:H9	H6	=IF(G6="","",VLOOKUP(G6,A$2:B$100,2,0))
I6:I9	I6	=IF(G6="","",SUMPRODUCT(--(A$2:A$100=G6),--(MATCH(A$2:A$100&"|"&C$2:C$100,A$2:A$100&"|"&C$2:C$100,0)=ROW(A$2:A$100)-ROW(A$2)+1)))
J6:J9	J6	=IF(G6="","",SUMIF(A$2:A$100,G6,E$2:E$100))
K6:K9	K6	=IF(G6="","",J6/I6)