Ah, gotcha.
Yeah, for about 10 years I was the Excel guru in my office. Then I changed jobs where everyone knows how to make Access and Excel do everything but make coffee. I’ve learned more in 2 years than I have in the 10 years before that. It’s such a nice luxury to have someone to confer with when you hit a snag, rather than slogging through Google.
Lotus 1-2-3’s macro language had a @BREW function. I miss it. Best coffee ever.
VLookup sucks. Don’t use it. The columns in the table you’re pulling from have to be in the right order, it’s slow, etc…
Try a combination of INDEX and MATCH instead. Like this:
=INDEX({column you want to pull data from},MATCH({value you want to look up and match},{array where your match will be found}, 0)
This way, the columns in the table you’re pulling from can be arranged in any order. I’ve also found it to be faster than VLOOKUP.
So, if you’re on Sheet1, and you want to pull data from column A in Sheet3 based on a match in column E in Sheet1 and column C in Sheet3:
=INDEX(Sheet2!A:A,MATCH(E1,Sheet3!C:C,0)
should do it. The “0” gets you an exact match, and I’ve never had the occasion to use 1 or -1 there.
Actually, that formula should be:
=INDEX(Sheet3!A:A,MATCH(E1,Sheet3!C:C,0)
I edited twice (because I originally had column D:D in the formula) and I still messed it up.
That should give you an idea of how my regular Excel sessions usually go. :smack:
Just had to thank the OP for a good laugh reading the title. It’s a lot better if you skip the parenthetical part at the end! 
I use a lot of applications, but none is anywhere near as annoying as Excel. I must be designed to be so!!
Yup, it’s annoying.
To me the most annoying part is that after saving it in one format it asks to save again (before quitting) even though I’ve made NO changes. The only justification for this is “Well, it shouldn’t do that, but blah blah blah blah and it would be more work than you’d guess because blah blah blah.”
Sad to say, I’ve been on both sides of that justification. It’s worse to be the one saying it than to be the one hearing it! But it’s a sad fact of life for huge programs that have grown by accretion over many years by thousands of programmers, rather than one designed by a nice tight team of clever people in a year or two.
If you want to make Excel less annoying . . . try Adobe Numbers. You’ll be begging for Excel. I’ve been using it for 18 months, thinking that in time I’ll get used to it . . . but I’m constantly finding more reasons to hate it. 18 months are long enough. I’m switching back to Excel. I guess Adobe’s team isn’t "tight or “clever” enough.
Open it FROM inside Excel, instead of double clicking or using the right click menu “open with excel”.
Popup saying “delimited fields”: next.
Popup saying “coma delimited” (or tab or whatever): make sure the separator is the right one, next.
In the list of fields, select all of them (you can move all the way to the end of the list and shif+click on the last one for this) and say they’re “text”.
It’s one of the things I’ve had to tell people so many times in the last 12 years I wish I could have it macroed (the telling, not the doing). Right now I’m trying to badger a team of 12 programmers into doing it, but apparently they think it doesn’t apply to them; they keep losing the leading zeroes and the periods, and then the key users say “but it’s not the same!”. We have some key users who would much rather use full mechanical comparisons than the partial visual ones we’re having to do (at least one will do the mechanical and then just run through the screenshots), that requires the leading zeroes and periods and commas to stay exactly the same, so text format rather than general.
RitterSport
A possible macro with [Application.DisplayAlerts = False] was the first thing that popped into my head when I read the OP.
That little statement has saved me many headaches when using Excel to manipulate other files.
I like your code. I may use it in my “AlsoLoad.xlsm” file with one exception.
It needs the line
’ **** Rittersport to the Rescue
added somewhere near the top of the code.
Bubbadog
Side question: Is there a decent alternative to Excel for making and editing simple CSV files? These days I’m often manually entering small-medium datasets into Excel, and saving as a CSV so I can do analysis with R. That means I’ve got to suffer the same constant-but-minor inconveniences described in this thread, while using absolutely none of the features of Excel.
Google Sheets or OpenOffice Calc
CSV files are just text files – any good text editor will work for that.
I’ve just now myself been looking for a way to extract selected information from Excel files, and the way I’ve been trying out is by converting the files to csv, then extracting the data from them.
What the heck, thinks I, I’ve got a desktop with Ubuntu/Linux, and I’d like to keep up on the latest, so I’ll see if I can extract the info by learning Python and coding up a simple script file.
Yes, I’ve run into the OP’s situation, and I find it fairly annoying myself. No, I don’t suppose there’s much to be done about it.
After I worked up a reasonably simple Python script to get the first part of what I need, I looked at the documentation again and I now see that Python has some kind of library specifically for reading and handling csv files.
It looks like there’s a version of Python that can be installed on Windows machines, and some futher utility that can turn Python files into .exe programs, but I’ve haven’t really tried that stuff out.
I doubt that this would be of much easy use to the OP, but it’s out there.
Wouldn’t excels built in programmability be better for stuff like that?
Not sure. The various xls files I’m data mining do not themselves have the macros or coding within themselves for extracting the info I’m looking for, so under any circumstance I’d somehow need to load each one into a spreadsheet that had the coding. I think.
(And no, I’m not a great fan of VBA.)