3,544 reputation
103185
bio website security.stackexchange.com
location Florida
age 31
visits member for 3 years, 6 months
seen Apr 3 at 15:07

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


Sep
20
comment Generate a comma-separated list of cell contents, excluding blanks
Does the Portuguese version of Excel use semicolons to separate function arguments? It's usually a comma for English.
Sep
20
comment Generate a comma-separated list of cell contents, excluding blanks
The reason I said "avoid nested IFs" was because my initial thought was to do some form of =IF(AND(B2="",C2="",D2=""),A2,IF(AND(C2="",D2=""),CONCATENATE(A2,", ",B2) ... But, since you can nest an IF inside of CONCATENATE, it turns out I only really need one IF statement per cell to be concatenated - still a little less than ideal, as I'd prefer some form of FOR loop or an array function, but it works very nicely. See my comment to @Scott's answer.
Sep
20
comment Generate a comma-separated list of cell contents, excluding blanks
@Dane I'm not trying to avoid "nested anything", but I do want to avoid huge amounts of multi-layered nesting especially when nesting within the same function. The sheet I'm working with right now only has 4 cells that need to be concatenated. The first cell is mandatory - so if that's empty there's something that needs fixing to begin with - but the other three may be filled or blank.
Sep
20
comment Generate a comma-separated list of cell contents, excluding blanks
Eww. Nested SUBSTITUTEs really aren't all that much better than nested IFs.
Sep
20
comment Generate a comma-separated list of cell contents, excluding blanks
Note: While this solution does still require IF statements, please note that my original intent was to avoid nested IFs. Here, the IFs don't need to be nested, and there's no real repetition involved - there's only one IF statement for each cell that needs to be concatenated. So, unless there's a better solution that involves some sort of loop or array, this is definitely an acceptable answer.
Sep
20
comment Generate a comma-separated list of cell contents, excluding blanks
The nice thing about "use some 'helper' cells" solutions is that usually the formulas for the 'helper cells' can be written right into the formula for the target cell. I took your idea and integrated it all into one formula. =CONCATENATE(S2,IF(U2="","",", "&U2),IF(V2="","",", "&V2),IF(W2="","",", "&W2)) One thing I forgot to mention is that the first cell in my list (S2 in this example) is going to be mandatory. So, by accepting that the first cell will always be filled, we can move the commas to the front of each subsequent string and avoid having an excess comma at the end.
Sep
18
comment Display Blank when Referencing Blank Cell in Excel 2010
I did post a related question awhile back and the only worthwhile answer that's different from what's here involved a VBA script.
Sep
18
comment Display Blank when Referencing Blank Cell in Excel 2010
That's more or less the same thing. Though it does resolve some of the cumbersomeness of having to troubleshoot two instances of one formula within one cell, it also complicates things a bit by adding another (otherwise unnecessary) cell or group of cells to the mix. Unfortunately, I suppose there really isn't another "nice and clean" answer for this, though.
Sep
18
comment Display Blank when Referencing Blank Cell in Excel 2010
The first option is sub-optimal, as it requires entering (or copying) your formula twice within the cell. It can be especially nasty when you have a really long formula, and then need to edit (or worse, debug) that formula later. The second option doesn't seem to be working for me, but maybe I'm doing it wrong. The third is definitely not ideal since the scope of its effect is much broader than one might truly desire. Are there really no better options?
Sep
11
comment What's wrong with my local network and router?
This question is missing some critical details like a thorough description of the layout of your network and particulars about where the system from which the ARP table was pulled is located in the network.
Sep
8
comment How can I easily split a CSV into two Excel worksheets with PowerShell?
Question I've been meaning to ask: Is $excel.visibile = $true really necessary? I've seen it in just about every sample script I've found online but it would seem like it should only be needed if you actually want to see Excel while the script is running - correct?
Sep
4
comment What is the exact use of a MAC address?
Worth noting about Wi-Fi MACs: While they are generally unique and can be used to identify devices, they're easy to spoof and sent in the clear over the air. If there is no other encryption/authentication used on the connection, or if the other encryption/authentication mechanism(s) are weak (e.g.: WEP), it is very trivial for an attacker to impersonate an authorized device and join the network.
Sep
4
comment Access the user's selection from a previous cmdlet
Interestingly, you can do without the $processresult variable entirely. if ((Stop-Process -Name notepad -Confirm -Passthru) -ne $null) {echo "R.I.P. Notepad"} else {echo "Notepad lives"}
Sep
4
comment Access the user's selection from a previous cmdlet
For my particular case, I don't need the user to supply a reason or the process. I have a particular application that is not multi-user friendly. So, if someone is already logged on to the server and running the application on their session I will not be able to run it on mine. I'm trying to automate the task of identifying who's running the application and (optionally) terminating their instance. If I choose to terminate it, I want the script to run the application afterward. If I choose not to terminate, the script should exit.
Sep
4
comment Access the user's selection from a previous cmdlet
Scratch the bit about $ErrorActionPreference. That applies to a different part of the script I'm writing. $Notepad = Get-Process notepad; while ($Notepad -ne $null) {$Notepad = Get-Process notepad}; echo 'Notepad is finally dead!' will announce an error when Notepad is finally dead unless you change $ErrorActionPreference.
Sep
4
comment Access the user's selection from a previous cmdlet
It's still redundant of the functionality provided by Stop-Process -Name notepad -Confirm. I'd rather have confirmation that I can't access the user input for Stop-Process before I fall back to writing my own confirmation prompt. (Even then, for this particular use case, I personally prefer @RoryMcCune's workaround.)
Sep
4
comment Access the user's selection from a previous cmdlet
Alternative condition for the if: $processresult -ne $null. This will work for my particular use case. Also, you might want to temporarily set $ErrorActionPreference = 'SilentlyContinue' to keep PowerShell from barfing at the user when they pick "No". It would still be great if there's a method that works for the more general case, or if someone can verify that there isn't.
Sep
4
comment Find and return sheet name
I'm not familiar with looking up strings across multiple sheets. However, I'm aware there are some functions that will return a cell's location reference. Perhaps if you can provide the formula you're starting from, I could mash it together with what I know to give you something workable?
Sep
4
comment What is the name of the WiFi card size in the UX31E?
@Ramhound Semi-true. It depends on the scope we're using to define "Mini PCI-E". The connector and bus probably fit the standard, and so the card is probably cross-compatible with other Mini PCI-E interfaces. The physical form factor on the other hand is not, which means that the motherboard/case is not designed to accept other Mini PCI-E cards.
Aug
28
comment Remove duplicate entries, keeping latest only
Awarded bounty here for inventiveness, transparency of method (i.e.: I don't need a third-party reference to tell me this will give reliable results) and for keeping the solution self-contained in one workbook. This does not scale up very well though, as it is very processor-intensive.