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 »

Lib and let die: Aug 26, 2004

It's just raw UTF-8 CSV files, opening them from explorer. My little machine with 16gb or ram would choke to death copying....

Lemme check real quick....225x24000 out of a csv file and pasting values...back into a csv file.

If I had my way, we'd just be getting full SQL dumps to load and work with but....

Yes, that's 225 columns of data. Exceed Beyond is....silly.

# ? Mar 3, 2022 17:39

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 05:47

Wandering Orange: Sep 8, 2012

Oh god you're dealing with that sort of stuff. Yeah I would be using SSIS/SQL or even Access if I had to as Excel is not going to be friendly.

# ? Mar 3, 2022 17:43

Lib and let die: Aug 26, 2004

Wandering Orange posted:

Oh god you're dealing with that sort of stuff. Yeah I would be using SSIS/SQL or even Access if I had to as Excel is not going to be friendly.

It's both shocking and completely unsurprising that the companies that do business with Nonprofits will do anything and everything the can to prevent customers from leaving their jank-rear end systems, up to and including making the export process as difficult as possible.

# ? Mar 3, 2022 17:46

me your dad: Jul 25, 2006

I'm trying to figure out how to approach a problem, or to see if it is even possible in Excel especially for me, who is a novice.

I have five categories A B C D E

I have 2,000 email addresses which are aligned with at least two of the categories, and a single email address may align with all five categories.

I have been asked to figure out the overlap in the categories. For example, how many people in A/B A/C A/D A/E B/C C/D C/E and so on.

Is this even possible? Aside from the two-category combinations, I need to consider who is in three, four, and five categories. That's a ton of combinations, right?

# ? Mar 24, 2022 13:50

nielsm: Jun 1, 2009

Each category is a binary yes/no. That means you have 2*2*2*2*2 = 32 combinations, possibly minus one if the "no categories" option is invalid/impossible.

# ? Mar 24, 2022 13:58

me your dad: Jul 25, 2006

Thanks - the good news (I think) for me is I just found out they only need these combos:

A B C

A B D

C D

A B C D

I still have no idea how to do it efficiently.

# ? Mar 24, 2022 15:35

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

me your dad posted:

Thanks - the good news (I think) for me is I just found out they only need these combos:

A B C

A B D

C D

A B C D

I still have no idea how to do it efficiently.

What structure is the data in? Does an email address have a row for each category (e.g., the table structure is Email Address | Category) or is it one row per email with multiple columns (e.g., the table structure is Email Address | Cat A | Cat B etc with a value of 0 or 1 or whatever)

How you have it structured will inform your easiest approach

# ? Mar 24, 2022 15:45

timp: Sep 19, 2007; Everything is in my control; Lipstick Apathy

Sounds like maybe Conditional Formatting could help you with this? I do something similar at work where I have to compare long columns for duplicates, and this resource is bookmarked on my browser because I can't bother to memorize the formulas https://www.ablebits.com/office-addins-blog/2019/04/30/google-sheets-compare-two-sheets-columns/

Specifically the parts about "Compare two lists and highlight records in both of them" help me a lot

# ? Mar 24, 2022 16:05

me your dad: Jul 25, 2006

kumba posted:

What structure is the data in? Does an email address have a row for each category (e.g., the table structure is Email Address | Category) or is it one row per email with multiple columns (e.g., the table structure is Email Address | Cat A | Cat B etc with a value of 0 or 1 or whatever)

How you have it structured will inform your easiest approach

After a lot of looking at it, I found it very messy. But I was able to clean it up a bit. Each row contains a category and an email address.

Category | Email

timp posted:

Sounds like maybe Conditional Formatting could help you with this? I do something similar at work where I have to compare long columns for duplicates, and this resource is bookmarked on my browser because I can't bother to memorize the formulas https://www.ablebits.com/office-addins-blog/2019/04/30/google-sheets-compare-two-sheets-columns/

Specifically the parts about "Compare two lists and highlight records in both of them" help me a lot

I have indeed utilized conditional formatting and it has been helpful. After some back and forth with the person I am working with, I have reduced the number of records to about 800 and I am working my way through them manually right now.

# ? Mar 24, 2022 16:31

HootTheOwl: May 13, 2012; Hootin and shootin

Sounds like you want CountIfs

# ? Mar 24, 2022 16:42

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

me your dad posted:

After a lot of looking at it, I found it very messy. But I was able to clean it up a bit. Each row contains a category and an email address.

Category | Email

I have indeed utilized conditional formatting and it has been helpful. After some back and forth with the person I am working with, I have reduced the number of records to about 800 and I am working my way through them manually right now.

here's my maybe overengineered shot at it - you can assign unique values to each category such that each individual combination of categories arrives at a distinct sum (think binary operations). so for example

(EDIT: lol oops you can do it with 1/2/4/8 instead of 1/4/8/16 but whatever you get the idea)

so for the combinations you want, you end up with:

so now we wrap it up: here's my faked data with a vlookup to the points table

from there you do a sumifs for each unique email and then vlookup the total

and voila, you get your desired combinations and you can filter out the #N/A rows that don't match your needed combos

there is probably an easier way to do this but this is where my mind went!

# ? Mar 24, 2022 16:50

Kibayasu: Mar 28, 2010

Are you just counting how many times a combination occurs? That is, for example, 10 emails appear under categories A/B/C. Or do need to have the emails displayed if they match a combination?

# ? Mar 24, 2022 16:55

Falconier111: Jul 18, 2012; S T A R M E T A L C A S T E

I�m having some issues with VBA in Word, but there is no Word VBA thread around these parts. Would this belong here?

# ? Mar 24, 2022 17:25

me your dad: Jul 25, 2006

kumba posted:

here's my maybe overengineered shot at it - you can assign unique values to each category such that each individual combination of categories arrives at a distinct sum (think binary operations). so for example

[snip]

there is probably an easier way to do this but this is where my mind went!

Thank you - this is a good approach!

Kibayasu posted:

Are you just counting how many times a combination occurs? That is, for example, 10 emails appear under categories A/B/C. Or do need to have the emails displayed if they match a combination?

I just need the number - 10 in category AB, 125 in category ABD, etc.

# ? Mar 24, 2022 17:57

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Falconier111 posted:

I�m having some issues with VBA in Word, but there is no Word VBA thread around these parts. Would this belong here?

this is the closest we have to a VBA thread i think and there's definitely excel VBA questions in here, go hog wild

# ? Mar 24, 2022 18:20

Falconier111: Jul 18, 2012; S T A R M E T A L C A S T E

kumba posted:

this is the closest we have to a VBA thread i think and there's definitely excel VBA questions in here, go hog wild

First of all: extremely inexperienced and self-taught, so keep that in mind.

I�m doing a screenshot let�s play right now, and I get the text for my updates by copying it over from the original script in the game files, then running it through a couple VBA scripts that are just big find-replace functions to bring it more in line with SA BBcode. After the first script, I get text looking like this:

quote:

"She forces a smile. It�s not very convincing."
s "Of course I am, silly! Why wouldn�t I be?"
mc "I dunno, you just seem a bit...off, today? Maybe I�m wrong, but�"
"My mind wanders back to what she asked me as we were walking home together."
s "You worry too much, [player]. I�m fine, seriously!"
"She gives a much brighter and more genuine smile.�
"While it seems much more Sayori-like, there�s still something inside me that feels like something�s up."
"Sayori must�ve noticed, as she hastens to explain."
s "To tell you the truth, I just didn�t get much sleep last night. I�m really tired, so sorry if I�m a little cranky, aha�"
s "Seriously though, I�m probably not much fun to hang around with right now."
s "Why don�t you spend some time with the others? I might try and have a little nap�"
mc "Uhm...sure, okay. Just uh...know that if anything�s bothering you, you can talk to me, okay?"
mc "I�d hate to see you suffering in silence."

Each line either starts with double quotes for narration or a letter or two to indicate the speaker. I need some code that will look at each section enclosed by quotes, check if it has the same speaker as the previous line, and if it does, append the two, ideally removing intermediary speaker markings and quotes in the process. I�m hoping it ends up looking like this:

quote:

"She forces a smile. It�s not very convincing."
s "Of course I am, silly! Why wouldn�t I be?"
mc "I dunno, you just seem a bit...off, today? Maybe I�m wrong, but�"
"My mind wanders back to what she asked me as we were walking home together."
s "You worry too much, [player]. I�m fine, seriously!"
"She gives a much brighter and more genuine smile. While it seems much more Sayori-like, there�s still something inside me that feels like something�s up. Sayori must�ve noticed, as she hastens to explain."
s "To tell you the truth, I just didn�t get much sleep last night. I�m really tired, so sorry if I�m a little cranky, aha� Seriously though, I�m probably not much fun to hang around with right now. Why don�t you spend some time with the others? I might try and have a little nap�"
mc "Uhm...sure, okay. Just uh...know that if anything�s bothering you, you can talk to me, okay? I�d hate to see you suffering in silence."

At this point I usually make necessary manual edits before running it through the last script and publishing it. I�ve been removing the unnecessary bits by hand, but I have mobility issues that are getting in the way; text to speech works miracles for prose and I have little issue with standard editing (I got through my last LP just fine), but the software doesn�t accommodate coding and having to edit these marks out over and over for thousands of words/write code by hand to experiment with it is killing me. The rest of the process is under control, it�s just this one area is a sticking point. Does anyone know how to go about researching or implementing a solution?

# ? Mar 24, 2022 23:32

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

I�ve never done macros in Word but I do a lot of Excel stuff. I think it would be like a For loop?

This is sort of pseudocode based on rough excel VBA from memory. I�m sitting on a very boring work call so thought I�d give it a shot but I don�t think I�ve quite got it all figured out, might give you an idea though of how to manipulate stuff - providing this works in Word. I figure VBA should be somewhat similar across MS applications.

I�m not sure what reserved name spaces exist in Word but guessing stuff like Document and Line and Paragraph so instead of saying �for each line in document� I�ve changed it.

Dim this_line as line
Dim next_line as line
Dim this_doc as document

Next line = this_line.offset(1,0)

For each this_line in this_doc
If left(this_line,2) = left(next_line, 2) then
1. take the quotation mark of the end of this_line
/something like left(this_line, len(this_line)-1)
2. add a space to the end of this_line
/this_line = this_line & � �
3. trim next_line to remove everything up to and including the first quote
/right(next_line, find(��, next_line))
4. Might be needed to remove the linebreak / paragraph break. Chr(10) or Chr(13) I think but not sure how to address this�
End if
Next

I think I might have missed an obvious one - might be too simplified for the narration lines, maybe two different checks?
If left(this_line, 1) = �� then
action for narration
Elseif (above check)
action for speaker
End if

# ? Mar 25, 2022 01:16

HootTheOwl: May 13, 2012; Hootin and shootin

Falconier111 posted:

First of all: extremely inexperienced and self-taught, so keep that in mind.

I�m doing a screenshot let�s play right now, and I get the text for my updates by copying it over from the original script in the game files, then running it through a couple VBA scripts that are just big find-replace functions to bring it more in line with SA BBcode. After the first script, I get text looking like this:

Each line either starts with double quotes for narration or a letter or two to indicate the speaker. I need some code that will look at each section enclosed by quotes, check if it has the same speaker as the previous line, and if it does, append the two, ideally removing intermediary speaker markings and quotes in the process. I�m hoping it ends up looking like this:

At this point I usually make necessary manual edits before running it through the last script and publishing it. I�ve been removing the unnecessary bits by hand, but I have mobility issues that are getting in the way; text to speech works miracles for prose and I have little issue with standard editing (I got through my last LP just fine), but the software doesn�t accommodate coding and having to edit these marks out over and over for thousands of words/write code by hand to experiment with it is killing me. The rest of the process is under control, it�s just this one area is a sticking point. Does anyone know how to go about researching or implementing a solution?

Use a code tag
E I can't tell on my phone what you're looking for

HootTheOwl fucked around with this message at 01:39 on Mar 25, 2022

# ? Mar 25, 2022 01:27

HootTheOwl: May 13, 2012; Hootin and shootin

Ok I'm at my computer and I think you overthought it:

First, format your input into an array of strings so it's on per line. It looks like you have this already but in case you don't, use the Split function to achieve this.
Then define an output array of strings
Then, run a For Each loop over the split input array, with each line being an element in the group.
Inside this loop:
If The output array is empty, add the current line to the output, except the last character (which should be a ")
Next

If the output array wasn't empty, then check the first character in the Line. If the line begins with a " then concatinate it onto the last element in the output array (remember to remove the leading and trailing " characters)

Else, concatenate a " character onto the last element in the output array. And then Add the current line as a new element into the output array, removing it's trailing "

Next, End For

My VBA is pretty rusty but it should look something like:

code:

dim Input() as string = Split(whateveryourinputis, "\n")
dim Output() as string = {} (I forget how to initialize arrays in VBA, but just make sure it's not null)

For Each Line in Input
    if Output.Count = 0 Then
        Output.Add(Substr(Line, 0, Len(Line)-1)
    ElseIf Len(Line) > 0 And Left(Line, 1) = "" " Then
        Output(Output.Count-1) = Output(Output.Count-1) + Substr(Line, 1, Len(Line)-2)
    Else
        Output.Add(Substr(Line, 0, Len(LIne)-1))
    End If
Next

Output(Output.Count-1) = Output(Output.Count-1)+"""

I don't know how to put quotes in BBC code blocks, But it should be "\"" (quote, escaped quote, quote) every time you need to check for or add one.

HootTheOwl fucked around with this message at 02:43 on Mar 25, 2022

# ? Mar 25, 2022 02:31

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

I am sure this is some floating point bullshit but I have literally never seen Excel do something like this on a simple calculation

what the actual gently caress is happening to the blue rows and why is the orange row fine

# ? Apr 14, 2022 23:17

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

Excel has some issues with decimal places, 8 bytes / 15 decimal places of precision. I�m not entirely sure if it applies to your example but there�s a detailed write up here on floating point errors.
To be honest I�m hungover and only sort-of following it, which is why I�m not sure if it covers your issue. Last time I saw it at work I gave the person who asked this link to MS and they agreed to round their numbers in future (since no one cares about 0.00032 minutes or whatever fraction they had).

# ? Apr 15, 2022 01:02

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

I only expanded the decimals out to that many places to show that I wasn't hiding a digit in the trillionths place or whatever, the numbers I'm using are really simple. It's gotta be related to that example in your link of not being able to store the value 0.1 in binary, but what is confounding me is that I using the same numbers and getting two different results, e.g. my calculation of 2.4-1.5 = 0.9 and the same exact calculation 5 rows up has a different answer. In what you linked, you can reproduce the binary truncation going on in every case cause the math is always the same, so you would think if it were a binary translation problem or a rounding problem, it would be uniformly wrong but that is not the case!

# ? Apr 15, 2022 02:55

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

I see what you mean now and agree, I�d expect the error to be consistent each time. Are F and H just values?

I have both Sheets (google) and Numbers (apple) on my phone and just did a quick test on them and both are also limited to 15 decimal places by default, so 8 bytes as well. I also tried the 2.4-1.5 and 3.35-3.45 that you originally showed and no issue with either of them. Something specific Excel is doing�

# ? Apr 15, 2022 04:42

Mak0rz: Aug 2, 2008; 😎🐗🚬

So have a column of dates. I want Excel to highlight entries that are a year or more old.

My understanding of how to do it is that I put this formula into the Conditional Formatting tool (assuming the information is in column A):

code:

=DATEDIF($A:$A,TODAY(),"y")>=1

Doesn't do anything. No errors or whatever, it just seems to not do anything at all.

I suspect the reason is that Excel doesn't like working with functions like TODAY() in conditional formatting, because I can make a column of Boolean values with the exact same formula just fine, but some Google results for how to do this suggests that it should.

In another thread I got a reply:

Stickman posted:

Yeah, basically the formula for conditional formatting is the formula for the top left cell in the formatting range, and the rest are applied as if they were autofilled.

In the case of DATEDIF, we can actually give it a range instead of a single cell without it throwing an error. However, in that case it returns an array, which isn't what conditional formatting is looking for so it will just run the same formula for each cell in the conditional format range, pulling the (same) top-left value for each (since it's the first value in the returned array). That means every cell gets the formatting if the formula is true for A1 does and none get the formatting if it's false for A1!

So considering this, is there a way to get Excel to do what I want?

# ? Apr 28, 2022 02:59

esquilax: Jan 3, 2003

Mak0rz posted:

So have a column of dates. I want Excel to highlight entries that are a year or more old.

My understanding of how to do it is that I put this formula into the Conditional Formatting tool (assuming the information is in column A):
code:
=DATEDIF($A:$A,TODAY(),"y")>=1
Doesn't do anything. No errors or whatever, it just seems to not do anything at all.

I suspect the reason is that Excel doesn't like working with functions like TODAY() in conditional formatting, because I can make a column of Boolean values with the exact same formula just fine, but some Google results for how to do this suggests that it should.

In another thread I got a reply:

So considering this, is there a way to get Excel to do what I want?

Yes, that reply is correct.

What you need to do is change the conditional formatting formula to something like:

code:

=DATEDIF($A1,TODAY(),"y")>=1

That formula assumes that your data and conditional format formula starts in cell A1 under "Applies to". If the formatting starts in A3 instead, you replace it with $A3 instead.

edit: This basically the same response as what you got in the math thread

esquilax fucked around with this message at 03:14 on Apr 28, 2022

# ? Apr 28, 2022 03:12

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

The way I�d do it is:
Click on A1
Conditional formatting | New Rule
Choose Use a formula
=(Today()-A1) > 365
Add the format/highlight
Then change the �Applies to� section to be A:A like you need.

That should do what you need with minimal fuss, just ensure you have either A1 or $A1 so it looks at each row.

# ? Apr 28, 2022 04:28

Mak0rz: Aug 2, 2008; 😎🐗🚬

esquilax posted:

Yes, that reply is correct.

What you need to do is change the conditional formatting formula to something like:
code:
=DATEDIF($A1,TODAY(),"y")>=1
That formula assumes that your data and conditional format formula starts in cell A1 under "Applies to". If the formatting starts in A3 instead, you replace it with $A3 instead.

That worked, thank you.

esquilax posted:

edit: This basically the same response as what you got in the math thread

Whoops. I somehow missed that one :sweatdrop:

DRINK ME posted:

The way I’d do it is:
Click on A1
Conditional formatting | New Rule
Choose Use a formula
=(Today()-A1) > 365
Add the format/highlight
Then change the “Applies to” section to be A:A like you need.

That should do what you need with minimal fuss, just ensure you have either A1 or $A1 so it looks at each row.

That works too, yeah. I got in the habit of using DATEDIF for working with months, because it takes into account the variable month length. For a whole year it probably doesn't matter much.

# ? Apr 28, 2022 17:29

esquilax: Jan 3, 2003

Mak0rz posted:

That worked, thank you.

Whoops. I somehow missed that one

That works too, yeah. I got in the habit of using DATEDIF for working with months, because it takes into account the variable month length. For a whole year it probably doesn't matter much.

Don't forget leap years!

# ? Apr 28, 2022 19:43

Mak0rz: Aug 2, 2008; 😎🐗🚬

esquilax posted:

Don't forget leap years!

Yeah I considered that but a creep of one day every four years is not going to be a big deal for my purposes.

Regardless I'm sticking with DATEDIF anyway so it's moot.

# ? Apr 28, 2022 20:02

neogeo0823: Jul 4, 2007; NO THAT'S NOT ME!!

hey thread, I've got a problem I'm hoping you can all help me with. My boss has asked me to get something together to help aggregate PM tasks that need to be completed on some of our machinery at certain hour intervals. Along with that, the's requested that I add in some way to easily list off what parts, materials, and supplies will be used when performing these tasks. He's given me the freedom to accomplish this as I see fit, and I want to make it into a tool that we can use for the next long while, and maybe incorporate it across the entire company. I've decided the "best" way is probably going to be via Excel spreadsheet.

First thing's first, I've been making an actual spreadsheet/grid, where each line lists a task. From left to right, it lists the task type, a small description of what the task is, the actual PM number in the machine manual, and then on the far right, there's a grid set up, where each column is an hour interval, and if the task should be done at that hour interval, then there's a check mark in that spot of the grid. I'm also compiling a second sheet, where each line has the PM number, "consumables" used(gloves, rags, etc), and then part numbers, if applicable, and then tools, if applicable.

What I'd like to be able to do, is have a box where a user can input the hour interval, and then have either a separate section, or a separate sheet, populate with all of the PMs for that hour interval, along with all the materials needed for those PMs. Preferably broken down by the PM number, so that if, for example, you're at the 100 hour interval, and you type that in, each line would populate like:

|____PM#____|_______________Description_______________|_Consumables_|__Part Numbers_|___________Tools___________|
|_PM-B0010_|_Check tension of oven stationary chain__|__Rags, gloves__|_____None______|___________None___________|
|_PM-B0059_|_Check oven locking pins, replace if worn_|______None_____|_752134009(4)_|_Socket wrench, Allen keys_|

etc, etc, etc

What's the "best" way to accomplish this? I've been googling around, and it seems that conditional formatting might be able to solve all this, but I'm way out of my depth when it comes to this stuff. If needed, I can move the required materials list to the same sheet as the grid, but I like the grid as it currently is just for how visually simple it is to look at. I'd also like to be able to expand the grid to add other machines and PMs to it as needed, if possible.

# ? Jul 20, 2022 22:16

Rakeris: Jul 20, 2014

Not sure what the best way would be. I did something similar several years ago with different types of data, unfortunately I don't recall exactly how I did it, I might be able to find one of the old workbooks...

But I used one sheet as a data sheet and kept it hidden, (so people didn't try to break it) populated all the data I wanted to pull from on there, and used data validation to create drop downs so users could select what week they wanted (insert wtvr you want said drop down to be, hours, etc), that would then pull the data from the data sheet and populate the main sheet with said data.

I know that is a really rough outline...hope that helps or gives you something to look at maybe. =\

# ? Jul 21, 2022 00:19

Harminoff: Oct 24, 2005; ð½

I'd look into the Filter function if you have a newer version of excel. It can filter and spill all the data where you need it.

https://www.youtube.com/watch?v=4Fgb8qp0NxU

# ? Jul 21, 2022 00:36

HootTheOwl: May 13, 2012; Hootin and shootin

My wife says you want a pivot table.

# ? Jul 21, 2022 00:50

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

I would guess a pivot table or two with a filter for the hour number, maybe a slicer depending on how many options you have (people seem to get excited if I include slicers). It�s hard to know for sure what would work best without knowing how much data needs to be returned.

Then you just select the hour on the filter or slicer and it shows you a nice table of data.

# ? Jul 21, 2022 01:50

Rakeris: Jul 20, 2014

Harminoff posted:

I'd look into the Filter function if you have a newer version of excel. It can filter and spill all the data where you need it.

https://www.youtube.com/watch?v=4Fgb8qp0NxU

Anytime I see these new functions, I get annoyed that my work has not upgraded us to desktop O365 yet (we can only use the web versions), so many nice changes in excel.

# ? Jul 21, 2022 04:14

Harminoff: Oct 24, 2005; ð½

Rakeris posted:

Anytime I see these new functions, I get annoyed that my work has not upgraded us to desktop O365 yet (we can only use the web versions), so many nice changes in excel.

drat so no xlookups? I'd dread having to go back to vlookups and index/match

# ? Jul 21, 2022 05:36

El Mero Mero: Oct 13, 2001

Lib and let die posted:

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.

Depends what you're trying to do, but powerquery if you need to clean/shape the date or access (lol) to query and write back to it.

# ? Jul 21, 2022 07:09

Rakeris: Jul 20, 2014

Harminoff posted:

drat so no xlookups? I'd dread having to go back to vlookups and index/match

Nope....indexing and matching over here. When I first saw xlookup I was like holy poo poo that is awesome! Then found out a few minutes later I couldn't use it hah.

# ? Jul 21, 2022 11:28

Looten Plunder: Jul 11, 2006; Grimey Drawer

I'm creating an agent performance dashboard at work. I run a report and paste the data in excel then have a dashboard that summarises it by employee. I have a bunch of rules that filters based on the employee name that's input into cell B3. I currently have the following macro for filtering a pivot table based on the employee name in B3 to show the assessments that were completed for them

code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("B3:B3")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("SummarySheet").PivotTables("EvalSummary")
    Set xPFile = xPTable.PivotFields("Agent Name")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub

Currently, if I don't have an employee name in cell B3 it displays every assessment. How do I alter that macro to filter to none unless an employee name has been input?

# ? Jul 21, 2022 12:24

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 05:47

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

I started using xlookup recently and it is really great, I do kind of like index match but I�m never going to use it again.

On new functionality, I got sent a spreadsheet with the new lambda function in it the other day, it�s a neat idea but our current enterprise version of Excel doesn�t support lambda function so the spreadsheet barely opened and then just hung �calculating�. I�m still waiting for a response to �if you want us to use this spreadsheet you need to make it compatible with our Excel�.

If anyone hasn�t seen it: Lambda function.

# ? Jul 21, 2022 12:40

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

«‹›82 »