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:
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:
Thank you,
Quari
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