3,980 reputation
134192
bio website security.stackexchange.com
location Florida
age 32
visits member for 3 years, 10 months
seen 7 hours ago

I'm not the droid you're looking for.


1d
comment windows cmd - return first 3 files of a search
Also, it seems your screenshot represents output of dir /o-d - not dir /od. Is that correct?
1d
comment windows cmd - return first 3 files of a search
FTR: The "other means" is really the right tool for the job in Windows 7 and later. PowerShell brings in a lot of functionality that CMD just wasn't built for. If your concern is about compatibility with execution policies or limited user permissions across different systems, you can write a helper batch file to bypass most of those issues.
2d
comment How do I find the next smallest/largest value without sorting?
@cybernard Could you post your helper column solution as an answer? Perhaps include a VBScript solution if you have one handy, as an alternative. Either way, you've been a great help and I want to give you votes.
Jul
21
comment How do I find the next smallest/largest value without sorting?
@cybernard I could get to them over the network, but they're locked - can't even read them - when the application is running. And there are over a dozen. Making all of them talk to one SQL database is in the near-future to-do list.
Jul
20
comment How do I find the next smallest/largest value without sorting?
@cybernard Want a good laugh? This is to support a spreadsheet that's manually populated based on data from several instances of another application that are using separate Access databases. If it was using a SQL database, and all instances used the same database, I could query the whole thing at once - while the application instances were still live - and be done. So, I'm using the wrong tool to support another wrong tool which is itself using the wrong tool.
Jul
20
comment How do I find the next smallest/largest value without sorting?
FTR: The dates & times are indeed in the same cell, stored in Excel's date-time format. So 7/12/2014 17:00 is effectively 41832.70833.
Jul
20
comment How do I find the next smallest/largest value without sorting?
@cybernard Ah, helper columns. Not ideal, but it works.
Jul
20
comment incorrect IF formula evaluation
@Peon = should work fine for textual comparisons just as the OP has written it, so long as SELL_INVESTMENT is a named reference to an individual cell and there are no spaces or nonprintable characters in the "YES" that they're looking for. It's not even case-sensitive.
Jul
20
comment How do I find the next smallest/largest value without sorting?
After some searching, it seems the RANK.EQ component is part of Data Analysis Expressions in PowerPivot - a feature not available in vanilla Excel 2010 (and with several additional prerequisites), and only available to certain editions of 2013. This won't work for me.
Jul
20
comment How do I find the next smallest/largest value without sorting?
Can you describe the syntax some more? I'm having a hard time understanding it, based on the information in Excel's help file for SMALL.
Jul
20
comment How do I find the next smallest/largest value without sorting?
@cybernard The list will contain past events. So, wouldn't that instead return "the first time something happened"? This is why I'm looking for something that will do next highest/lowest matching.
Jul
20
comment How do I find the next smallest/largest value without sorting?
@cybernard Example: A bunch of things are supposed to happen exactly an hour from now, and are listed on one sheet along with a whole bunch of other events and times which might not necessarily be in a given order. My other sheet has a field for the "next time something's going to happen" - it doesn't really care what's specifically going to happen, or how many things are going to happen at the same time. It just wants to know when the next time is (in this case, exactly an hour from now) that something, anything, is going to happen.
Jul
20
comment How do I find the next smallest/largest value without sorting?
@cybernard Thanks for the info. The ranged being searched will have duplicates, but this is inconsequential to my purposes for now. Right now, I'm trying to search a non-ordered list (or, at least, one which may end up ordered by something other than time) of past and upcoming events and return the nearest past and future times in that list relative to the present.
Jul
20
comment How do I find the next smallest/largest value without sorting?
@cybernard I can't count how many times I've been told "Excel is not a database" around here. Unfortunately, it is indeed what I'm stuck with - not only by limitations of my own knowledge, but also the same limitations existing in the knowledge of the peers who need to be able to work with the data similarly. Plus, getting funding to support a proper database, and build an application on top of it, isn't easy.
Jul
20
comment How do I find the next smallest/largest value without sorting?
@cybernard If that's really the only or best way to do it, I'm interested. If there's a relatively simple way to do it with native Excel functions though, I'd prefer that. Third-party tools are still flat out of bounds.
Jul
20
comment In Excel I need to find data on sheet3 from data on sheet1
Oh, wait... I'm making this too hard. HLOOKUP will work fine. One sec. Previous statement about uniqueness of lookup values still applies. This also assumes that all of your numbers in the "X" columns are perfectly sequential starting at row 3 and always sorted ascending.
Jul
20
comment In Excel I need to find data on sheet3 from data on sheet1
@user3857791 What you need are functions that will return the cell references (I'll have to look them up) for the search result in 'Sheet3'!1:1 that begins with CONCATENATE('SheetA'!W42,":"), and the one in 'Sheet3'!A:A that exactly matches 'SheetA'!CD42. Then, use INDIRECT and CONCATENATE to build a reference to the cell that has the column of the first match and the row of the second. The one thing you must check though, is to be sure that all potentially matching values in 'Sheet3'!A:A and 'Sheet3'!1:1 are unique.
Jul
20
comment In Excel I need to find data on sheet3 from data on sheet1
@user3857791 No, I'd leave it as is. Now that I've seen it with the pictures embedded inline, and proper cell references spelled out, I think I've got it figured out.
Jul
20
comment In Excel I need to find data on sheet3 from data on sheet1
Ok. Did what I could to fix it. Still confused as hell. Also, Excel is not a database!
Jul
20
comment In Excel I need to find data on sheet3 from data on sheet1
@Tyson I think I'm going to try rewriting this with Excel's proper cell reference syntax, and encapsulating all cell contents in quotes. Maybe that will help. I'm twenty kinds of confused too, though.