Excel

Johnny Carwash · Post by **Johnny Carwash** » 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.

A_B · Post by **A_B** » Tue Aug 06, 2019 8:31 am

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?

Jerloma · Post by **Jerloma** » 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!

Baloney · Post by **Baloney** » Fri Sep 08, 2023 7:53 am

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).

degenerasian · Post by **degenerasian** » Fri Sep 08, 2023 10:43 am

fucks up formulas too

Jerloma · Post by **Jerloma** » Thu Dec 28, 2023 10:55 am

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?

A_B · Post by **A_B** » Thu Dec 28, 2023 1:28 pm

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

Johnnie · Post by **Johnnie** » Sun Dec 31, 2023 12:44 pm

This was suggested to me on YouTube:

I had no idea this was a thing.

Johnnie · Post by **Johnnie** » Thu Jan 18, 2024 4:18 pm

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.

Ryan · Post by **Ryan** » Thu Jan 18, 2024 4:26 pm

Put a single quote in front

Johnny Carwash · Post by **Johnny Carwash** » Thu Jan 18, 2024 4:27 pm

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.

Johnnie · Post by **Johnnie** » Thu Jan 18, 2024 4:42 pm

Success. Thank you.

Giff · Post by **Giff** » Fri Jan 26, 2024 4:32 pm

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?

mister d · Post by **mister d** » Fri Jan 26, 2024 4:34 pm

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.

mister d · Post by **mister d** » Fri Jan 26, 2024 4:37 pm

: image.png (15.24 KiB) Viewed 5583 times

A_B · Post by **A_B** » Fri Jan 26, 2024 4:38 pm

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

ETA: So the screenshot does help.

Giff · Post by **Giff** » Fri Jan 26, 2024 6:13 pm

Appreciate the help and laughs.

Jerloma · Post by **Jerloma** » Tue Feb 13, 2024 3:55 pm

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]

mister d · Post by **mister d** » Tue Feb 13, 2024 4:00 pm

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 ...

Jerloma · Post by **Jerloma** » Fri Apr 05, 2024 10:19 am

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.

Ryan · Post by **Ryan** » Fri Apr 05, 2024 11:50 am

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

mister d · Post by **mister d** » Fri Apr 05, 2024 11:56 am

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

Jerloma · Post by **Jerloma** » Fri Apr 05, 2024 12:15 pm

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.

mister d · Post by **mister d** » 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.

Jerloma · Post by **Jerloma** » Fri Apr 05, 2024 1:20 pm

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

wlu_lax6 · Post by **wlu_lax6** » Fri Apr 05, 2024 1:22 pm

Feels like a pivot table scenario

A_B · Post by **A_B** » Fri Apr 05, 2024 2:11 pm

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?

mister d · Post by **mister d** » Fri Apr 05, 2024 2:14 pm

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

Ryan · Post by **Ryan** » Fri Apr 05, 2024 2:31 pm

Fully team pivot now i think

mister d · Post by **mister d** » Fri Apr 05, 2024 2:35 pm

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

Jerloma · Post by **Jerloma** » Mon Jun 03, 2024 12:15 pm

If I have two date columns like this...

: image.png (4.73 KiB) Viewed 4667 times

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?

Ryan · Post by **Ryan** » Mon Jun 03, 2024 12:24 pm

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

mister d · Post by **mister d** » Mon Jun 03, 2024 1:17 pm

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.

Jerloma · Post by **Jerloma** » Mon Jun 03, 2024 2:15 pm

Oh, Jesus. Thank you David!

Ryan · Post by **Ryan** » Mon Jun 03, 2024 2:20 pm

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

Giff · Post by **Giff** » Thu Aug 15, 2024 1:56 pm

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

mister d · Post by **mister d** » Thu Aug 15, 2024 2:09 pm

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.

Giff · Post by **Giff** » Thu Aug 15, 2024 2:16 pm

Gracias.

Johnny Carwash · Post by **Johnny Carwash** » Tue Oct 29, 2024 3:41 pm

Say I wanted to go from this:

to this:

What's the simplest way to get there?

A_B · Post by **A_B** » Tue Oct 29, 2024 3:45 pm

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.