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

«‹›82 »

C-Euro: Mar 20, 2010; Soiled Meat

Zorak of Michigan posted:

If they were all in one sheet, this would be a good use case for a pivot table. Multiple sheets complicate it a lot. Are you wedded to that design?

Not particularly, and finally getting serious about a household budget feels like a good excuse to overhaul the system.

# ? Feb 8, 2024 22:34

Adbot: ADBOT LOVES YOU

# ? May 25, 2024 12:15

HootTheOwl: May 13, 2012; Hootin and shootin

Use a google form to submit expenses which then populate rows in a google sheet which are then used to make dashboards and charts in another.

# ? Feb 8, 2024 22:45

Super-NintendoUser: Jan 16, 2004; COWABUNGERDER COMPADRES; Soiled Meat

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
[code]

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
[code]

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?

# ? Mar 11, 2024 22:38

esquilax: Jan 3, 2003

I have a very kludgy solution using pivot tables, until someone else posts something better.

Create a new column, and translate your version number into a value, such that the most recent version is the highest number. We can call this column VersionValue.

Be make sure you include sufficient leading zeroes for this translation, in case your versions go up to 1.10.100 or something with a different number of digits.
e.g.
1.1.100 translates to 1001100
1.2.100 translates to 1002100

You can then make a pivot table using Device and Version as rows, and VersionValue as values. Set VersionValue to summarize values as "Max".

Then set a filter on the Version field, using the Top 1 by Max of VersionValues. This is part of the Filters->Top 10 right click menu.

If you then set the pivot table to "Show in Tabular Form" and hide any subtotals, it's easier to copy and paste out.

# ? Mar 11, 2024 22:59

Super-NintendoUser: Jan 16, 2004; COWABUNGERDER COMPADRES; Soiled Meat

esquilax posted:

I have a very kludgy solution using pivot tables, until someone else posts something better.

Create a new column, and translate your version number into a value, such that the most recent version is the highest number. We can call this column VersionValue.

Be make sure you include sufficient leading zeroes for this translation, in case your versions go up to 1.10.100 or something with a different number of digits.
e.g.
1.1.100 translates to 1001100
1.2.100 translates to 1002100

You can then make a pivot table using Device and Version as rows, and VersionValue as values. Set VersionValue to summarize values as "Max".

Then set a filter on the Version field, using the Top 1 by Max of VersionValues. This is part of the Filters->Top 10 right click menu.

If you then set the pivot table to "Show in Tabular Form" and hide any subtotals, it's easier to copy and paste out.

That's helpful, I can get a pivot chart to show what I need but unreadable, that probably would help, thanks!

# ? Mar 11, 2024 23:09

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

HootTheOwl: May 13, 2012; Hootin and shootin

If you have the ability to add columns then TEXTSPLIT or the text to columns tool will do it for you using the period as a dilimiter, then you can sort on these new columns my major, minor and then by patch

# ? Mar 12, 2024 01:39

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

Can someone explain to me how this is supposed to work like I'm five? I've got an excel file that's moving to OneDrive/Sharepoint and all I want is an easy replacement for ThisWorkbook.path in my VBA scripts that won't give me a URL instead of a local filepath. This seems like it should be easy-peasy (just import and go) which is making me feel real goddamn stupid about it.

# ? Mar 18, 2024 18:54

HootTheOwl: May 13, 2012; Hootin and shootin

Import the module.

Call the module dot the exposed method.

Also, I am confused: Won't shared drive or other web files always return a URL as their path? They don't exist on the local file system they're in the cloud?

# ? Mar 18, 2024 19:10

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

They also exist on my local file system, and when I have a script set to save and then open another file, I would like for it to do that the way it normally would for local files instead of opening a browser window and asking me to log in to access the web version of the text file I just generated. :argh:

# ? Mar 18, 2024 19:37

Count Thrashula: Jun 1, 2003; Death is nothing compared to vindication.; Buglord

I have a dumb niggling issue I can't figure out.

I have a lookup table in a second sheet, say Sheet2!A:A, that's full of short strings each on a single line, so for example:
1
5
10a
LCP
20z
Etc...

I have a column in my main worksheet that I want to check the value against the lookup table. If everything in the cell (delimited somehow, maybe comma) is in the lookup table, color green, else color red. So in the example above:
1,10a would color green
5,7,AT,10a would color red

I know I can split on commas, but iterating through a list like that is stumping me.

# ? Mar 20, 2024 08:15

HootTheOwl: May 13, 2012; Hootin and shootin

https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/using-for-eachnext-statements
Like this?

# ? Mar 20, 2024 08:46

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Count Thrashula posted:

I have a dumb niggling issue I can't figure out.

If you have excel through Office365 you can use TEXTSPLIT. I added the below formula to a second column in the table of test strings that returns true or false by counting the items in the string and comparing it to the count of items in the string that match any item in the lookup.

code:

=COUNTA(TEXTSPLIT([@[Test String]],","))=SUM(SIGN(COUNTIF(LookupTable[LookupColumn],TEXTSPLIT([@[Test String]],","))))

The SIGN(...) is to deal with duplicates in the lookup table.

# ? Mar 20, 2024 09:46

Count Thrashula: Jun 1, 2003; Death is nothing compared to vindication.; Buglord

TheLastManStanding posted:

code:
=COUNTA(TEXTSPLIT([@[Test String]],","))=SUM(SIGN(COUNTIF(LookupTable[LookupColumn],TEXTSPLIT([@[Test String]],","))))
The SIGN(...) is to deal with duplicates in the lookup table.

This worked perfectly! Thanks!

# ? Mar 20, 2024 13:43

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

I've got a base sheet full of tables with names like Water, Sewer, Power, Gas, etc. I'm working on a macro that will copy that sheet, rename it to the month and year, then rename every table to WaterApr, SewerApr, PowerApr, etc etc etc. I've mostly got it, except that when the sheet gets copied it renames all the tables to Water11 Sewer_32 or whatever, so I end up with Water11Apr (not useful). In theory I could work around this by stripping everything except alphanumeric characters, but I don't know the easiest way to do that.

# ? Apr 9, 2024 15:58

HootTheOwl: May 13, 2012; Hootin and shootin

post the macro code

# ? Apr 9, 2024 17:08

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

You probably don't need the whole thing and I hate posting it because I'm lazy and terrible at code, but:

code:

Sub newMonth()

    Dim wbYear
    Dim wbMonth As String
    Dim shortMonth As String
    Dim shortYear As String
    Dim tbl As ListObject
    
    wbYear = Application.InputBox("Enter the year of the workbook.")
    wbMonth = Application.InputBox("Enter the month of the workbook.")
    shortMonth = Left(wbMonth, 3)
    shortYear = Right(wbYear, 2)
    
    Sheets("Base").Copy Before:=Sheets(Sheets.Count - 1)
    Sheets("Base (2)").Select
    Sheets("Base (2)").Name = LCase(shortMonth) & "." & shortYear
    Range("J1").FormulaR1C1 = wbYear
    Range("J2").FormulaR1C1 = wbMonth
    
    
    For Each tbl In ActiveSheet.ListObjects
        tbl.Name = tbl.Name & shortMonth
    Next tbl

    'this bit's messed up but i'll fix it later
    'Range("I57").Select
    'ActiveWorkbook.Names.Add Name:="aprWages", RefersToR1C1:="=apr.24!R57C9"

End Sub

# ? Apr 10, 2024 00:41

Ninja.Bob: Mar 31, 2005

It's probably easiest to use a regular expression replace. You'll need to add a reference to 'Microsoft VBScript Regular Expressions 5.5' in the vba editor menu Tools > References.

Visual Basic .NET code:

Dim reg As New RegExp
reg.Pattern = "[0-9]+$"

For Each tbl In ActiveSheet.ListObjects
    tbl.Name = reg.Replace(tbl.Name, "") & shortMonth
Next tbl

# ? Apr 10, 2024 09:29

DRINK ME: Jul 31, 2006; i cant fix avs like this because idk the bbcode - HTML IS BS MAN

I don�t have Excel in front of me but google says if you have consistent cell references you can do it like this instead of finding table names / knowing what they�re called now.

So if Water table is always in a1

code:

With ActiveSheet
    .Range("a1").ListObject.Name = "Water" & shortmonth
End With

# ? Apr 10, 2024 11:59

Everett False: Sep 28, 2006; Mopsy, I'm starting to question your medical credentials.

Ninja.Bob posted:

It's probably easiest to use a regular expression replace. You'll need to add a reference to 'Microsoft VBScript Regular Expressions 5.5' in the vba editor menu Tools > References.

That worked! I only modified the expression a little:

code:

reg.Pattern = "[^a-z]+"

Unfortunately the tables sometimes end up moving around if they change in size so cell references wouldn't have worked, this seems to work perfect though. :3:

This thread is such a lifesaver.

# ? Apr 10, 2024 23:12

Quixzlizx: Jan 7, 2007

Is there any way to make Google Sheets "remember" the last sort done on a sheet like the Excel behavior? The frustration of this not happening is multiplied by Sheets' UI being worse/slower than Excel's, with no keyboard shortcuts listed, no quick command bar, and having to add each column after the first in the sort interface instead of defaulting to having 3 present.

I know the SORT function exists, but I don't really want to need to duplicate the sheet and have to unhide/rehide the data source sheet every time just to work around this.

Edit: My solution ended up being to create a macro, since I always want to sort the sheet by the same criteria.

Quixzlizx fucked around with this message at 22:12 on Apr 27, 2024

# ? Apr 27, 2024 21:50

Combat Pretzel: Jun 23, 2004; No, seriously... what kurds?!

loving hell, Microsoft, add loving REGEXP formulas finally.

# ? May 14, 2024 21:54

Count Thrashula: Jun 1, 2003; Death is nothing compared to vindication.; Buglord

Here's a dumb little thing I can't figure out.

I have a list of a few thousand dates. I'd like to get a count of how many dates are in each month (how many Jan '15, how many Feb '15, etc., through today). I could easily do it by typing out a couple hundred rows of month/year combos and using COUNTIF, but is there an easier solution I'm blanking on?

# ? May 14, 2024 21:58

HootTheOwl: May 13, 2012; Hootin and shootin

Make a chart or pivot table

# ? May 14, 2024 22:42

Strong Sauce: Jul 2, 2003; You know I am not really your father.

i'm on my lunch break so this is a little janky but seems to work.

code:

=LET(
  start_date, "2023-08-01",
  range, $A$2:$A,
  gen_key, LAMBDA(val, MONTH(val)&"_"&YEAR(val)),
  date_list, MAP(range, gen_key),
  MAP(
   SEQUENCE(DATEDIF(DATEVALUE(start_date), DATEVALUE(NOW()), "M"), 1, 0),
   LAMBDA(d, COUNTIF(date_list, gen_key(EDATE(start_date, d))))
  )
)

where your list of dates you want counted is defined in "range" and the "start_date" is the earliest date.

# ? May 14, 2024 22:57

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

Count Thrashula posted:

Here's a dumb little thing I can't figure out.

I have a list of a few thousand dates. I'd like to get a count of how many dates are in each month (how many Jan '15, how many Feb '15, etc., through today). I could easily do it by typing out a couple hundred rows of month/year combos and using COUNTIF, but is there an easier solution I'm blanking on?

If you don't want to use vba, can you use helper columns? In the next column fill down a formula like =TEXT(A1,"MM-YYYY") to convert it into the month/year format you need. Next column use =SORT(UNIQUE(B:B)) to get a list of each instance. Last column is just your countif you mentioned: =COUNTIF(B:B,C1) and fill down to match everything in column C.

I'm doing this from memory so those functions might be formatted wrong, or be backwards somewhere...

# ? May 15, 2024 01:45

TheLastManStanding: Jan 14, 2008; Mash Buttons!

Count Thrashula posted:

I have a list of a few thousand dates. I'd like to get a count of how many dates are in each month (how many Jan '15, how many Feb '15, etc., through today). I could easily do it by typing out a couple hundred rows of month/year combos and using COUNTIF, but is there an easier solution I'm blanking on?

Pivot Table:

# ? May 15, 2024 06:27

Count Thrashula: Jun 1, 2003; Death is nothing compared to vindication.; Buglord

Alkanos posted:

If you don't want to use vba, can you use helper columns? In the next column fill down a formula like =TEXT(A1,"MM-YYYY") to convert it into the month/year format you need. Next column use =SORT(UNIQUE(B:B)) to get a list of each instance. Last column is just your countif you mentioned: =COUNTIF(B:B,C1) and fill down to match everything in column C.

I'm doing this from memory so those functions might be formatted wrong, or be backwards somewhere...

Admittedly a pivot table would have been quickest, but this is the solution that worked the best for my brain. Thanks!

# ? May 15, 2024 13:21

Big Bad Beetleborg: Apr 8, 2007; Things may come to those who wait...but only the things left by those who hustle.

Trying to help a dude at work who has been running himself ragged doing something manually multiple times per month for years, which I'm pretty sure can be simplified significantly.

Gets a list of servers as below
Server - OS
FamilyA001 - Windows
FamilyA002 - Windows
FamilyA003 - Linux
FamilyB001 - Linux
FamilyB002 - Windows
FamilyB003 - Linux

He gets given a list of the server families eg FamilyA, FamilyB etc and needs to iterate through a list of thousands to identify and collate all of servers in that family - ideally only the Windows ones but can either use a filter on the results or pre-exclude the non-Windows ones.
I can get partway there with the =filter formula, but can't deal with the spillover. Is there a bit of VBA collate the multiple filter queries into one and dump it into a specific column, one per line?

Big Bad Beetleborg fucked around with this message at 04:24 on May 20, 2024

# ? May 20, 2024 04:08

nielsm: Jun 1, 2009

Why are you doing that in Excel in the first place?

# ? May 20, 2024 04:45

Big Bad Beetleborg: Apr 8, 2007; Things may come to those who wait...but only the things left by those who hustle.

nielsm posted:

Why are you doing that in Excel in the first place?

I dunno, I'm just trying to help the dude so he doesn't have a coronary. Seems like it should be in the asset records but for some reason he has to work with lovely exports instead.

# ? May 20, 2024 04:55

DRINK ME: Jul 31, 2006; i cant fix avs like this because idk the bbcode - HTML IS BS MAN

I don�t quite get what you mean. Is the list in that format of �name� dash �family� and you just want to group by family?

I would probably just use a helper column, something like
=RIGHT(A2, LEN(A2) - FIND("-", A2) - 1))
Then you have a column to filter on.

If you want to do it with filters to create new lists, I think this should work
=FILTER(A2:A200, RIGHT(A2:A200, LEN(A2:A200) - FIND("-", A2:A200) - 1)="Windows")

*** It will depend on consistency of the formats. If some are SpaceDashSpace, others are just Dash, and so on, you need to account for that in your formula.

# ? May 20, 2024 04:56

DRINK ME: Jul 31, 2006; i cant fix avs like this because idk the bbcode - HTML IS BS MAN

You know for the filter I was still thinking about the format, you could make it easier with this (if this is what you want). No worries about the Spaces and Dashes then

=FILTER( A2:A200, IFERROR( SEARCH( "windows", A2:A200), 0) > 0)

# ? May 20, 2024 05:11

Big Bad Beetleborg: Apr 8, 2007; Things may come to those who wait...but only the things left by those who hustle.

Aah no, those are two separate columns - Family, and OS - I just didn't know how to represent that quickly in bbcode.

He gets given a dump of thousands of server names, and an arbitrarily long list of families. He currently iterates through line by line (presumably after sorting but you never know) and identifies all the Windows servers that match the list of families, which he collates and manually inputs one by one into another system.

I'm hoping to get to a point where he can paste his list into col A, the servers into sheet 2, then press a button and col B spits out all the results he needs.

# ? May 20, 2024 05:22

nielsm: Jun 1, 2009

Yeah that's a five line PowerShell script, if you have the input as plain text files/CSV.

Otherwise I'd suggest using a table with filter on the Category column, especially if the list of categories to filter out is short.

# ? May 20, 2024 05:42

HootTheOwl: May 13, 2012; Hootin and shootin

Big Bad Beetleborg posted:

Trying to help a dude at work who has been running himself ragged doing something manually multiple times per month for years, which I'm pretty sure can be simplified significantly.

Gets a list of servers as below
Server - OS
FamilyA001 - Windows
FamilyA002 - Windows
FamilyA003 - Linux
FamilyB001 - Linux
FamilyB002 - Windows
FamilyB003 - Linux

He gets given a list of the server families eg FamilyA, FamilyB etc and needs to iterate through a list of thousands to identify and collate all of servers in that family - ideally only the Windows ones but can either use a filter on the results or pre-exclude the non-Windows ones.
I can get partway there with the =filter formula, but can't deal with the spillover. Is there a bit of VBA collate the multiple filter queries into one and dump it into a specific column, one per line?

Pivot table? Make sure row 1 has column labels

HootTheOwl fucked around with this message at 12:50 on May 20, 2024

# ? May 20, 2024 12:48

Adbot: ADBOT LOVES YOU

# ? May 25, 2024 12:15

Quixzlizx: Jan 7, 2007

Anyone know why an Excel line chart would have a random 0 value when the correct value for that x-axis point is being plotted correctly?

300.........x (correct)
200...............................x (correct)
100
0.............x (wrong)
...........5/23/24........5/24/24

When I hover over each of the data points, the two correct ones are properly displaying "Point '5/23/24'" and "Point '5/24/24'", while the 0 is just displaying "Point 3."

There are a bunch of columns in the data range after these two columns that are currently blank (or have formulas that resolve to "" with legitimately empty column headers), but the chart is seemingly correctly ignoring them.

# ? May 24, 2024 16:49

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

«‹›82 »