Text Expansion in Excel

[YaseenAli]

Hi,
I have to frequently work with a fixed set of station names e.g.

Compile_Active_Student_List.xlsx
	P
13	Stations
14	Station A
15	Station B
16	Station C
17	Station D
18	Station E
19	Station F
20	Station G
Active Student List

1. I have multiple reports coming in from multiple stations and some times some reports do not arrive
2. After merging all sheets
3. I get a unique list of station names and compare it with the Complete Stations List, so that I can find out which stations are missing
4. a quick countif/lookup/conditional formatting solves this

My Solution & Question

I want to have the complete Stations List always at hand and Currently I use a text expansion software called breevy, (trying to learn AutoHotKey but don't have the time for now).

• I use following expansion in breevy

=FILTERXML(
        "<t><s>" &
        SUBSTITUTE(
            "Stations,Station A,Station B,Station C,Station D,Station E,Station F,Station G",
            ",",
            "</s><s>"
        ) & "</s></t>",
        "//s"
    )

• I have to use above because normal text expansion puts every thing in a single cell and
• putting {Enter} or \n makes the text expansion very very slow (imaging a 100 stations)

Is there a better way to do this, without using VBA

 

[Dan_W]

I have no issue trying to come up with a solution that doesn't involve VBA (whether I can or not is an entirely different issue), but can you give a little more reasoning as to why VBA is not an option? It just must help to understand the parameters of your requirements, is all... Also, you wrote it in a fairly large, emphatic font.

 

[Dan_W]

Ok - so here some possibilities:
1. Use a Name. You can create a name in the Name Manager (I used STATIONLIST) that will be accessible from the worksheet and will list the Stations A-G in a column as per your example above. It's just a matter of loading the Name manager by pressing either Ctrl F3 when you're on the worksheet or selecting the Name Manager button from the centre of the Formulas tab. Then press New, type in the Name you want, and in the the Refers to section:

=TRANSPOSE({"Station A","Station B","Station C","Station D","Station E","Station F","Station G"})

After that, you can then just type in =STATIONLIST into a cell on the worksheet, and you get:

The

=STATIONLIST

is not actually visible, I just added it in the image above for your reference

 

[Dan_W]

The second option is you could use a Custom List. The benefit of a custom list versus option 1 above, is that option 1 is only available in the workbook you added the name to. So if you're going to be doing this across multiple workbooks, you will need to add the code above to each workbook. Which is not a hugely onerous task which recent formula addins available on the MS Store, for example.

A custom list, by contrast, is available across the entire application at all times in any workbook. So if you've ever typed into a cell Monday, and then Tuesday in the next one, you'll see that you can drag the corner of the cell down to autofill the subsequent cells with the rest of the days of the week. That's exactly what you could do with this.

You can acces the custom lists screen by going to the Options window (Ribbon -> File Tab -> Options (usually at the bottom)) then click on the Advanced tab on the left hand side and scroll down towards the bottom until you get to the Edit Custom Lists button:

 

[Dan_W]

Separately, AutoHotKey would be ideal for exactly this task. You also wouldn't need to use \n, if that's problematic. AutoHotkey also has the added benefit of being able to engage with COM Objects, which means that it would be able to interface with Excel directly and actually put whatever values you wanted in whatever cell you wanted. But if you're doing that, you might as well just be using VBA.

Anyway, hope that helps.

 

[YaseenAli]

The second option is you could use a Custom List. The benefit of a custom list versus option 1 above, is that option 1 is only available in the workbook you added the name to. So if you're going to be doing this across multiple workbooks, you will need to add the code above to each workbook. Which is not a hugely onerous task which recent formula addins available on the MS Store, for example.

A custom list, by contrast, is available across the entire application at all times in any workbook. So if you've ever typed into a cell Monday, and then Tuesday in the next one, you'll see that you can drag the corner of the cell down to autofill the subsequent cells with the rest of the days of the week. That's exactly what you could do with this. View attachment 65228

You can acces the custom lists screen by going to the Options window (Ribbon -> File Tab -> Options (usually at the bottom)) then click on the Advanced tab on the left hand side and scroll down towards the bottom until you get to the Edit Custom Lists button:
View attachment 65230

Separately, AutoHotKey would be ideal for exactly this task. You also wouldn't need to use \n, if that's problematic. AutoHotkey also has the added benefit of being able to engage with COM Objects, which means that it would be able to interface with Excel directly and actually put whatever values you wanted in whatever cell you wanted. But if you're doing that, you might as well just be using VBA.

Anyway, hope that helps.

Hi Dan_W,
Thank you, all three of your comments were very helpful.
I did not think of named ranges and custom lists for this purpose.

 

[YaseenAli]

I have no issue trying to come up with a solution that doesn't involve VBA (whether I can or not is an entirely different issue), but can you give a little more reasoning as to why VBA is not an option? It just must help to understand the parameters of your requirements, is all... Also, you wrote it in a fairly large, emphatic font.

I do not have much experience with VBA at the moment.
As at any time I might be working with files from other people, I do not want to save them in a macro enabled workbook.

 

[Dan_W]

I'm glad it was helpful. It was an interesting question - I wasn't entirely sure at first how to go about writing the Named Range version, so it was an education for me too.
Thank you very much for responding to the question re: VBA - even though you certainly didn't need seeing as I had already managed to think of a non-VBA solution! I completely understand we're you're coming from, too.

As for working with files from other people point, sending XLSM files with VBA code in it can be a headache in the corporate environment, I know, and personally I will try and avoid making XLSM files at work as much as possible. But I just thought I'd make the point that when you want to run VBA code over worksheets/workbooks, the code itself doesn't actually need to be in that particular workbook. So unless you wanted your colleagues to be able to execute the VBA code themselves, you and your colleagues could still use/exchange XLSX files (for example), and then you could use the VBA code sitting on your computer and run it over those files. Just a thought.