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

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

I have a quick question about Excel, I'm tasked with reporting from CRM. I can export a list of all our tickets, with the owner on them. So I have an excel sheet that looks like this:

I can easily use "CountIf()" and make a pivot chart that outputs a bar graph with each persons total tickets. But I have a more interesting problem. Half our team is in IL and half is in US. We compete on handling ticket totals. The list of people both US and IL, but there's on specific data in the table to sort out who is who. I'd like a formula that will look at the list and check and see if a person is US is IL based on their name, and then use that data to output a pie chart that shows the split. I'm messing around with "Countifs()" but it's not too agreeable. Any ideas?

# ¿ Nov 15, 2013 17:40

Adbot: ADBOT LOVES YOU

# ¿ Apr 30, 2024 09:22

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

Old James posted:

What is the criteria to flag US vs India? If US owners say "John Smith-US" and India owners say "Vijay Singh-IN" then you can use wildcards in your countif (provided you are using Excel2007 or newer).

=countif(C:C,"*-US")
=countif(C:C,"*-IN")

That's a good suggestion, but there's nothing in the names that specifies. Each guy is just named with their name, I'd need to write a query that says "all these guys are US and all these guys are IL"

I did this for now, these four guys are US, everyone else is Israel:

=IF(OR(C2="Andrew",C2="Bimalkumar",C2="Doug",C2="Eric"),"US","IL")

# ¿ Nov 15, 2013 18:30

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

DukAmok posted:

That sounds manageable because there's 4 names, but you can build a Vlookup table somewhere as well, with each person's name next to their country.

Andrew US
Bimalkumar US
Doug US
Eric US
Joe IL

Then add a column to your data that fills down with =Vlookup(Name cell like A4, your lookup table like J:K, the column to look at like 2, and then false).

I paraphrased, but there's about fifty names. Also, I can't edit the source sheet at all, since it's updated from CRM, any changes get overwritten when it's refreshed. Also, if I scrap the data from another sheet, the list changes in length dynamically, so I'd need to accommodate that some how and not have a pivot chart that is not reading far enough or has a bunch of null entries at the end.

# ¿ Nov 15, 2013 19:31

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

Old James posted:

Make a list in another workbook that you've saved and then write a countif there which points to the source data. Then once you have the calculations, break the link so you have the raw value counts.

Trying to match 50 names in a function is not reasonable, you will hate yourself. But if you still want to give that a try, take the group that has the shortest number of names and do something like the following.

=if(or(A1="Jim",A1="John",A1="Jacob"),"US","IL")

I ended up talking to our CRM admin, and getting them to add a field for Location. Now it's all integrated. What I'm stuck on now is the ticketing reports won't include a name of a guy that does no tickets, since the list is generated from "tickets in the past week". The pivot chart need to show a goose egg for the guys that don't do anything. As it is not they are just omitted.

# ¿ Nov 15, 2013 23:13

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

Splendiferous posted:

Another question. I have two timestamps:

3/30/2012 9:08

and

3/29/2012 20:04

One indicates the "before" point in time, and one is the "after." I have thousands of these pairings. Is there any way I could write a formula in Excel to calculate the number of seconds between time A and time B? This would be easy if all of my pairings happened in the same month, year, etc, but they don't.

Thanks!

Do you want to compare the real time total seconds? Or the seconds between the same time each day?

EDIT: I wrote this to convert each time to EPOCH time and then subtract them. And then take the absolute value of the result.

=ABS(((A1-25569)*86400)-((B1-25569)*86400))

Super-NintendoUser fucked around with this message at 20:19 on Nov 21, 2013

# ¿ Nov 21, 2013 20:14

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

Adbot: ADBOT LOVES YOU

# ¿ Apr 30, 2024 09:22

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

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