Concatenate VARIABLE ranges of Character Strings

See attached for a better context of what I'm getting at.

Please have forgiveness if this sounds familiar. I should say I DID NOT write this formula.

I have a Formula that recognizes the pattern of values (characters) in the range specified. I am wanting to create a Character string that will show the pattern of values (see UNIQUE field). The Formula does what I need it to do, but the one problem I have is that if the template type string?! in the formula (for lack of a better word) changes to 8 or 4 cells, as in ("00000000") or ("0000"), the results down below on the page don't change. For example, if one range is "000000" and the next range is "00000000" the next value in UNIQUE will only be "000000"....the 4th record in the series won't show up.

I have to manually change the number of cell references in the Formula as well as the number of 0s in the template type string to get the correct values in the UNIQUE field.

What I want to know is how can I get the formula to be Dynamic enough to recognize variable cell ranges?

Thanks for your forgiveness.

Dan

Ron,

Because you gave a good try with your response, I thought I'd show you what I ended up with from another couple of guys from the forum.

I know something about VLOOKUP tables, but not enough to be able to automatically do something like this, and have no idea as to how the IFERROR formula came about; that is just nuts. Maybe you might be able to suggest an MS Excel 2007 or 2010 book that ties into IFERROR statements?

Anyhow, I do thank you for taking a shot at what I was trying to get at.

Dan B

Ron:

Thank you for your efforts. I am sorry if I didn't attach a spreadsheet to work with. Yes I can see the logic there, but I was somewhat fried mentally when I put the post out there. I thought it would be simple enough to create a formula for what I'm talking about. I'll send the spreadsheet that this was based on with some changes (sets of 2 and 4 records) to give you a better idea as to what I am going to have to run the formula against.

1) When I copy pasted this into I2, it broke up into three lines. Was I only supposed to use what went into I2 before it broke up? Where or what would I use after the I2?


2) What do you mean by the set in S1:T5? My dataset never went that far over or down. And not all of the sets that I have are 5 records/2 fields long. I should mention that ultimately this script will be running against a database of several thousand records. The majority of them are 2 records, a good number are 3. There are SOME that are >4 records.

So a related question would be is how will this work for something that is two or three records long?

3) After I pasted the whole formula into I2, I also tried the part about holding CTRL and SHIFT down while hitting ENTER but nothing happened. Does this tie into question 1?

It would help if you could simplify things--do more step by step-- because it is somewhat overwhelming.
I was thinking before about whether or not there is a function in Excel that works like the functionality in Find and Replace, where there is a way of finding characters but also being flexible/dynamic enough to recognize other strings of character as it goes through the set.


Thanks,

Dan

Ron:

Attached is a spreadsheet with different sets/ranges of values going from 2 records to 4 records.

WHatever formula we/you cook up needs to be flexible to recognize more than one string of characters--i.e., not stuck on a 'default' string of 4 characters/2 records. As you'll see, the data set can go from that to having the next set be 3 records and then the one after that be 4 records and then go back to 3 or ...



Maybe I am off my rocker here. Maybe there is an even simpler way--I was thinking along the lines of FOR ACCIDENT NUM (WHILE <>0),GROUP(A2:B3, NAME("XXXXX"),AND + OR - X" ....

Dan

XOR:

Here is what I tried to do.

1) I copied the one line formula into D2 and dragged that cell's value down and got 1,2,3 ...repeating. I went back to Name Manager and did the same thing (after I cleared out D) and even selected the K and I columns to show the range in the sheet is twas upposed to cover and all I go t this time was 1s.

2) I copied the longer formula into E2 and Excel seemed to show it as going into 2 records ??! but I dragged it down and I got NAME#? or VALUE or # whatnot...

I am sorry to no end if I am pissy here (see other emails in Thread) its just that I have been banging my head on this for nearly a week and don't mean any thing personal.

Dan

XOR:

See Sheet 2 of XOR.xls (I threw in the .xlsm for good measure).

I clicked on D2, copy-pasted the 1 line formula into the REFERS To box, named it Arry1, and got out. I hit enter and nothing happened. I copy-pasted the Long Formula into E2, hit enter, nothing happened. I took a chance and dragged the Long Formula down and got the results I did).

I don't get how this is helping?

Not that you aren't trying to help, but the Long Formula is somewhat overkill.

Check out one of the other posts I made more recently to another guy who helped me out with one of the other fields. What this really boils down to is the formula in UNIQUE in SHEET 8. IF I can just get that formula to change (cell references and string on the end to recognize that I need variable string lengths and number of cell references), and have the other fields pick up on it, i.e., Pattern2 becoming a label that recognizes the instance of Unique, and then Pattern of Values recognizing Unique, and then Count recognizing the other values, then life would be soo much better.

Dan

Are you guys.....

Ok. I at least made some progress today--the person who helped me out on this earlier created some formulas that are bloody simple, but the only problem is that they are not entirely dynamic enough for a Several Thousand record database with the bulk of the data sets being in 2 record format ("0000").

See the attached spreadsheet and look at the formulas involved.

I will explain more if needbe but the answer as to how to make the text string more flexible in the Unique Field and how to get the Patterns2 values to be more flexible as well can't be that hard.

If I can get formulas this simple this close to working I have a feeling I can see the goal line...

Dan

Ron and X:

See SHEET 8.

UNIQUE is one field I am having problems with. See how the values go from 3 records to 4 and then back to 2 and then... See how the formula (as shown in the JPG) only has a text string ? that has 000000. How much editing do I really need to do to get the text string and number of Cell References that are concatenated to be more Dynamic/flexible/etc? Does it come from taking out the lines in between each set?

The other problem is Patterns2. Yes there's a look up table involved but how do I get it to expand to include the longer strings...which of course, rely on how to get the text string to be more flexible?

AGAIN:

I DID NOT create this. Someone else did but hasn't been able to explain clearly how to fix these things.

Please; I hope this isn't that complicated.

Dan

XOR:

Please.

Forget the other suggestions. Now.

I want to hear what YOU would do in this situation. Until I get an answer from the other source who created it, just look at the data like this:

FOR each series of Duplicate ACCIDENT NUM, there is a pattern of values in the K and I fields.
Group the Patterns of values and Label them.
Create a field for the Pattern (just to show what it is).
If there is a different pattern than the one before it, Group the values, and label it.
If the next pattern of values is the same as one that has already occurred, use the same label.
Create a field for the Groups and a Count for how many times each individual Group has happened.

I would like to think this won't take More than 4 fields, and not require a VBA. SHouldn't each field have its own Formula?

=GROUP(A2:B3).

=TEXT("A2:B3" or something similar for the Pattern Field)

=LABEL("A2:B3" or something similar for each Group of values--the key is to make this flexible. For the most part (several 1000s of records, there are going to be 2 record sets =="0000", but the series of record sets may also vary, from 2 records to 3 to 4 to 2 to 3 to 5 to 2 and so on... How can I create a DYNAMIC or malleable way of showing the changes in numbers of characters? Isnt' there a Character String function for this? )

=COUNT(A2:B10 (to indicate the range),A2 (for field with Group Label)

How really hard is this?

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

Ron,

I shouldn't have to be dealing with Circular References. I don't know how to solve them, especially at this point. Maybe its not that hard but it feels that way.

No, your solution didn't work. The text string should be based on the K and I values; I shouldn't have to add in another column to get to what I need. They are coded as General. They are also the pre-existing values that the Unique label should be based on.

Maybe the best way to describe my situation is this.

Start with the sets of duplicate ACCIDENT NUM. Take the Pattern of values in the K and I fields for each set of Duplicates, and Group them.
Label the Group.
If the next set of Duplicate Ids has a different Pattern of values in the K and I fields, Group them and label them as well.
For the next instance of a Previous Pattern of values, use the same label as the one you had created before.
Create a Count field for the Groups of Data and count the number of instances of each Group.

If there is a simpler way to do what I have just described than even the formulas I have had to deal with, then I am all ears. I would like to think that this could be done in two fields and not 4-5 like what I am dealing with but this is as close as I have gotten.

Use SHEET 8 and the values in ACCIDENT NUM, KILLED, and INJURED to do this. I would like to think it should be as simple as:

K I PATTERN LABEL COUNT
0 0
0 0
0 0 000000 a 2

1 1
1 1 1111 b 1

0 0
0 0
0 0 000000 a

1 0
1 0 1010 c 1



Dan

XOR LX:

Because you gave a good try with your response, I thought I'd show you what I ended up with from another couple of guys from the forum.

I will even say that in another situation, your formulae might actually be what I need, and I will do what I can to take it to heart.

I know something about VLOOKUP tables, but not enough to be able to automatically do something like this, and have no idea as to how the IFERROR formula came about; that is just nuts. Maybe you might be able to suggest an MS Excel 2007 or 2010 book that ties into IFERROR statements?

Anyhow, I do thank you for taking a shot at what I was trying to get at.

Cheers,

Dan B

XOR:

See Sheet 3.

I believe I did what you are indicating--Values of Killed and Injured in correct fields, proper name for Array, proper records/Rows to place formulas in.

However, while I appreciate your efforts:

Count of records is nice, but not needed
Value in E = first value (?) in all records in set – not correct record or field to work with

Spacing between each set of values seems to be determined by Count of records in each set, and value in E is not what I need (see above).


Need pattern field for each pattern of values (i.e., 0000, 000000, 010101010, etc..)

Need to group patterns of values and label them (0000 = a1, 000000 = a2, 010101010, a3...)

Need to create new groups based on new patterns (if next pattern of values doesn't = pattern in a1, then a2 = 000000, and so on. If next set of values is same as a1 then put a1 in label field. If next pattern of values doesn't equal a1 or a2, then create new pattern field, new group label, and so on. )

Count each time a group and its label comes up a1= 1, a2 = 1, a3 = 1, if a2 comes up again, then a2 Count = 2, and so on.
Grouping and labeling needs to be flexible given that there will be a good number of groups of varying sizes though basis will be two (2 records 3 records 4 records, then could be 2 then 3 then 2 then...)

Hope you can roll with this.

Back to the drawing board.

(Will root for England in the World Cup if that helps

Pl see attached file.I have put formula in UNIQUE Column and other columns.A helper column is also used to have single patterns (Avoiding Duplicates).

Kvsrinivasamurthy:

GOAL!!!!

Just about. Everything is there except the formulas stop working after I put in new values. This is for everything except the Unique field.

How do I get the formulas to work? Do I copy-paste? Drag the cells?

If we could get them to work with new values that would be immense.

Dan

x:

I'll give what you just wrote a shot but I don't sense you got what I just laid out in terms of the complexity of what I need--I didn't see how your formula(s) covered all of that.

Dan

XOR (and RON if you're still out there);

See SHEET 8.

This is exactly what I have been wanting all along. Forget the Pattern and Helper fields.

(Again, I did not write this out. There is another guy from the forums who created the fields and the values for Patterns2, UNIQUE, Pattern of Values, CHAR ASSIGNED, and COUNT, and another guy who got it to keep going when the UNIQUE field at least kept going.

PATTERN2 references the VLOOKUP in CHAR ASSIGNED. Count creates its count based on each instance of PATTERN2.

UNIQUE is = to the Pattern field (the way I need it since if we were going to use the Alphabet we'd run out of letters (duh). The VLOOKUP values create the Index for the Values in CHAR ASSIGNED. Based on this, I get a count.

****************

The only problem I have now (which is where you guys can come in ????) is that when I got it this this morning I still coulnd't get the PATTERN2, Pattern of Values, and Count fields to advance if there were more instances.

I tried increasing the cell ranges (after I added more values in the Killed and Injured Fields, tried copy pasting values, tried other basic stuff but still couldn't get it to run beyond where it is right now.

If you guys can figure out a way to make this consistently run beyond where it is then I'll root for whomever you have in mind in the WC.

Dan

Ron,

See SHEET 8 on attached, and see most recent Comment/Reply below.

Also refer to SHEET 8 on initial sheet.

On the initial spreadsheet by the other submitter/poster Pattern2 values only went up to the Pattern of Values = 130102. Even if I entered in new values in the Pattern2field, the formula won't advance--I keep getting an N/A error, can't seem to figure out where it is in the formulae that I have in the other fields that I am missing something.

The Pattern of Values formula is considerably different than the one that was in the original file (.....ans.xlsm)and I can't figure out what it means, and even if I expand the range of cells to 500 (which will still not be enough for the size of the database I have) I still cannot get the Pattern2 Values to advance.

I am trying to challenge myself to learn, but I just keep going around in circles.

Whatever you can offer would be much appreciated.

XOR and Ron,

I may not have described the order of opertaions correctly back there but overall you guys can see what I am really needing.

Kvsrinivasasmurthy,

See attached with more detailed comments about the situation I have been running into with the last couple of attachments you have sent.

Just so I know, where are you? What time zone? It is 10:30 am PST Mon/23 right now.

I will do what I can but I still cannot get the values in Pattern2, Pattern of Values, and Count to go beyond what is already there. I keep getting the N/A error message in the Pattern2 field every time I put in a new pattern of values in the Killed and Injured fields, and so therefore I can't get Pattern of Values and Count to increase as well.

I will try to figure out what the N/A error message in Pattern2 is referring to, but could use some help here.

I've attached a screen shot of what I am talking about.

Don't worry about the J field--you can include it if you like--but it wasn't central to the main tasks. I have heard of the Ctrl+Shift+Enter keys part, but don't know what you mean by "confirming array formulas" by using the keys?

Thanks again. We are just about there if we can get these 3 columns to work.

Daniel B

K,

Same difference. No change (no advancing in values in Pattern2).

Just so you know, I'm going to need the formulas to cover more than 500 records--I've got several THousand that I'm ultimately going to be dealing with.

I tried going back to your "1" sheet and the "dan" sheet and thought that I could see what you did to get Pattern of Values to go beyond 130102 (which was the last value you had in the first sheet in Pattern of Values that that field recognized) but I couldn't see it. I looked at "1" and at least a couple more values in Pattern2 showed up in Pattern of Values but for some reason everything is hardwired (!?) to K53. I even thought the original A field values had something to do with this since they are in CharAssigned ($A$number) but no difference either.

But I don't know what to make of the IFERROR formula that you have in the "2" sheet in the Pattern of Values field (which still seems to be wired to K53, even if I make it non-absolute (take out the $) but nothing changes.

Anyhow.

I know the answer has to be right in front of me/us if I keep getting N/A in the Pattern2 field, but I just can't figure out where, and what would be causing a circular reference.

Thanks again--you're doing an Awesome job.

Dan

Kvsrinivasmurthy:

See attached, Sheet 8.

I had some luck (!) by copy-pasting the value in Pattern2 into the Pattern of Values field, and once I did, I right clicked (due to Green Triangle) and selected "Keep Original Values and Formatting" in the drop down and I was able to get the Unique Value to stick (!) and the Pattern2 value to kick in. I then went to the Count field, cleaned out the contents except for the first record, changed the cell reference to the last one, and dragged the formula down, and the Count updated!

If the Pattern2 value was a Pattern of Values that was already on the list, when I dragged the Unique Field down, it automatically updated the Pattern2 field with a referenced to a CharAssigned value, but if Pattern2 was a value that wasn't previously assigned to a Char Assigned Value, that's when I had to do what I did above.

The bigger problem is that in the IFERROR formula, if I was to drag it down, I kept getting blank fields in the Pattern of Values field; I also have been seeing how there is a circular reference in the formula but I don't know where it would be in the formula's references. It doesn't matter if there is 500 instead of 53, I get the same results.

Anyhow, at LEAST I have gotten somewhere!

(I am curious though, when you did the formulas, were they intended to automatically update the Pattern of Values and Count fields, instead of manual updating?

As in, when I am working with my final database, with Killed/Injured fields with patterns of values, when I apply the formula for Unique and Pattern2, will Pattern of Values and Count automatically update or will I have to drag the Count formula all the way down? I can do it I just thought there was a more advanced way to go).

Thanks again,

Dan

Pl see attached file. Changes done in formula as a drawback is noticed.Range also extended to Row 5000.

Kvsrinivasamurthy:

=IF(GOAL='REACHED',Concatentate'!'Until)....

Unreal.

Ok, yes its a little funky seeing '0000 in the fields for patterns, but its a small price to pay after all you have done. Do not worry. Have a pitcher of your favorite drink on me in your currency, and I'll work to invite you to this country...

Epic.

(Yes, if you want to fuss around so that there aren't '0000 types of values, go ahead, but really, you deserve an epically big break.)


Thanks again to no end my friend
Daniel B
Sacramento, CA
(State Capitol of CA)

Formula in I & J columns changed.

kvsrinivasamurthy:

As a courtesy to all our supporters, we hereby would like to offer you full retirement ....

YOU are hired! Now...

=IF(DREAMCOME = TRUE, THEN CONCATENATE....

Good work. Good work.

Thanks to no end my friend.