Concatenate VARIABLE ranges of Character Strings

Pl see attached file with UDF "ConcatenateTillBlank" for column I result .

Kvsrinivasamurthy:

Thanks very much for this--I clicked and dragged it down as long as I had values in the right column and it worked.

NOW: the problem is that the field that I really need to work--UNIQUE--is working, but the problem is that there are 0s being put in front of the first digit in the pattern of values, so that I am getting the wrong string. I also can't seem to get a label for the the group (based on this string), and because of that I can't get a Count for the label for the group.



For example: I have 3 1 2 1 2 3 in a series, and I can run your Formula fine in the UDF field, but in the Unique field, I am getting 00312123. The main reason for that is because in the formula in the Unique field, the character string has "00000000" as a default value, but it doesn't change if I have a string that is based on what I have--it should be "000000" instead. The set of values right before it had a similar situation--the character string was "00000000" but the pattern of characters was "0000". (I had to MANUALLY change the string and the number of cell references that it was concatenating together.)

What I need to know is this:

Because my several THOUSAND-record dataset is going to have a wide variety of record sets with different values--3 records to 2 to 2 to 3 to 4 to 2 to 2--how can I get the formula in the Unique field to be DYNAMIC enough to have the IDs in your UDF field and the Unique field to change? (As well as the Cell references?)

SO: instead of "000000" the whole way, I would need something like "000000" switching to "0000" and then "000000" and then "00000000" and then "0000" ....

Click on I4 to see what I am talking about.

The other big question I have is how do I get the Pattern2 values to recognize the different Pattern of Values field information so that the Count can be based on this?
The string of characters in the Unique field create a label in Pattern2, and in the Pattern of Values field, and once these happen, the Count field updates for each Pattern2 value.

If htis is too much to take on, I can roll with it, but if you can give me any help, that would be great. If you could also write out the steps you took to get there that would be a help too.

Thanks again,

Dan