Column A has a customer ID
Column B has the date the customer had a transaction
The maximum number of transactions a customer can have is 365, one per each day. This is a rule from the data source.
The user pulls the data from the source and copies the data into Column A and B on an ongoing basis as the year goes on. They will go to the first blank row, and copy the next weeks worth of data into the spreadsheet.
I can require, if its needed to solve this problem, additional formulas in the subsequent columns that the user can copy down when they paste their next batch of data, but the user does not have the ability to edit, delete, or any other way manipulate the sheet. (ie remove duplicates and create a list of unique IDs)
I need to know via formula the breakdown of my total transactions, within each individual month, by transactions per unique ID.
So I want to know if in April (assuming 500 total transactions), that 10 of my customers had a transaction each day in the month 10x30=300, and that 15 of my customers had transactions on 3 of the days in the month 15x3=45, and that 145 of my customers had one transaction within the month.
My report will have the months going across columns, and my rows will be
1x
2x
3x
4x
5x
6x
7x
8x
9x
10+ (10 or more transactions)
Additionally, there is one customer, ID 1, that needs to be excluded because it breaks the one transaction per day rule. This one customer can have an infinite number of transactions each day.
Column B has the date the customer had a transaction
The maximum number of transactions a customer can have is 365, one per each day. This is a rule from the data source.
The user pulls the data from the source and copies the data into Column A and B on an ongoing basis as the year goes on. They will go to the first blank row, and copy the next weeks worth of data into the spreadsheet.
I can require, if its needed to solve this problem, additional formulas in the subsequent columns that the user can copy down when they paste their next batch of data, but the user does not have the ability to edit, delete, or any other way manipulate the sheet. (ie remove duplicates and create a list of unique IDs)
I need to know via formula the breakdown of my total transactions, within each individual month, by transactions per unique ID.
So I want to know if in April (assuming 500 total transactions), that 10 of my customers had a transaction each day in the month 10x30=300, and that 15 of my customers had transactions on 3 of the days in the month 15x3=45, and that 145 of my customers had one transaction within the month.
My report will have the months going across columns, and my rows will be
1x
2x
3x
4x
5x
6x
7x
8x
9x
10+ (10 or more transactions)
Additionally, there is one customer, ID 1, that needs to be excluded because it breaks the one transaction per day rule. This one customer can have an infinite number of transactions each day.
Last edited:
