The Swamp

Creating a table in Word where you should have just done an Excel sheet should be punishable by a kick to the genitals.

Johnny Carwash wrote: Tue Aug 06, 2019 8:27 am Creating a table in Word where you should have just done an Excel sheet should be punishable by a kick to the genitals.

Sure...but a fucking letter in excel?

Does anybody know anything about Google sheets? Like what happens if there's a public sheet that 30 or so people add rows to and you go in to make edits to all of your entries, so you sort it to isolate your rows and make changes to each row, and then remove the sort but then after that everybody's entries are all dicked up and not matching the row that you sorted because you're old and now realize that Google sheets doesn't work like Excel. Does my sort fuckery reflect in everyone else's views, or does only data entry record but not formatting?

Thank you!

Jerloma wrote: Thu Sep 07, 2023 9:50 pm Does anybody know anything about Google sheets? Like what happens if there's a public sheet that 30 or so people add rows to and you go in to make edits to all of your entries, so you sort it to isolate your rows and make changes to each row, and then remove the sort but then after that everybody's entries are all dicked up and not matching the row that you sorted because you're old and now realize that Google sheets doesn't work like Excel. Does my sort fuckery reflect in everyone else's views, or does only data entry record but not formatting?

Thank you!

It messes everyone's view up.

We us google Sheets for our IR/RFI Log on construction projects and if someone sorts the sheet by discipline (eg MEP engineer/structural engineer/architect) it's changes the view for everyone, annoys me intensely if they don't return sheet to the original format (sorry).

fucks up formulas too

I'm trying to generate a random sample from a spreadsheet. So I insert a column and then I add the function to the first cell below the header in row 2. Hit enter, and no number is generated; it still just has =RAND() there. Anyone know how to troubleshoot that?

Hitting f9 forces a calculation but not sure what’s your problem.

This was suggested to me on YouTube:



I had no idea this was a thing.

Excel wizards, question:

The Air Force uses Air Force Specialty codes to define jobs in the military. When I pull rosters out of system they come as Excel files.

However, AFSCs like 3E231 or 3E071 default to looking like "3E+231" or "3E+71."

How the hell do I just format the cell to not recognize that "equation" crap? Either using general or text doesn't work. And removing all formats still uses the "+."

So frustrating, but I know it's fixable because I've seen other Excel spreadsheets without the issue. I just don't know how to do it.

Put a single quote in front

If I'm understanding right - ' (apostrophe) in front of what you type in the box should make it recognize it as not an equation.

e: Ryan beat me to it.

Success. Thank you.

So this is probably the most basic question one could ever ask, but honestly I don't use Excel much in my job. I need to know which customers in column B do not exist in column A. How would this dummy go about that?

Assuming all the names are exactly the same (so not "Houston Cheaters", "Hou Cheaters", "Houston", "Cheaters", etc), just add another column next to B where its =vlookup(B1,A:A,1,false) ... that checks column A to see if the name in B1 exists. Drag that formula down as far as you need in column B.

Vlookup is definitely for people who don't use excel that often.

ETA: So the screenshot does help.

Appreciate the help and laughs.

How do I sum up a cell so that the formula shows the column header name? So that instead of this...

=SUM(J11,M11,O11,V11,X11,Z11,AF11,AG11)

This...

=[@5Avoid]+[@14Places]+[@21Talking]+[@28Remind]+[@30Event]

Like you want a word string and not numbers?

=J11&M11&O11&V11&X11&Z11&AF11&AG11

And if you want a space in between, its =J11&" "&M11 ...

If I have a spreadsheet with several columns, how do I get a collapsed count for the header column in which I have it sorted? I've tried the Count and Subtotal functions but those seem to be for cells containing numbers. I just need to get a subtotal for each cell of text in the sorted column.

Not sure if I really get it, but COUNTIF or a motherfuckin' tableau de pivotte?

Yeah I need a screenshot or something to understand this one.

So I have a spreadsheet with a bunch of columns with client data, demographics, dates, etc. Each row accounts for an individual service provided and recorded in the database. I have it sorted by the Service Type column, and what I want is for the spreadsheet to count the number of each service type, so that at every change in that sorted column, it provides the subtotal for that service type. For example:

Administration - 146
Case Management - 1,365
Intake - 89
Treatment Plan - 412

Now I can just individually filter each service type and get the count that way, but I'm certain there's a function for this and I can't figure it out.

If I'm understanding it right that those 4 categories above are a column and then amount is a column, it sounds like you want to use SUMIFS.

mister d wrote: Fri Apr 05, 2024 1:01 pm If I'm understanding it right that those 4 categories above are a column and then amount is a column, it sounds like you want to use SUMIFS.

No there is no amount column. Those are just some of the values under the "Service Type" column. So if it look like this...

Service Type (Column Header)
Administration
Administration
Case Management
Case Management
Case Management
Case Management
Case Management
Intake
Intake
Intake
Treatment Plan
Treatment Plan
Treatment Plan
Treatment Plan

I want it to do this:

Service Type (Column Header)
Administration
Administration
Count: 2
Case Management
Case Management
Case Management
Case Management
Case Management
Count: 5
Intake
Intake
Intake
Count: 3
Treatment Plan
Treatment Plan
Treatment Plan
Treatment Plan
Count: 4

Feels like a pivot table scenario

Sounds like you could have sorted and hand counted by this time. I don't mean that bad, and I love it when a spreadsheet works, but unless you're going to be doing this all the time and therefore need the formulas to work it's a boondoggle.

And doesn't subtotals let you use the count function and not just sum?

You want COUNTIF. If that data is in column A, its ... =COUNTIF(A:A,"Administration")

Fully team pivot now i think

Yeah, I don't like the count in the same column.

If I have two date columns like this...
Is there a formula I can put into the next column for calculating the amount of days in between the admit and discharge columns to calculate length of stay?

I had to look this one up because I’d rather count in my head than enter dates in Excel. Good for you though.

https://support.microsoft.com/en-us/off ... 100a162f38

Pretty sure if its just calc'ing the days, its =B2-A2, no need for anything else fancy in there.



Source: I do this all the time to see midpoints of lives or "Happy Gilmore is the midpoint between present day and Halloween 1967.

Oh, Jesus. Thank you David!

Huh. I bet it just thinks the dates are fractions like usual and it's getting really lucky.

Is there an easy way to identify the cell in a specific column with the most characters in it?

It takes a little manual intervention ... you want the =len() function and then you could do a =max or something to find the highest if its a big enough sample. To fully automate I think you'd need to add a column that holds the actual cell IDs to reference and even then it would only return the top one if there's a tie.

Gracias.

Say I wanted to go from this:



to this:



What's the simplest way to get there?

If you don't have to do further manipulation a copy(or cut)/paste then sort would be the easiest. If you've got data there that has to be responsive it may require more finagling.