Need VBA to Create Sentence Based On Criteria

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

Hi chaddug.
try to use this formula in column 'E' (not the whole column B:B, and only a specific range B3:B10000)

Formula:

Please Login or Register  to view this content.

Thanks Nilem, Ill try that. Any suggestions on the first part of my question? I appreciate the suggestion, but that is sort of a moot point if I cant get the first part to work.

Thanks again!

For a particular Component, a class of "F" would indicate that the item is good - correct?

Bob

and what the result should be in this case (two "out" and two "good")
BEYOND THE PINE BLUE C
BEYOND THE PINE GERY CZ
BEYOND THE PINE YELLOW F
BEYOND THE PINE ORANGE F

Yes, anything without a C is good. And I dont suppose I really care which way it goes if there are equal good and out, I guess just go with good if they are equal.

Give this a try

Please Login or Register  to view this content.

Originally Posted by stnkynts

Give this a try

Please Login or Register  to view this content.

Thanks stnkynts, Im not at my desk at the moment, so I cant test it out, but looking over it there are 2 things I noticed that I think might be a problem, but I could be wrong. First the good products aren't always 'F' they could be 'E' 'O' 'OJ' 'EO' 'OZ' and probably a couple of other things that I'm not remembering. Looking at your code, it looks like it is looking specifically for 'F's, when it should be looking for anything that does not contain a 'C'. Second, it looks like it is only going to say either All Good, [Colors] - Out or All Out. The All Out or All Good is fine, but it needs to decide if there are more that are Good, or more that are Out, and pic the one that has the least in that category. Does that make sense?

Thanks again though, this is much farther than I was.

Yeah it only looks for F & C. Best of luck finding a solution to your problem.

I have modified your code a bit, and it think its mostly doing what I need. But it doesn't seem to work when its supposed to say All Out. It just lists all of the colors and says out. Any idea why?

Here is the workbook I'm using: Example2.xlsm

Please Login or Register  to view this content.