Count Functions

[geoffbird]

Hi,

I am struggling to get my head around the following, any help would be appreciated. I have the following list, and need to count the number of mixed sku/single sku pallets.

In this example Pallet 1 has 2 different item codes, so need to return 1 mixed pallet, and pallet 2 has only 1 item code, so expecting 1 Single Sku Pallet.

Any help would be appreciated

1	20965
1	20662
1	20662
2	20965
2	20965

 

[alansidman]

With Power Query, bring data into Editor and Group by Pallet and Count Unique Rows. Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Pallet"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
    #"Grouped Rows"

Book4
	A	B	C	D	E
1	Pallet	SKU		Pallet	Count
2	1	20965		1	2
3	1	20662		2	1
4	1	20662
5	2	20965
6	2	20965
Sheet1

 

[geoffbird]

I have managed to solve this by adding a extra colum to assist with the pallet counts,

=SUM(--(FREQUENCY(IF(C2:C500=ROW()-1,MATCH(E2:E500,E2:E500,0)),ROW(E2:E500)-ROW(E2)+1)>0))

This returns, Pallet 1 has 2 item codes,
the next row then return Pallet 2 has only 1 item code

I have then added a simple count of 1, and greater than 1

Going to try the power query thing but i have not used this before.

Appreciate all your help

 

[jorismoerings]

Hi,

as an alternative option:

Book1
	A	B	C	D	E
1	Pallet#	SKU		Pallet	Type
2	1	20965		1	Mixed SKU Pallet
3	1	20662		2	Single SKU Pallet
4	1	20662
5	2	20965
6	2	20965
Sheet4

Cell Formulas
Range	Formula
D2:D3	D2	=INDEX(A2:A6,MATCH(0,COUNTIF(D1:D1,A2:A6),0))
E2:E3	E2	=IF(SUM(--(FREQUENCY(IF($A$2:$A$6=$D2,$B$2:$B$6),$B$2:$B$6)>0))>1,"Mixed SKU Pallet","Single SKU Pallet")
Press CTRL+SHIFT+ENTER to enter array formulas.