Register a SA Forums Account here!
JOINING THE SA FORUMS WILL REMOVE THIS BIG AD, THE ANNOYING UNDERLINED ADS, AND STUPID INTERSTITIAL ADS!!!

You can: log in, read the tech support FAQ, or request your lost password. This dumb message (and those ads) will appear on every screen until you register! Get rid of this crap by registering your own SA Forums Account and joining roughly 150,000 Goons, for the one-time price of $9.95! We charge money because it costs us money per month for bills, and since we don't believe in showing ads to our users, we try to make the money back through forum registrations.
 
  • Post
  • Reply
LawfulWaffle
Mar 11, 2014

Well, that aligns with the vibes I was getting. Which was, like, "normal" kinda vibes.

kumba posted:

my first thought: your last argument is <=DATE(2021,9,30), but that means MIDNIGHT 9/30/21. Are there times hidden in column K, and are there entries for 9/30/21 that sum up to your difference (and a corresponding lack of entries on the final days of other months that do calculate right)?



746,307-688,249=58058

Goddamn, got it in one. Thank you! The months that were correct had a last day of the month fall on a holiday or weekend.

If I drop the DATE() function and replace it with a cell reference to a cell with a date in it, does it do the same thing? As in, should I set my start cell as 9/1/2021 and my end cell as 10/1/2021?

Adbot
ADBOT LOVES YOU

kumba
Nov 8, 2003

I posted my food for USPOL Thanksgiving!

enjoy the ride

Lipstick Apathy

LawfulWaffle posted:



If I drop the DATE() function and replace it with a cell reference to a cell with a date in it, does it do the same thing? As in, should I set my start cell as 9/1/2021 and my end cell as 10/1/2021?

yes and yes - and drop the = from the <= to avoid an exact 10/1 at midnight entry being counted in september

LawfulWaffle
Mar 11, 2014

Well, that aligns with the vibes I was getting. Which was, like, "normal" kinda vibes.

kumba posted:

yes and yes - and drop the = from the <= to avoid an exact 10/1 at midnight entry being counted in september

Thank you kindly. I don't think a midnight entry is likely but thank you for pointing out that wrinkle with the >= v >, gonna tuck that into the ol' mental rolodex,

Rabbit Hill
Mar 11, 2009

God knows what lives in me in place of me.
Grimey Drawer
I have a big spreadsheet which was set up in a clumsy manner, the biggest problem being that it combines two variables in single columns. I want to separate these columns of combined variables into separate columns.

Take this hypothetical example with hypothetical data (color-coded for clarity):



In this example, the spreadsheet is combining the law enforcement agency and the crime into a single column. I want to be able to separate the agency and the crime into two separate columns, and have the spreadsheet look like this:



Is there any way to do this using a formula or some kind (any kind :pray:) of shortcut, so I don't have to do it by hand? The actual spreadsheet I'm working with is really big (imagine 100 agencies, 20 crimes each, for 15 years) and ain't nobody got time for that.

esquilax
Jan 3, 2003

Rabbit Hill posted:

I have a big spreadsheet which was set up in a clumsy manner, the biggest problem being that it combines two variables in single columns. I want to separate these columns of combined variables into separate columns.

Take this hypothetical example with hypothetical data (color-coded for clarity):



In this example, the spreadsheet is combining the law enforcement agency and the crime into a single column. I want to be able to separate the agency and the crime into two separate columns, and have the spreadsheet look like this:



Is there any way to do this using a formula or some kind (any kind :pray:) of shortcut, so I don't have to do it by hand? The actual spreadsheet I'm working with is really big (imagine 100 agencies, 20 crimes each, for 15 years) and ain't nobody got time for that.

There might be a way to use sumifs or index/match, but an array formula is the easiest way for me. I'll call the first chart "ugly" and the second one "neat"

Step 1: Add "helper" rows to the top or bottom of the ugly table. For example, if you insert rows above, column B would have "Agency #1" in B1 and "Murder" in B2. This should match the labels you use in the neat table.
Step 2: Build your neat table.
Step 3: Create a formula to pull the numbers based on each column as your criteria. If for example, the "Year" label of the ugly chart started in A3, with your helper labels above in rows 1 and 2, your formula for the first row would be something like:
=sum(if((Neat!A2=Ugly!$A$4:$A$6)*(Neat!B2=Ugly!$B$2:$J$2)*(Neat!C2=Ugly!$B$3:$J$3), $B$4:J$6, 0))
Step 4: Press Ctrl+Shift+Enter to turn that formula into an array formula
Step 5: Copy that formula down to the other columns

The formula in step 3 basically applies all 3 criteria to the ugly chart to find which numbers to pull. The multiplication is just a way to do an "AND" function, and the fact that it sums doesn't matter since it's only pulling a single number for each.

Wandering Orange
Sep 8, 2012

Rabbit Hill posted:

I have a big spreadsheet which was set up in a clumsy manner, the biggest problem being that it combines two variables in single columns. I want to separate these columns of combined variables into separate columns.
...

That can be done with an unpivot and then column split in the Power Query editor if you are willing to lose the cell/color formatting. There are other benefits to this method like being able to go back and edit the steps but I won't preach my Business Intelligence Bull poo poo here. You may have to download the PQ add-on from Microsoft if you don't have it installed.

1. Select the entire range of data then go to the Power Query tab and click 'From Table/Range', it should open the PQ editor. Open the Transform tab, click the Year column header, then click the dropdown next to Unpivot Columns and click Unpivot Other Columns. You should then have 3 columns titled Year, Attribute, and Value which correspond to your Year, Agency Crime, and Count columns.

2. Click the Attribute column, click the Extract button, then the Text After Delimiter dropdown option. Enter the hyphen as the delimiter and click OK, it should split that Attribute column to Attribute.1 and Attribute.2 columns. You may have to play around with this or other split/extract functions depending on the data you actually have. I can imagine your agencies may not all conform to a single rule. :)

3. Then you can rename all of the columns as you want - just double click on the column header.

4. When you're done, go back to the Home tab and click Close & Load To..., then select where you want to put the new table - probably a new worksheet. Click Load and you're done!

DarkHorse
Dec 13, 2006

Vroom Vroom, BEEP BEEP!
Nap Ghost

Rabbit Hill posted:

I have a big spreadsheet which was set up in a clumsy manner, the biggest problem being that it combines two variables in single columns. I want to separate these columns of combined variables into separate columns.

Take this hypothetical example with hypothetical data (color-coded for clarity):



In this example, the spreadsheet is combining the law enforcement agency and the crime into a single column. I want to be able to separate the agency and the crime into two separate columns, and have the spreadsheet look like this:



Is there any way to do this using a formula or some kind (any kind :pray:) of shortcut, so I don't have to do it by hand? The actual spreadsheet I'm working with is really big (imagine 100 agencies, 20 crimes each, for 15 years) and ain't nobody got time for that.

Power Query is absolutely the way to go about this, as others have mentioned.

If you haven't done it before, it's under Data. You can create a new file and Get From Other Source, then point it at the original file. Another way is to make your data into a table by highlighting and Ctrl + T (or Insert -> Table), then using Data -> Get Data From Table

The interface is pretty intuitive even if the process might not be, but you can fiddle with the steps and it will process the entire file how you tell it.

Everett False
Sep 28, 2006

Mopsy, I'm starting to question your medical credentials.

I've got a Power Query question! I'm using it to import a list of receipts with dates attached so that I can create PivotTables from it. But in order for summarizing by date and cross-referencing with other tables to work properly, I need to fill in all the dates that are missing (i.e. even if there aren't any receipts for the last week of January I still need all those days in my PivotTable). My current solution is that after I update my query, I scroll to the bottom of the table and type the first day of the month and drag it down until I have every day in that column (the rest of the columns can be blank, it's fine).

Is there an easy way to make Power Query fill in those missing dates on its own? It feels like the hard part would be getting it to recognize that I specifically want to fill out the month, and not just fill in the dates between the ones I already have.

Wandering Orange
Sep 8, 2012

Haven't had my caffeine yet this morning but that sounds like a job for a union/merge with a date table. Create another table that has all the dates for the month and add that to PQ, then do a merge from that table to your receipts table using the date fields and a Full Outer join. This will match & merge all of the dates from both tables, any dates with blank receipts will still show up.

If it were me, I'd create a date table with the next year+ of dates that also has columns for fiscal year, fiscal month, and fiscal week. Then when you do your monthly analysis, just add a step before the merge mentioned above and filter the date table to month you're working on.

Heavy_D
Feb 16, 2002

"rararararara" contains the meaning of everything, kept in simple rectangular structures
If you're keen to fully automate this, the List.Dates function will probably be quite helpful. You'd have to do some of the maths yourself to figure out the count of dates you need to generate, given your first and last dates in the given data, plus a bit to get to the end of the final month.

Edit: Simpler still, apply Date.EndOfMonth to your max date and the hard part of the calculation is done.

Heavy_D fucked around with this message at 10:19 on Feb 11, 2022

AG3
Feb 4, 2004

Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.

Oven Wrangler
I don't know if this is beyond the scope of Excel, but I thought I'd at least ask before giving up.

The software we are currently using for managing our inventory and known products is going to be replaced by a different one that lacks the same search capabilities (yay for parent company shenanigans). I've been trying to find a way to use Excel instead for searching, and it sort of works, but it's unsurprisingly much more fiddly than the current software we use. Below is an example of how it looks in Excel with only the bare minimum required fields:



Apart from the first 3 columns plus the MRSP column, all of the columns contain a mix of text and numbers somewhere. While I can search by using the filter buttons on each column, this is as mentioned fiddly and I would like to emulate the current software by simply being able to type what I'm looking for in a cell in row 2 and have it search the whole column beneath it and filter out the results. Likewise clearing the contents of the cell should also clear the filtering.

Because we don't always know the exact measurements of what we're looking for, we often do a range search between two values, which I can replicate by hitting the filter button and going into "Number Filters--> Between", but again, a lot of clicking for what is really quick and easy today; we simply type something like 80:85 in a cell to list everything from 80.00 to 85.00. In the text fields, we mostly use wildcard searches with * .

I'm thinking that it should be possible, but I imagine it will require VBA which I don't know. I've been googling for the past week or so trying to find a ready made solution for this, but so far I've come up empty. Normally I'd resign myself to my fate, but our inventory list has some 65.000 entries which often requires a ton of fine-tuning searches across multiple columns to find what we're looking for, hence why I'm desperate for something faster and easier than Excels filter button fiddling.

Is what I want even possible in Excel?

AG3 fucked around with this message at 14:37 on Feb 15, 2022

DRINK ME
Jul 31, 2006
i cant fix avs like this because idk the bbcode - HTML IS BS MAN
Yes possible but I think you’d need to use VBA to get what you want (although it’s 2am and I’ve been helping with database migrations all night so I might be overlooking something).

I know you said you don’t use vba but I’m not in front of a computer so I’m just going to give broad strokes of how I would approach it. I think I could put it together pretty quickly but I have spent so many years being the Excel Guy.

I’d use the worksheet On Change property:
*Target range of 2a:2j (rows you have data in)
*If change not in target range then exit
*If there’s an existing filter on this column, clear it (I think you need to otherwise might have issues but might not)
*Then you’d work out what type of filter you need to do and the values with some string functions:
- If string contains a colon you’re using the between filter (if() and instr() function)
- If you’re using the between filter you separate the lower and upper (from and to) values to variables (left() and right() functions)
- Otherwise (I guess) you’re doing a wildcard search and not exact?
- In that case use the contains filter (or equals if you do want it to do exact)
*Apply the chosen filter to the column (I think it’s like target.column to aim it at the right column)

It’s definitely going to be a bit of dicking around to build this yourself if you’ve not dealt with excel vba before. What you can do to get started is hit macro record button and then record a macro while you filter a column with a contains filter, the clear the filter, then do a between filter. That will give you some of the pieces you need to put together for it.

Then you’d need to copy and modify those recorded statements from specifics like
Range(“a:a”).filter.contains.value=”80:85”
To variables like
Range(target.column).filter.whatever.value=lower_value&”:”&upper_value
(These aren’t real code, just stupid examples)

One you’re going to have to google is something like “excel vba onchange target macro”. That should give you the where and how to setup the code that will only fire when the values change and how to target it to just the area you want to watch for changes in. That way if you make a note or add a formula it doesn’t bother you.

DRINK ME
Jul 31, 2006
i cant fix avs like this because idk the bbcode - HTML IS BS MAN
Here you go, I knocked this up quickly and think it will do what you need. Stick it in the worksheet code that you’re working on.

It’s a bit messy cause it’s been through email and then phone posting and it’s not perfect, I had to add in the last “show row 2” line because when excel filters it filters everything in the range, so when I typed “1:20” it would hide row 2 because it is not a number between 1 and 20.

!Change a2:d2 to the range where you will be entering filter values.

code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Me.Range("A2:d2")) Is Nothing Then
'if multiple cells are selected then exit

If Target.Cells.Count > 1 Then
Exit Sub
End If

'set data_range
'get current selection
my_selection = ActiveCell.Address
'find last cell
Selection.SpecialCells(xlCellTypeLastCell).Select
last_cell = ActiveCell.Address
'put the selection back where it started
Range(my_selection).Select
data_range = "a1:" & last_cell

'check if it was a delete and if target empty then clear filter
If Target.Value = "" Then
Range(data_range).AutoFilter Field:=Target.Column
Exit Sub
End If

'decide what filter type to use
If InStr(1, Target.Value, ":", vbTextCompare) Then
filter_type = "between"
Else
filter_type = "contains"
End If

'find values for filter
If filter_type = "between" Then
lower_filter_value = Left(Target.Value, InStr(1, Target.Value, ":") - 1)
upper_filter_value = Right(Target.Value, Len(Target.Value) - InStr(1, Target.Value, ":"))
End If

If filter_type = "contains" Then
filter_value = Target.Value
End If

'clear existing filer on this column
Range(data_range).AutoFilter Field:=Target.Column

'apply filter
If filter_type = "between" Then
Range(data_range).AutoFilter Field:=Target.Column, Criteria1:=">=" & lower_filter_value, Operator:=xlAnd, Criteria2:="<=" & upper_filter_value

ElseIf filter_type = "contains" Then
ActiveSheet.Range(data_range).AutoFilter Field:=Target.Column, Criteria1:="=*" & filter_value & "*", Operator:=xlAnd

End If

Else

Exit Sub

End If

'set row2 to visible
Rows("2:2").RowHeight = 15

End Sub

AG3
Feb 4, 2004

Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.

Oven Wrangler
Thanks a lot for the code! I couldn't quite get it to work, but at least I learned something trying to troubleshoot.

Basically when I type something in a box, it'll pop up a runtime error 1004 "Autofilter method of range class failed", with the debug button highlighting this:
code:
'clear existing filer on this column
Range(data_range).AutoFilter Field:=Target.Column
If I comment it out and try again, the same error pops up but targeting this section:

code:
'apply filter
If filter_type = "between" Then
Range(data_range).AutoFilter Field:=Target.Column, Criteria1:=">=" & lower_filter_value, Operator:=xlAnd, Criteria2:="<=" & upper_filter_value

ElseIf filter_type = "contains" Then
ActiveSheet.Range(data_range).AutoFilter Field:=Target.Column, Criteria1:="=*" & filter_value & "*", Operator:=xlAnd
It'll highlight either the top or bottom depending on which type of search I do.

The same error likewise pops up when I try to clear a search box, but with the debug marking that relevant part of the code. Googling seems to indicate that it's a common error, but I can't quite wrap my head around how a fix would work for this particular code.

Time to call it a day and look at it again tomorrow when brain is less fried.

DRINK ME
Jul 31, 2006
i cant fix avs like this because idk the bbcode - HTML IS BS MAN
Unfortunately Error 1004 is a surprisingly common error that doesn’t tell ever tell you much detail.

I’m taking a guess but I think the data_range might be the issue. If you run it again, you can jump into debug and have a look at what value that has. Once you’re in the code view, click on View menu at the top of the screen and choose Locals Window (if you don’t already have Locals showing). This will list all the variables/expressions and their current values. It should be “a1:$P$30” style - where P30 is the last cell with data in it that you’re interested in.

Useless extraneous information: you could also do this with the Immediate window. If you turn Immediate on from the View menu, then you can type “print data_range” and it will tell you the current value of the variable.

If for some reason it’s set to something stupid like $XFD$1048576 then you need to tell excel it’s being stupid. It sometimes picks up where data once was or where formatting runs to the end of the sheet just because…

You should be able to reset that but it’s sometimes a fight. I usually select from the right of my data to the end of the sheet and then ‘clear contents’ and ‘delete’. Doing both seems redundant but that’s my experience with excel. Then repeat the same at the bottom of the data.

If that doesn’t resolve things you can try adding the line “Activesheet.Usedrange” at the top of the code, this usually(but not always) forces excel to re-evaluate the actual range with data in it.

Finally, if its still having the same issue (saying you have data out to a stupid range where you don’t and refuses to to be fixed: Copy the data to a new worksheet. Copy the code to the new worksheet. Use that worksheet.
—-

One other thing to check: make sure you’ve updated the “A2:D2” in the first line of the code. The D should be the last column you have data in.

AG3
Feb 4, 2004

Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.

Oven Wrangler
After changing this

code:
data_range = "a1:" & last_cell
to this

code:
data_range = "a1:$j$54381" & last_cell
it changed the error to "application-defined or object-defined error" pointing towards the same code lines as earlier. Just to test I also made a new file with just 11 lines and changed the range accordingly and it gave the same error. Googling and trying various things from there doesn't seem to have done much, it still throws out that error. It doesn't seem to narrow down things much more than the previous error :sweatdrop:

I have the code pasted in the sheet directly instead of as a separate module since I couldn't get it to run at all when it was in a separate module, I don't suppose that's an issue?

DRINK ME
Jul 31, 2006
i cant fix avs like this because idk the bbcode - HTML IS BS MAN
Nah all good in the sheet - that’s what it’s intended for.

Ditch the “last_cell” and then you’ve manually set the range it’s looking at. Not great long term but will help with testing.
code:
data_range = "a1:$j$54381"
With what you had it would end up something like “a1:j54381j54381” which isn’t a valid range.

Let me know if that sorts it, otherwise I can throw up an example file tomorrow and you can start from that / see if that works for you. I don’t think any of that code is excel version specific so I don’t think it’s that - it’s sometimes a problem with this sort of thing, like if you use .formula2 instead of .formula it won’t run in excel 2016 but will in later versions.

AG3
Feb 4, 2004

Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.

Oven Wrangler
Holy crap, that actually fixed it! It doesn't seem to like "& last_cell" , but manually setting the range and dropping last_cell made the whole thing work! I guess the code needs to change as the list grows, but that's a tiny price to pay for a an actual practical filter function.

One thing I noticed is that the wildcard "*" are reduntant since the filter in Excel seems to do that by default; i.e if I search for 1234 it'll still show a line containing ABC1234EDF whether the wildcard * is there or not. Is there a way to make it default to "begins with" when there are no wildcard * present? And for the numbers only fields (columns A to C) "equals" when there are no ":" present?

Shazback
Jan 26, 2013
Why not set last_cell to be the actual cell and then use last_cell.address ?

Phone posting so I haven't tried the code, but I'd expect that the issue comes from assigning a cell address directly to a variable which might not result in the expected type of string...

Edit: After quick test the above doesn't seem to be the case, cell.address seems to return a nice boring string in A1 format with dollars.

Shazback fucked around with this message at 16:51 on Feb 17, 2022

Fritzler
Sep 5, 2007


I'm using SUMIFS to sum a column based on two different categories, and I am trying to show the breakout for pretty much every permutation (there are a lot).

So I used:

=SUMIFS($H$2:$H$207,$C$2:$C$207,INDIRECT("B214"),$E$2:$E$207,INDIRECT("A214"))

B214 is basically a text category that reference something in the C column, and same for A214 and the E column.

This formula works, and pulls the numbers I want. However I want to be able to drag to it down and B214 and A214 update to B215 and A215 next, etc. I believe the INDIRECT is what is preventing this. Is there something else I can put in there? If need be I can manually change all those cells but would prefer not to. I tried just putting quotes there as well. I am not sure that indirect is the best thing here, but when I was searching online for help that is what people seemed to be using, but they were mainly referencing sheet names.

Edit: I am not sure this is best way but I fixed by adding a concatenate and row reference:

=SUMIFS($H$2:$H$207,$C$2:$C$207,INDIRECT(CONCATENATE("B",ROW(B214))),$E$2:$E$207,INDIRECT(CONCATENATE("A",ROWA214))))

It now sums and works when I drag it down. I am glad this works, but is this the best way to do it?

Fritzler fucked around with this message at 18:46 on Feb 17, 2022

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

AG3 posted:

Holy crap, that actually fixed it! It doesn't seem to like "& last_cell" , but manually setting the range and dropping last_cell made the whole thing work! I guess the code needs to change as the list grows, but that's a tiny price to pay for a an actual practical filter function.

One thing I noticed is that the wildcard "*" are reduntant since the filter in Excel seems to do that by default; i.e if I search for 1234 it'll still show a line containing ABC1234EDF whether the wildcard * is there or not. Is there a way to make it default to "begins with" when there are no wildcard * present? And for the numbers only fields (columns A to C) "equals" when there are no ":" present?

Yeah that’s all possible but it was just easier to knock it up than to try type out each change. The wildcard filtering doesn’t sound like it’s doing what it should from your description, so hopefully this works.

I changed how the data range is determined, since the previous attempt wasn’t happening. I think P is your last column but that may need updating - I added capslock notes for each of them.

code:
 Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Me.Range("A2:P2")) Is Nothing Then
'UPDATE ABOVE LINE REF FOR COL P
'if multiple cells are selected then exit
If Target.Cells.Count > 1 Then
Exit Sub
End If
 
'set data_range
last_cell = worksheetfunction.counta(range("P:P")) + 1
'UPDATE ABOVE LINE REF FOR COL P
data_range = "a1:p" & last_cell
'UPDATE ABOVE LINE REF FOR COL P

'check if it was a delete and if target empty then clear filter
If Target.Value = "" Then
Range(data_range).AutoFilter Field:=Target.Column
Exit Sub
End If

If Target.Column <= 3 Then
'number filter

'decide what filter type to use
If InStr(1, Target.Value, ":", vbTextCompare) Then
filter_type = "between"
Else
filter_type = "equals"
End If

Else
'text filter

'decide what filter type to use
If InStr(1, Target.Value, ":", vbTextCompare) Then
filter_type = "between"
Else
filter_type = "begins"
End If

End If

'find values for filter
If filter_type = "between" Then
lower_filter_value = Left(Target.Value, InStr(1, Target.Value, ":") - 1)
upper_filter_value = Right(Target.Value, Len(Target.Value) - InStr(1, Target.Value, ":"))
End If

If filter_type = "equals" Or filter_type = "begins" Then
filter_value = Target.Value
End If

'clear existing filer on this column
Range(data_range).AutoFilter Field:=Target.Column

'apply filter
If filter_type = "between" Then
Range(data_range).AutoFilter Field:=Target.Column, Criteria1:=">=" & lower_filter_value, Operator:=xlAnd, Criteria2:="<=" & upper_filter_value
ElseIf filter_type = "equals" Then
ActiveSheet.Range(data_range).AutoFilter Field:=Target.Column, Criteria1:="=" & filter_value, Operator:=xlAnd
ElseIf filter_type = "begins" Then
ActiveSheet.Range(data_range).AutoFilter Field:=Target.Column, Criteria1:="=" & filter_value & "*", Operator:=xlAnd
End If

Else

Exit Sub

End If

'set row2 to visible
Rows("2:2").RowHeight = 15

End Sub

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

Fritzler posted:

I'm using SUMIFS to sum a column based on two different categories, and I am trying to show the breakout for pretty much every permutation (there are a lot).

So I used:

=SUMIFS($H$2:$H$207,$C$2:$C$207,INDIRECT("B214"),$E$2:$E$207,INDIRECT("A214"))

B214 is basically a text category that reference something in the C column, and same for A214 and the E column.

If A214 and B214 are just text you shouldn’t need the Indirect. This way when you drag the formula down it will update those values automatically.
=SUMIFS($H$2:$H$207,$C$2:$C$207,B214,$E$2:$E$207,A214)

I usually use Indirect for things like named references, variable stuff. Exceljet has some good examples of when it’s useful.

AG3
Feb 4, 2004

Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.

Oven Wrangler

DRINK ME posted:

Yeah that’s all possible but it was just easier to knock it up than to try type out each change. The wildcard filtering doesn’t sound like it’s doing what it should from your description, so hopefully this works.

I changed how the data range is determined, since the previous attempt wasn’t happening. I think P is your last column but that may need updating - I added capslock notes for each of them.


This works more or less exactly how I had imagined. Thank you so very much, this is going to save me no small amount of headaches if my worst fears regarding the new software comes to pass! If I can buy you a couple of beers through paypal or something do let me know.

DRINK ME
Jul 31, 2006
i cant fix avs like this because idk the bbcode - HTML IS BS MAN
Thanks but not necessary. It’s an interesting idea and I think I might be able to use something very similar at work. I’ve always just accepted “this is how to filter excel” without considering it could be done different or better.

AG3
Feb 4, 2004

Ask me about spending hundreds of dollars on Mass Effect 2 emoticons and Avatars.

Oven Wrangler
So I've been fiddling with this for a bit and tweaked a few things (thanks to Google; I still can't code the stuff :v: ) and there is one thing that has me stumped again. How on earth do you make sorting results not include row 2, the one you enter search criteria into? You'd think there would be an easy way to tell Excel "exclude this row from sorting" or "anchor this row in place and don't move it or its contents" but there doesn't really seem to be, except for row 1 which is the header.

The problem of course is that as soon as you sort, the results start at row 2, which means that you can't use the search box again unless you delete the contents that just appeared (bad) or try to CTRL+Z, which works... sometimes at least. For now I'm resetting it by giving the search fields in row 2 a blue background, which I can sort by to get it to come back on top after I'm done with the actual search. If getting the sort function to leave row 2 alone is impossible then the only solution that comes to mind is to replace entering data in row 2 with ActiveX text fields for entering data instead or something. But that seems like a lot of work for something you'd think was straight forward to tell Excel to do (leave row 2 alone). But then again a lot of things aren't straight forward in Excel, so.

DRINK ME
Jul 31, 2006
i cant fix avs like this because idk the bbcode - HTML IS BS MAN
That’s a good question. I can’t think of a way to do it off the top of my head.

I think you’d have to either build your own sorting or move your filters outside of the table (maybe above the header row) so that the normal sort can do it’s job. I’m away from computers today so can’t see how sorts work (and I can’t remember the last time I used sort in vba) but maybe hit record macro, sort a column, stop recording, undo the sort and then you can take a look at the code it used to sort the data.

If it ends up being something simple like range(“A1:P100”).sort column:=3, sortorder:=ascending then you could possibly just push it to “A3:P100” instead. Although I doubt it’s going to be that easy.

I’ll let it roll around in the back of my brain and have a look tomorrow when I’m back at work. Sometimes that works for me; it’ll be like midnight and my brain will go “hey I know how to solve that stupid work problem now” and I get to experience disappointment and excitement at the same time.

Everett False
Sep 28, 2006

Mopsy, I'm starting to question your medical credentials.

Let's say that in A1 I have "February 14 9:00:00" and in B1 I have "February 15 9:00:00". Then in C1 it just has "February 15" with no time attached. Is there an easy way to determine how many of the hours between A1 and B1 are in the date given in C1? The end goal would be a formula that in this case would spit out '9.00'.

I feel like the answer will be "I can do it, but it will involve a lot of suffering and helper columns and nested formulas", but hope springs eternal.

kumba
Nov 8, 2003

I posted my food for USPOL Thanksgiving!

enjoy the ride

Lipstick Apathy

Everett False posted:

Let's say that in A1 I have "February 14 9:00:00" and in B1 I have "February 15 9:00:00". Then in C1 it just has "February 15" with no time attached. Is there an easy way to determine how many of the hours between A1 and B1 are in the date given in C1? The end goal would be a formula that in this case would spit out '9.00'.

I feel like the answer will be "I can do it, but it will involve a lot of suffering and helper columns and nested formulas", but hope springs eternal.

a simple =ABS((C1-B1)*24) (or really just a =(B1-C1)*24 but going in that direction feels wrong to me for some reason) in this case will give you the 9 hours but i'm guessing real data is going to be more complicated than that

Everett False
Sep 28, 2006

Mopsy, I'm starting to question your medical credentials.

Oh that's way simpler than what I was thinking, I might be able to make that work in conjunction with a bizarre series of IF statements. That's exactly the nudge I needed, I've been staring at it so long I've overcomplicated everything in my head.

I'm trying to do something clever with timesheets for a department whose current timesheets are done in excel and are. An absolute nightmare. Absolutely everything gets calc'd out manually and usually wrong. I briefly thought I had managed to automate all of the obtuse rules around pay calculations before I remembered that the 24 hour shifts don't actually line up to midnight-midnight which is how additional holiday pay is determined... :argh:

So if someone has a 24 hour shift from 9-9 on the 20th I need to correctly allocate 9 of those hours to holiday, but if they work 9-9 on the 21st I need to make sure that 9 of those hours aren't included. Which I think I can do with simple subtraction like you have, I just have to determine what happens based on whether the date the shift starts or ends corresponds to one of the dates in my table of holidays.

DRINK ME
Jul 31, 2006
i cant fix avs like this because idk the bbcode - HTML IS BS MAN
Just chiming in - the reason that works is Excel dates all include a time component, even if it’s not visible. So where you have 15th Feb 2022 Excel actually sees that as 00:00 15/02/2022 (or 02/15/2022 if you use American format). Then under the covers Excel stores it as 44607, which is the number of days since 01/01/1900. With the time component it is 44607.00 - the .00 represents the fraction of the day, or 24 hours.

So jumping back to the 9:00am example it would be 44607.375, where .375*24=9.
9:00pm would be 44607.875, where .875*24=21. 21 hours or .875 of the day, if you’re used to 24hour clocks then it’s a simple 2100hours.

Hopefully you can manage to avoid having like an extra sheet of helper columns and formulas but just going to throw in one useful one for dealing with time
=number - trunc(number)
This pulls just the decimal part of the number, or when working with dates and times it’s just the time.

Personally I hate working with times and dates because I wish it was decimal - French revolution time or even swatch @beat time would be better.

C-Euro
Mar 20, 2010

:science:
Soiled Meat
Speaking of SUMIF functions and time, my wife is working up a simple expense sheet for her startup to help her break down costs by type and month-


The SUMIF for breaking down costs based on type is easy enough, but I'm not sure how best to do the by month SUMIF. Is there something I have to add to the second argument of the function to help it recognize months in Column B, or do I need to write out the months in Column I in a certain way?

C-Euro fucked around with this message at 05:31 on Mar 2, 2022

DRINK ME
Jul 31, 2006
i cant fix avs like this because idk the bbcode - HTML IS BS MAN
One way to do it (and there are multiple like with everything in Excel) is change your Jan-Mar in column I to dates:
01/01/2022
02/01/2022
03/01/2022
If you still want them to look the same you can go to Format Cells | Custom and use dddd.

Then your formula for J2 would be
=Sumifs(c:c, $b:$b, “>=“&i2, $b:$b, “<“&i3)

That is summing C where B is between those dates. The only catch is you need an extra value in i5 - 04/01/2022 would make sense. You can hide it if you want to keep things tidy but the formula just needs an end date.

Edit: you could also make little pivot tables instead and save yourself the formula bother.
The pívot table can automatically group at Month/Quarter/Year and would make expanding it as the year continues a cinch.

DRINK ME fucked around with this message at 06:25 on Mar 2, 2022

Strong Sauce
Jul 2, 2003

You know I am not really your father.





C-Euro posted:

Speaking of SUMIF functions and time, my wife is working up a simple expense sheet for her startup to help her break down costs by type and month-


The SUMIF for breaking down costs based on type is easy enough, but I'm not sure how best to do the by month SUMIF. Is there something I have to add to the second argument of the function to help it recognize months in Column B, or do I need to write out the months in Column I in a certain way?



Put this in J2 and copy it down.

quote:

=SUMIFS($A2:$A, $B2:B, ">=" & DATEVALUE(MONTH($I2&1) & "/1/2022"), $B2:$B, "<=" & DATEVALUE(MONTH($I2&1) & "/31/2022"))


quote:

=MONTH("January" & 1) converts to 1
=MONTH("February" & 1) converts to 2, etc...

Edit: apparently the above formula does not like 2/31/2022 kinda dates, DATEVALUE is not necessary, and you need to use $A$2/$B$2 to anchor those ranges so the correct way apparently is this:

quote:

=SUMIFS($A$2:$A, $B$2:B, ">=" & MONTH($I2&1) & "/1/2022", $B$2:$B, "<=" & EOMONTH(DATEVALUE(MONTH($I2&1) & "/1/2022"), 0))

The last part generates the date for the end of the month

Strong Sauce fucked around with this message at 09:24 on Mar 2, 2022

HootTheOwl
May 13, 2012

Hootin and shootin
Can index and match be conditional?

Like lets say I have some arbitray number of rows and two columns A and B. Can I then add a third column and using index and match have column C equal to some concatination of every row where B is equal to the A on the specific row I'm working on?

code:
[a] [b]
[x] [a]
[y] [z]
becomes
code:
[a] [b] [x]
[x] [a] []
[y] [z] []

Shazback
Jan 26, 2013
If you just want to check for an item in a cell [A1] that there is at least one match in a column [B] :

= Countifs( B:B , A1 ) > 0

This formula can be extended down (to replace A1 by A2 in the calculation in the next line) or expanded (if you have other criteria to determine if a row should be true or false), and if you want to match an exact number of times you can just change the equivalence at the end (exactly one match being " = 1 " as you'd expect).

Is this what you were looking for?

Everett False
Sep 28, 2006

Mopsy, I'm starting to question your medical credentials.

DRINK ME posted:

Just chiming in - the reason that works is Excel dates all include a time component, even if it’s not visible. So where you have 15th Feb 2022 Excel actually sees that as 00:00 15/02/2022 (or 02/15/2022 if you use American format). Then under the covers Excel stores it as 44607, which is the number of days since 01/01/1900. With the time component it is 44607.00 - the .00 represents the fraction of the day, or 24 hours.

Yeah, that's why I'm now running into the problem with matching to my list of holidays where Excel goes, "Oh, the shift ends at February 21st at 9:00 am? That's not February 21st at midnight! No holidays detected."

Somehow this was not the part that I thought would give me problems.

Lib and let die
Aug 26, 2004
Probation
Can't post for 23 hours!
I don't know that this is the best thread for it, but since Excel is so often used for working with csv files...is there a better tool? Or a specific set of options I can turn on to have Excel not do any of its allegedly helpful things with say, date and time stamps?

I'm talking 500,000+ rows of data, so a notepad++/general IDE is out because I need the cell-based display, but good lord Excel really needs a very basic no-frills raw csv mode or something.

Wandering Orange
Sep 8, 2012

Before pasting CSV data or otherwise importing, select the entire sheet and change the number formatting to Text?

Lib and let die
Aug 26, 2004
Probation
Can't post for 23 hours!

Wandering Orange posted:

Before pasting CSV data or otherwise importing, select the entire sheet and change the number formatting to Text?

And then when I re-open the file to work with it, I have to convert it all to text again else it saves dates in whatever hosed up format it's decided is best. I suppose I could macrofy it, but then I have to have a workbook with my macros in it open when I'm working with the files too...It's absolutely infuriating. I'm close to giving up on Excel entirely and seeing if Google Sheets handles them any better - I've tried about everything I can think of with Excel (hence turning to my fellow goons for advice)

Adbot
ADBOT LOVES YOU

HootTheOwl
May 13, 2012

Hootin and shootin

Lib and let die posted:

And then when I re-open the file to work with it, I have to convert it all to text again else it saves dates in whatever hosed up format it's decided is best. I suppose I could macrofy it, but then I have to have a workbook with my macros in it open when I'm working with the files too...It's absolutely infuriating. I'm close to giving up on Excel entirely and seeing if Google Sheets handles them any better - I've tried about everything I can think of with Excel (hence turning to my fellow goons for advice)

Turn off automformating?
Past values only?

  • 1
  • 2
  • 3
  • 4
  • 5
  • Post
  • Reply