Count number of trips by occurences

Hello,

I have a table with 31 days; each day consists of 15 groups; each group consists of several trips; each trip has only one of the 3 colors (value =1). I need macros to:

1) For each color, count the total number of trips that have the same sum of color = 0, or 1, or 2....
2) For each trip, mark similar trip with a code (so I can do grouping later). For example, in "Color 1" column, all trips that have the sum of color = 1 are marked as "C1_1", sum of color = 2 are marked "C1_2", etc.

Please see the attached Excel file as a sample of original data and desired outcomes (shaded).

I appreciate your help.

Any solution?

Originally Posted by Tinbendr

You are going to have to explain how you come to the totals in your file. Pick one from each color group and explain in detail.

I used a SUMIF to calculate totals, but I'm not getting the same numbers as your example.

E.g. in O2

Please Login or Register  to view this content.

Thank you for your reply.

I attached the revised file with formula to explain how I came to the total at Column N (used info from column P-T). I apologize for the wrong totals that I posted early.

Column A to F consist of raw data. I made up manually column H to T to explain the desired outcomes.

Hello Tinbendr;

Thanks very much for your SUPERB Macro skill.

Could you help me with the second part of the question? (assign same value to all trip that have same ID. For example: for color 1, all Ttrip ID = 1 will have value of 0; color 2 will have value of 2; color 3 will have value of 0). Pleasee see the attached.

Cannot be better! I'm wondering how long it would take a person to reach your macro level

Thanks very much for advice. It was remarkable that you could go that far by self-leaning.