Pivot counting distinct values (not unique) in single row (not columns)

[Quari]

Dear Forum members,

I hope I will provide you clearly what I need.
Excel 2013. Win 7
Formula: I have no idea what formula to use. In internet I found multiple examples how to do same but for columns.
I have following data:

Row number	Ticket	Month	Brand 1	Brand 2	Brand 3	Brand 4	Brand 5
1	RITM1313201	2019-8	Ford	Ford	KIA	KIA	Toyota
2	RITM1312925	2019-8	KIA	Toyota
3	RITM1310754	2019-9	Ford	KIA	KIA
4	RITM1310759	2019-10	Toyota	Toyota

I need a pivot and chart at the end which will show me how many disctinct Brands appeared in given month while"ticket" is a value counted.
E.g. There were following number of Tickets with Brand:
2019-8 1x Ford (disctinct number of Fords for Month 2019-8 is 1 despite of the fact "Ford" is listed x2 in first row) and 2x KIA (row 1st [second occurance ignored] and 2nd) and 2x Toyota (row 1st and in row 2nd)
2019-9 1x Ford (row 3rd) and 1xKIA (row 3rd [second occurance ignored])
2019-10 1x Toyota (row 4th, [second occurance ignored])


I do not care how many times 'Ford' is listed in single row. It is important for me to count only first occurance and list it later in pivot table

Result table would looks like:

Month	Ford	KIA	Toyota
2019-8	2	2	2
2019-9	1	1	0
2019-10	0	0	1

Thank you,
Quari

 

[hernantorres23]

Hi, an easy way for that is Power Query, first you need convert you data in a Table (Ctrl + t)
1. Step

2. Go to Data menu and select From Table, this action will open Power Query

3. Delete Changed Type step

...

 

[hernantorres23]

Hi, an easy way for that is Power Query, first you need convert you data in a Table (Ctrl + t)
1. Step
View attachment 940

2. Go to Data menu and select From Table, this action will open Power Query
View attachment 941

3. Delete Changed Type step
View attachment 942
...

4. Select two first columns, go to Transform menu and select Unpivot Other Columns

5. Delete column named Attribute

6. Close & Load To / Close & Load, this action put data in Excel, but in this shape

Please see this attachment It is only an idea

Hernan Torres
Mikel ERP

 

[DanteAmor]

Try this UDF

Function CountDistinct(Period As Range, Brand As Range, Data As Range)
  Dim a As Variant, i As Long, j As Long, m As Long
  a = Data.Value
  For i = 1 To UBound(a, 1)
    If a(i, 1) = Period.Value Then
      For j = 2 To UBound(a, 2)
        If a(i, j) = Brand Then
          m = m + 1
          Exit For
        End If
      Next
    End If
  Next
  CountDistinct = m
End Function

----------------------------------------
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use TextOnly just like it was a built-in Excel function. For example,

Book1
	A	B	C	D	E	F	G
1	Ticket	Month	Brand 1	Brand 2	Brand 3	Brand 4	Brand 5
2	RITM1313201	2019-8	Ford	Ford	KIA	KIA	Toyota
3	RITM1312925	2019-8	KIA	Toyota	Toyota
4	RITM1310754	2019-9	Ford	KIA	KIA
5	RITM1310759	2019-10	Toyota	Toyota
6
7
8
9	Month	Ford	KIA	Toyota
10	2019-8	1	2	2
11	2019-9	1	1	0
12	2019-10	0	0	1
Hoja2

Cell Formulas
Range	Formula
B10:D12	B10	=CountDistinct($A10,B$9,$B$2:$G$5)

 

[Quari]

@hernantorres23 @DanteAmor Thank you for your ideas - I will review them and check if I can reaplly.
I see I did a small mistake in Result table - it should looks like this:

Result table would looks like:

Month	Ford	KIA	Toyota
2019-8	1 (there was 2 initially)	2	2
2019-9	1	1	0
2019-10	0	0	1

Is there are way to edit own post? I checked FAQ and guidelines but did not found that information.

 

[Quari]

@DanteAmor I tested it and it works! Thank you for help and additional information (yes I am new with UDF and VBA) and I like this as I do not need to change anything in source data or transform it.

Now I have trickier question since counting is working like dream. I would like to select only particular records depends on value in another column e.g. only rows with Status "Closed" or "Closed Skipped" should be included while those with Status "Open" should be not taken into consideration. Pivot table can filter data but here?
Is it possible to change a formula you provided or UDF should be modified? I have some knowledge about scripts/SQL but I do not fully understand your UDF to be able to modify it by myself. Surely if I would study it longer + what particular syntax means.

Entry Data:

Row number​	Ticket​	Status​	Month​	Brand 1​	Brand 2​	Brand 3​	Brand 4​	Brand 5​
1​	RITM1313201​	Closed​	sie-19​	Ford​	Ford​	KIA​	KIA​	Toyota​
2​	RITM1312925​	Open​	sie-19​	KIA​	Toyota​
3​	RITM1310754​	Closed Skipped​	wrz-19​	Ford​	KIA​	KIA​
4​	RITM1310759​	Closed​	paź-19​	Toyota​	Toyota​

Result:

Month	Ford	KIA	Toyota
2019-8	1	1 (second KIA from Row #2 was excluded as Status is "Open"	1 (2nd Toyota excluded)
2019-9	1	1	0
2019-10	0	0	1

 

[hernantorres23]

@hernantorres23 @DanteAmor Thank you for your ideas - I will review them and check if I can reaplly.
I see I did a small mistake in Result table - it should looks like this:

Result table would looks like:

Month	Ford	KIA	Toyota
2019-8	1 (there was 2 initially)	2	2
2019-9	1	1	0
2019-10	0	0	1

Is there are way to edit own post? I checked FAQ and guidelines but did not found that information.

I'm so sorry, but these was only an idea, but if possible do it, you only need add two steps more, these are:
= Table.AddColumn(#"Removed Columns", "idx", each [Ticket]&[Month]&[Value])
= Table.AddColumn(#"Removed Columns", "idx", each [Ticket]&[Month]&[Value])

Then, your Power Query window Applied Steps (Advanced Editor) looks like:

And in Excel your Pivot Table looks:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1										Ticket	Month	Value
2	Your data example									RITM1313201	2019-8	Ford
3	Ticket	Month	Brand_1	Brand_2	Brand_3	Brand_4	Brand_5			RITM1313201	2019-8	KIA
4	RITM1313201	2019-8	Ford	Ford	KIA	KIA	Toyota			RITM1313201	2019-8	Toyota
5	RITM1312925	2019-8	KIA	Toyota						RITM1312925	2019-8	KIA
6	RITM1310754	2019-9	Ford	KIA	KIA					RITM1312925	2019-8	Toyota
7	RITM1310759	2019-10	Toyota	Toyota						RITM1310754	2019-9	Ford
8										RITM1310754	2019-9	KIA
9	Your data example result									RITM1310759	2019-10	Toyota
10	Month	Ford	KIA	Toyota
11	2019-8	2	2	2
12	2019-9	1	1	0
13	2019-10	0	0	1
14
15	Pivot Table created by htorres
16	Ticket_	_
17	Row Labels	Ford	KIA	Toyota	Grand Total
18	2019-8	1	2	2	5
19	2019-9	1	1		2
20	2019-10			1	1
21	Grand Total	2	3	3	8
22
pivot-counting-disctinct-values

This is the attachment updated:::Pivot counting with Power Query

 

[DanteAmor]

Hi @Quari, try the following.

I would like to select only particular records depends on value in another column e.g. only rows with Status "Closed" or "Closed Skipped" should be included while those with Status "Open" should be not taken into consideration.

I adjusted the code to consider the status.
Now the data area should start in the status column, check the example below:

Book1
	A	B	C	D	E	F	G	H	I
1	Number	Ticket	Status	Month	Brand 1	Brand 2	Brand 3	Brand 4	Brand 5
2	1	RITM1313201	Closed	2019-8	Ford	Ford	KIA	KIA	Toyota
3	2	RITM1312925	Open	2019-8	KIA	Toyota	Toyota
4	3	RITM1310754	Closed Skipped	2019-9	Ford	KIA	KIA
5	4	RITM1310759	Closed	2019-10	Toyota	Toyota
6
7
8
9	Month	Ford	KIA	Toyota
10	2019-8	1	1	1
11	2019-9	1	1	0
12	2019-10	0	0	1
Bands

Cell Formulas
Range	Formula
B10:D12	B10	=CountDistinct($A10,B$9,$C$2:$I$5)

Try this:

Function CountDistinct(Period As Range, Brand As Range, Data As Range)
  Dim a As Variant, i As Long, j As Long, m As Long
  a = Data.Value
  For i = 1 To UBound(a, 1)
    Select Case LCase(a(i, 1)) 'first column of Data
      Case LCase("Open")
      Case LCase("Closed"), LCase("Closed Skipped")
        If a(i, 2) = Period.Value Then  'second column of data
          For j = 2 To UBound(a, 2)
            If a(i, j) = Brand Then     'brands columns
              m = m + 1
              Exit For
            End If
          Next
        End If
    End Select
  Next
  CountDistinct = m
End Function

 

[hernantorres23]

@DanteAmor I tested it and it works! Thank you for help and additional information (yes I am new with UDF and VBA) and I like this as I do not need to change anything in source data or transform it.

Now I have trickier question since counting is working like dream. I would like to select only particular records depends on value in another column e.g. only rows with Status "Closed" or "Closed Skipped" should be included while those with Status "Open" should be not taken into consideration. Pivot table can filter data but here?
Is it possible to change a formula you provided or UDF should be modified? I have some knowledge about scripts/SQL but I do not fully understand your UDF to be able to modify it by myself. Surely if I would study it longer + what particular syntax means.

Entry Data:

Row number​	Ticket​	Status​	Month​	Brand 1​	Brand 2​	Brand 3​	Brand 4​	Brand 5​
1​	RITM1313201​	Closed​	sie-19​	Ford​	Ford​	KIA​	KIA​	Toyota​
2​	RITM1312925​	Open​	sie-19​	KIA​	Toyota​
3​	RITM1310754​	Closed Skipped​	wrz-19​	Ford​	KIA​	KIA​
4​	RITM1310759​	Closed​	paź-19​	Toyota​	Toyota​

Result:

Month	Ford	KIA	Toyota
2019-8	1	1 (second KIA from Row #2 was excluded as Status is "Open"	1 (2nd Toyota excluded)
2019-9	1	1	0
2019-10	0	0	1

Of course you can do it, in addition you can take the advantages of M language, if you are thinking in Power BI
Let me show you the code for Advanced Editor in Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Ticket", "Status", "Month"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Idx", each [Ticket]&[Month]&[Value]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Status] <> "Open")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Idx"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Removed Duplicates",{"Status"})
in
    #"Removed Columns1"

Here the result:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	Your data example
3	Ticket	Status	Month	Brand 1	Brand 2	Brand 3	Brand 4	Brand 5				Ticket	Month	Value	Idx
4	RITM1313201	Closed	sie-19	Ford	Ford	KIA	KIA	Toyota				RITM1313201	sie-19	Ford	RITM1313201sie-19Ford
5	RITM1312925	Open	sie-19	KIA	Toyota							RITM1313201	sie-19	KIA	RITM1313201sie-19KIA
6	RITM1310754	Closed Skipped	wrz-19	Ford	KIA	KIA						RITM1313201	sie-19	Toyota	RITM1313201sie-19Toyota
7	RITM1310759	Closed	paź-19	Toyota	Toyota							RITM1310754	wrz-19	Ford	RITM1310754wrz-19Ford
8												RITM1310754	wrz-19	KIA	RITM1310754wrz-19KIA
9	Your data example result											RITM1310759	paź-19	Toyota	RITM1310759paź-19Toyota
10	Month	Ford	KIA	Toyota
11	2019-8	2	2	2
12	2019-9	1	1	0
13	2019-10	0	0	1
14
15	Pivot Table created by htorres
16	Count of Ticket	_
17	Row Labels	Ford	KIA	Toyota	Grand Total
18	sie-19	1	1	1	3
19	wrz-19	1	1		2
20	paź-19			1	1
21	Grand Total	2	2	2	6
22
23
pivot-counting-disctinct-va(2)

 

[Quari]

Topic can be closed.
@DanteAmor thank you for help
@hernantorres23 thank you for your help as well. I did not tried your final solution as Dante's worked for me.

Cheers!