Autofilling and concatenating info onto a main sheet from a data sheet

I'm trying to make more efficient an Excel task that involves a lot of manual entry, but the information, though many different types, is consistent once used. For example, if I type the first however-many characters of a language string (like SQL, example, and typing up until it differentiates from similar strings), I want it to fill-in the brief "layman" explanation of what it's doing.

I envision putting it on separate tab (that I will ultimately hide) and that I can transfer/copy for the hundreds of workbooks I need to complete. Think like a vlookup or index/match scenario (except doesn't put your results into a different cell, if possible) meets autofill world (except, since you "can't remember" the verbiage you used 20 workbooks ago, you're not filling in completely what you're typing, but the "layman" equivalent. A drop down won't work either, since you are typing what you are copying in front of you, but looking for what you have entered EXACTLY for that particular language string 20 workbooks ago.)

I'm thinking, on that separate interchangeable tab from workbook to workbook that have the language string in Column A, and then the layman explanation in Column B, and constantly keep adding to it. But here's the kicker: I want to have the results all in one large, text-wrapped cell, and like SQL language that is separated by parentheses for specific actions, but CONNECTED by And's (&) and Or's (|), I want the layman explanations to read the same way. (?) are 'is", (~) is "contains", (!) is "not".

For example, this is what I'm typing (essentially the info from my hypothetical Column A): sec_class~('CP') & sec_group!?('XYZ') to fill-in from the hidden worksheet, from Column B, (Sec Class contains CP) AND (Sec Group is not XYZ). So, see below what I'm thinking for Col A and Col B entries (unless you can think of a better system):

Essentially, as I type the following string, with each component separated by "[]":

[sec_class~('CP')][ & ][sec_group!?('XYZ')]


to read:

(Sec Class contains CP) AND (Sec Group is not XYZ)

in the large, text-wrapped box.

Now that I'm thinking about the functionality and limitations of Excel (prove me wrong, experts!), I may have to type the language in one area, and have the results (layman explanation) end up in my ultimate large, text-wrapped cell (THE ONE THAT WILL BE VIEWED). This can be on the same page off to the side, or on an "Entry" tab (perfect world I'd love to have in the same cell if feasible).

Appreciate!
BWR

This isn't a full solution, but a couple of thoughts for same cell changes. In both cases you'd need to work out in advance what might need to be changed - although for the first you'd need a complete list, whereas the second might work with more generic set of rules.

Option 1 - use the Autocorrect functionality. There's a list of things that excel amends as you type. For example, a c in brackets is automatically changed to a copyright symbol. You can add to the autocorrect list, either "permanently", or you could have macros set for the Open and Close events that added items to the list on opening, and removed them on closing.

Option 2 - use a Change event macro. You can build a check into the start of the macro so that it only triggers when a cell in a specific range is changed. The macro would apply the various rules you define to the cell value.

Does either of these sound feasible? If so, I can put together some sample macro code. I think the trickiest bit may be defining a comprehensive set of "rules"?

bwr,

You can use a series of Replace function code line paying particular attention to the order of replacements. These would serve as rules Trevor speaks of. In this example, Place the SQL line in cell A1 then run the code. You can replace Range("A1") with looping cells if needed.

HTH,
Maud

Please Login or Register  to view this content.

Trevor/Maud-

I like where we're going with this.

Trevor S-
Not sure that I get the concept of Option 2, but with Option 1, if it's similar to modifying the existing AutoCorrect, I think this does everything in the same cell, this seems like a good route. I would just want this task-specific AutoCorrect to be for these type of workbooks only, and not my other Excel activities outside of this scope.

Maud-
Ran your macro and worked flawlessly. Please explain the "looping cells" concept.

Thanks,
Brian

This page: https://msdn.microsoft.com/en-us/lib.../ff840663.aspx shows how to modify (adding to and deleting from) the existing autocorrect using a macro. So you could have a macro add the extra items to autocorrect when that workbook is opened, and remove them again when it is closed. The question is more whether you can define a set of corrections that don't impact on each other. For example, a [ appears to be treated differently depending upon whether its followed by an & ...

My "option 2" is along the same lines as Maud's macro. The only difference is that I'd been considering using the Worksheet Change event (see examples here: https://msdn.microsoft.com/en-us/lib.../ff839775.aspx ), so that the changes would be automatic, rather than needing to run the macro. However, it may be better to see what you've typed, and then "translate" it, rather than it happen automatically?

Trevor/Maud-

Great efforts from you both. Advancing along efficiently, so appreciate the work. Just a few more tweaks, and I think I'll have it where I want it.

1) Went with Replace method vs the Autocorrect (the latter being what I thought I originally wanted, but decided against because a) unlike Replace macro, I have to basically retype everything anyway vs cut/paste/run macro and was not efficient and b) if I don't Delete Replacement in AutoCorrect after Add Replacement, it seems the Autocorrect is permanent even in other new books and in cells/contexts I don't want it to AutoCorrect.

2) The code, Maud, worked perfectly, but I just have one issue: I need the last character in the cell (essentially, the end of SQL formula) to end with a closed parentheses ")". I figured the open parentheses "(" into the other lines of the code, but because each string of code ends uniquely, and can't figure out a way to close the string automatically. My string of code could end with a letter, a number, an apostrophe, etc, and there's no constant. Is there a way, using the sample code above for Replace, to have the absolute last character be a ")", essentially the last line of my Range code?

3) And Trevor, along the lines of your thinking, yes, I want it to be automatically run, but yes, I will want to proofread too for any minor adjustments. But I'm assuming, that once the macro has run, I can just go and check it (meaning it won't alter again, unless I rerun the macro?). I'm in the process of incorporating the color changes, so thanks for that. But to you both (or anyone), I want it to run in the entire workbook. Cell B11 is the same location on all of the sheets/tabs of a workbook, so I just want to run the macro once, and if the workbook has 27 sheets, run the Range macro automatically for all sheets, simultaneously, and then do my proofread. Correct me if I'm wrong, Maud, but the code above basically just does Cells A1-A10 for the sheet I'm on, and I need all of Range code to be run for all cells B11 within a given workbook.

4) What does an asterisk "*" do in code? Is it a wildcard, or character replacement?

Thanks again for the info and the progress, and in advance of the continued efforts.

BWR

Nice tweak Trevor!

Maud

Trevor (or Maud)-

Pure awesomeness with the tweaks to Maud's thread. Flawlessly, including the exclude sheets, as I had two hidden sheets that were not to be changed. As an additional caveat to sheets that I knowingly want to exclude, there are sheets that I unknowingly want to INCLUDE, but have blanks in cell B11 (targeted cell in the code above), but DO NOT want to add the ")", and simply want leave blank if they don't have anything there to begin with. Can we tweak that If Right line, or add an additional line if B11 is blank to leave blank?

Thanks!
BWR

Trevor-

"bit" as in an extra space, or as I would a sheet in the form of 'IF B11=""' or 'IF B11=0'? Please define "bit". Thanks!

BWR

Got it. For whatever reason, your string didn't come through upon first review. Thanks.

BWR

bwr,

How about this alternate tweak? Skip sheets whose tab color is red. No need to alter code to add skip sheets.

Please Login or Register  to view this content.

Maud

Appreciate, Maud. I'll definitely use, as I always color code my tabs, but in this instance, I have a finicky end recipient that loves consistency and I guess, just plain vanilla, so it will just cause for many questions as to why are the tabs a different color.


Alright, Trevor or Maud (or anyone), we've got our tightest model yet, and though I would have gathered all inquiries for you at once, always never the case. Now, my biggest issue (and one that I want to dig in deeper) is the previously discussed "*", or wildcard.

1) I guess, first and foremost, does an "*" have to represent EACH missing character, or can it represent a series of characters in between? (This may answer a lot).

2) My current dilemma is that with all of my rules, and appropriately placing them top to bottom is that some rules are grouped together, to be included together when put in the following format: "!($FOX_TROTS=ABCD & $FOX_TAILS=WXYZ)" to read "((Fox Trots is not ABCD) AND (Fox Tails is not WXYZ))" whereas:

a) "!" means "not", or written out "is not";

b) "&" (or "AND") can be substituted with "|" (or "OR"), hence my use of wildcards ("*") to get the characters and spaces in between the core rules;

c) "$FOX_TROTS" and "$FOX_TAILS" can be substituted with any of handful of substitutions (such as "DEER_FEET" and/or "TURTLE_SHELLS"), but generally speaking the two formers go together when used;

d) "ABCD" and "WXYZ" can be any of a huge list of identifiers, of any amount of characters, in no particular order, and not necessarily used together;

e) and most of the time, there will be just two paired together (i.e., FOX_TROTS and FOX_TAILS), but sometimes there can be three or more of any combinations (i.e., FOX_TROTS, FOX TAILS, DEER_FEET)

When I use "!($FOX_TROTS=ABCD & $FOX_TAILS=WXYZ)" , I currently run the macro and get !(Sec Group is FUND) AND (Sec Class is STIF) ,so I'm close. And then in the macro (below the initial strings, near the end), I try to run cell = Replace(cell, "!(Sec Group is*Sec Class is *)", "((Sec Group is not ""(Sec Class is not ""))", , , 1) and it doesn't do anything. I think I am simply using the wildcards incorrectly.


But I have to watch what I do with the "!", and where I place it anywhere in the macro (unless I use it with a specific string of other characters), because although it always means "not" or "is not", it's never consistently placed in a string (could be at the beginning, as in the aforementioned case, or also in the middle of a string, depending on where the programmer puts it. It just basically states whatever is being used, it is NOT the case (or the opposite).

Thank you for your continued feedback and efforts,
BWR

Ok... not a full answer yet, but a start!

1) Wildcards - * is any number of characters. Use a ? for a single character. And if you need to find an actual * or ?, preceded it with a ~ symbol.

2a) that's a tricky one, because in your example the ! isn't next to the =, so somehow you need to tie the two together. I'll need to give that more thought. We need to consider the syntax provided by the brackets.

2b) More straight forward ... use a line similar to the & to AND line for | to OR. With any of these lines, there's no problem if the string that the line is looking to change can't be found.

2c) Should be possible to do using wildcards and the Proper function (converts the case of text to lower with a capital for each initial. I'll try to work out some coding.

2d) Shouldn't be a problem, as that part of the string should be left untouched. Only problem I can see would be if that string contained one or more of the "special" characters ($!_=&|) that we're trying to convert elsewhere.

2e) Shouldn't be a problem, the macro should be able to convert all occurances found.

I will look further into this, but maybe a couple of days before I get much chance, so if you manage to work anything out in the meantime, please keep the thread updated.

Some more code lines that you may want to consider adding some of to the previous code - looking mainly at cases. Order is again important!

Please Login or Register  to view this content.

This still doesn't do everything! I'll next try to look at how to deal with negated brackets - i.e. how "!(A=B [&] C=D)" can become "A is not B AND C is not D"