Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

Alkanos: Jul 20, 2009; Ia! Ia! Cthulhu Fht-YAWN

Xenoborg posted:

Is ther a way to resolve all of the references in a formula? For budgeting, I have a cell of all my credit cards bills due that is =$F$19+$F$20+$F$21+$F$22. When all statements for a month are in, I paste values, and it becomes a flat -2500, but what I really want to see is for it to bring in all the refs and make it like =-1000-1000-200-300, which would make checking a lot easier when something is wonky.

This might not be helpful, but you can highlight portions of the formula and hit F9 to resolve them to test things out. So using your example, you can highlight $F$19 in the formula, hit F9, and it'll replace that with -1000. (You can escape out afterwards if you don't want to actually change the formula.) You can repeat this with each reference, or even for parts of the expression. Fair warning that this doesn't respect order of operations so be careful. For example, if your formula's =A1+B1*C1 and you highlight A1+B1 and hit F9, it'll just add those together, which won't be a useful answer.

It's mostly only good when you need to check something quick, Evaluate Formula is usually better.

# ¿ Oct 6, 2023 17:37

Adbot: ADBOT LOVES YOU

# ¿ May 3, 2024 11:22

Alkanos: Jul 20, 2009; Ia! Ia! Cthulhu Fht-YAWN

Super-NintendoUser posted:

Heyo, I have a head scratcher for excel, I'm trying to parse some data.

Basically I have two columns like this:
code:
Hostname	Version
DeviceA	1.1.100
DeviceA	1.2.100
DeviceA	1.1.100
DeviceB	1.1.100
DeviceB	1.1.100
DeviceC	1.2.100
DeviceD 1.1.100
I need to know the latest version of software on them. The problem is that a device may show up multiple times, and I need to report only the post recent so in the above example, I need to see this:
code:
DeviceA 1.2.100 
DeviceB  1.1.100
DeviceC 1.2.100
DeviceD 1.1.200
I can probably use some kind of query for it, but I'm having trouble parsing. There's half a million entries, and some devices show up 20-30 times, I sort of have a pivot table that gets me the data but not in a useful way.

I can get it into google sheets as well, I know more about that but it's natively in excel.

Any tips?

I'm going to assume two things. First, that you have a newer version of excel that can use the fancy array formulas they added in the last few years. And second that the version numbers in your second column are easily sortable ascending or descending.

Start with a list of the different devices (which if you don't already have it, you can use the UNIQUE function to build one). Lets put that in column D (so there's a gap). In column E use this formula:

code:

=INDEX(SORT(FILTER(B:B,A:A=D1),,-1),1)

D1 is the cell with the first device name (though it'll probably be D2 if you have headers). Whatever's behind the equal should in the row with formula so you can easily fill down.

Breaking it down, the FILTER function gets a list of every version number that matches the Device name in column D, the SORT function then puts them in Descending order (if you want Ascending change the -1 to just a 1), and the INDEX function returns whatever's at the top of that array.

It should looks something like this:

# ¿ Mar 12, 2024 01:35

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread