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

# ? Apr 20, 2024 11:44

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

Adbot: ADBOT LOVES YOU

# ? Apr 20, 2024 11:44

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

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

«‹›82 »