Hello,
*I should say before hand that this might be a confusing explanation, but hopefully the example workbook I am uploading will make more sense.
I have a couple of questions. I need to run a report that we gives to our customers that tells them what products are still available in the most concise way. All of our products are organized into classes. There are a ton of classes, but if it contains the letter 'C' that means it is closed out and no longer available. Each of our products comes in several different colors and sometimes only certain colors are available.
I already have formulas that count the total number of colors for each product and the number of colors for each product that are closed out. If all of the colors are closed out I need it to say: [PRODUCT NAME] - All out. If all of the colors are available I need it to say: [PRODUCT NAME] - All good. The tricky part is that if only some of the colors are out, I need a sentence that will say which colors are good or bad in the most concise way. For example, if there are 4 good colors and 3 closed out colors, it is more concise to say which colors are out so it would look like this: [PRODUCT NAME] - [COLOR1], [COLOR2], [COLOR3] Out. On the other hand if there are 2 good and 5 out its more concise to say: [PRODUCT NAME] - [COLOR1], [COLOR2] Good.
That is my first question. My second question is if there is a VBA sub that could take the place of the countif functions on the sheet. The sheet is going to be massive (more than 10,000 lines) and even two countif formulas on a sheet that big make the calculation time so incredibly slow.
Thanks in advance for any help, below is my sample workbook.
Example.xlsx
Bookmarks