Hello,
I haven't been able to figure out how to color 7 different ways a total number of rows automatically with a VBA code.
So basically I work on a client report on a daily basis that has 20,000+ rows. Once I've filtered it and hidden unnecessary rows, it comes down to around 1,000 rows. One client can have multiple transactions, so for example one client could account for 50 rows and another client for 3 rows. I've figured out a way already to count the unique number of clients that appear on the ~1,000 rows, which comes to about 160 unique clients.
I need to divide the total number of clients by 7 and have my macro fill-color those rows differently (yellow, light green, dark green, orange, blue, brown, purple). My problems are:
1. I usually never get a number that can be evenly divided by 7. We can use 160/7=22.86 as an example.
2. Using the above number, let's just round that to 23. I would highlight my first 23 UNIQUE clients with yellow. But those 23 clients could be 23 rows one day or 500 rows another day, depending on the number of transactions that those clients made.
Therefore, I really have no idea on how to make Excel count, divide and color 7 different ways my data.
Any help/input is appreciated! Let me know if you have questions!
This is my counting formula in case you find it useful:
I haven't been able to figure out how to color 7 different ways a total number of rows automatically with a VBA code.
So basically I work on a client report on a daily basis that has 20,000+ rows. Once I've filtered it and hidden unnecessary rows, it comes down to around 1,000 rows. One client can have multiple transactions, so for example one client could account for 50 rows and another client for 3 rows. I've figured out a way already to count the unique number of clients that appear on the ~1,000 rows, which comes to about 160 unique clients.
I need to divide the total number of clients by 7 and have my macro fill-color those rows differently (yellow, light green, dark green, orange, blue, brown, purple). My problems are:
1. I usually never get a number that can be evenly divided by 7. We can use 160/7=22.86 as an example.
2. Using the above number, let's just round that to 23. I would highlight my first 23 UNIQUE clients with yellow. But those 23 clients could be 23 rows one day or 500 rows another day, depending on the number of transactions that those clients made.
Therefore, I really have no idea on how to make Excel count, divide and color 7 different ways my data.
Any help/input is appreciated! Let me know if you have questions!
This is my counting formula in case you find it useful:
Code:
' Count Unique Clients
' Define Rge as the column A range without the header
ActiveWorkbook.Names.Add Name:="Rge", RefersTo:= _
ActiveSheet.Range("A2:A50000")
' Define unRge as a formula
ActiveWorkbook.Names.Add Name:="unRge", RefersToR1C1:= _
"=IF(SUBTOTAL(3,OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,1)),Rge,"""")"
ActiveWorkbook.Names("unRge").Comment = ""
' Count the unique number of CIDs that are NOT hidden
Range("AE1").Select
Selection.FormulaArray = _
"= SUM(N(IF(ISNA(MATCH("""",unRge,0)),MATCH(Rge,Rge,0),IF(MATCH(unRge,unRge,0)=MATCH("""",unRge,0),0,MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1))"
