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

«‹›2 »

esquilax: Jan 3, 2003

schmagekie posted:

You can take the column of what you're trying to grab. =VLOOKUP($A2,Data!$A:$J,COLUMN(Data!B2),0)
e: assuming the lookup range starts in col 1, otherwise you'll have to do a little thinking.

=VLOOKUP($A2,Data!$C:$J,COLUMN(Data!D2)-COLUMN(Data!$C2)+1,FALSE)

The +1 is necessary.

Or just use Index & Match.

# ¿ Sep 23, 2014 17:54

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 06:15

esquilax: Jan 3, 2003

So I have a semi-advanced question.

We've been maintaining an .xlsm workbook, and some of the tabs in the worksheet are "Very Hidden". That is, we set the tab properties in VBA to have visibility 2. Users can pull data from a "very hidden" sheet, and it's completely unencrypted, but you can't actually unhide the sheet and see it. There is a password on the VBA project (so you can't unhide via VBA), but the workbook is otherwise unprotected.

The person who maintained the sheet left the company, and he was the only one who actually knew the VBA password.

Is there a way to unhide these sheets and/or crack the VBA password to make the workbook useful again? I have a feeling it's possible since the contents of the hidden sheets are completely unencrypted.

# ¿ Feb 4, 2015 22:52

esquilax: Jan 3, 2003

ShimaTetsuo posted:

Unless I did it wrong just now, can't you just open a new workbook, and run code in the new workbook to unhide the sheet in your first workbook? It seems to work fine.

Yeah this worked. Thanks!

# ¿ Feb 5, 2015 14:04

esquilax: Jan 3, 2003

kapalama posted:

Excel formatting question:

This is an order sheet.

I want to keep a running total that is only displayed when the total actually increases from a line total. Not really used to doing this, but I got it to work OK as long as I have two dummy rows to start things, but of course it sums the column, instead of updating the the value. How do I get this to work so that every time a row causes an increase in the running order total it adds to the running total and displays the updated total? Obviously I could have zero appear in a bunch of rows but it makes reading the order sheet hard

=IF((F4>0),(F4+SUM(G$2:G3)),"") first guess gets the blanks right, but of course just sums all the displayed values.

This might be easy, but I have never really needed to do this before.

Do you have any issues using conditional formatting? You can have the formula in your cells equal the running total in each row, then set the font of the text to white when it's the same as the row above it in order to make it invisible.

# ¿ Sep 23, 2015 17:05

esquilax: Jan 3, 2003

Turkeybone posted:

Above, I would like the blue data bar to represent percentage completion (the first row is 33 thingees accomplished out of a goal of 78 thingees, so the bar should be 42% of the way across the cell). Right now the data bars how big that number is in reference to the others in the column. I feel like there should be a way to go about this (I can even hide the %s in some other column, but I'm not sure how to overlay those percentages over this number). Any suggestions?

You're using conditional formatting data bars for this?

Both of the solutions that I can think of either require VBA or are very messy.

(1) You could have the top row fill up 42% of the way by setting the minimum bar length to Number=0 and the maximum to Number = 78/(33/78). This makes it think the maximum bar length is 184.36, and 78 is 42% of this which leads to the bar being filled up 42%.

However, due to the limitations of excel it doesn't appear you can use relative references for width of the bars, so this conditional format only works for a single row you specify. You could use a macro to set up a conditional format for each cell using the function:
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:= [insert a formula here]

(2) Another option is to split up your second column into 100 columns that are 1-2 pixels wide, then conditionally format them that way.

# ¿ Oct 15, 2015 22:34

esquilax: Jan 3, 2003

Turkeybone posted:

Yeah, it's going to change as progress occurs each day, so while those are all cool answers, it's going to fall into "too much work for this one thing." Too bad I can't use relative references for it.

You could modify the macro solution I posted to update automatically whenever the sheet updates. It would take a VBA expert like 15 minutes to make the whole thing, but even a beginner should be able to get it done within a few hours. It's up to you whether that's worth it.

# ¿ Oct 16, 2015 14:06

esquilax: Jan 3, 2003

nielsm posted:

I have a bunch of data items, each item has a starting time and an ending time, but otherwise there isn't any particular distinguishing features or values of each item. There is overlap between items. I'd like to make some sort of visualization of this, that emphasizes the overlapping.

Any suggestions for ways to accomplish this, inside or outside Excel?

(I could probably hack up some VBA myself, or even make a proper C# program, but would prefer not to.)

If you don't have too many points, like maybe under 100, a Gantt Chart might work.

You could also make a line graph, with the height being the number of simultaneous projects going on.

Or you could combine the two of them into one chart.

# ¿ Oct 26, 2015 16:35

esquilax: Jan 3, 2003

raej posted:

Another weird one. I have a cell that's calculating a duration based on GetCurrentTimeEST()-<another cell>. When I change anything on the other cell, the formula recalculates (good). When I change any other cell on the form, the duration does not re-calculate (bad).

Is there an easy way to update this calculation when ANY cell changes value?

Mark the custom formula GetCurrentTimeEST() as volatile.

I believe you just put "Application.Volatile" in the function code.

# ¿ Nov 19, 2015 23:36

esquilax: Jan 3, 2003

Xandu posted:

Anyone ever taken an excel test for a job interview? I know pivot tables and vlookups and I'm generally pretty competent in excel outside of VBA, but I'm not really sure what to expect. Any particular things to study up on?

Hard to say without more context, but these are often pretty important in an analysis position:
Autofilter and sorting
Absolute vs. relative references
SUMIF() and COUNTIF()
The basics of SUMPRODUCT() and array formulas
Named ranges
Goal seek
(If it's financial) A lot of the financial functions, particularly PMT(), PV(), FV(), IRR(), NPV()

Also formatting - if they want an example work product instead of just an answer make all your results clean and readable

If I were performing the test I would also be watching you to see if you utilize keyboard shortcuts or navigate with the mouse (more shortcuts, less mouse is better).

# ¿ Apr 7, 2016 18:43

esquilax: Jan 3, 2003

Squashy Nipples posted:

I would argue that there are VERY few things that you can do with Array Formulas that you can't do with CountIf(s), SumIf(s), and the like. That said, knowing how to use Array Formulas is still impressive.

I would also be impressed with someone who still uses the old school (pre-ribbon) shortcut keys.

TRANSPOSE() is super useful and MMULT() sometimes is a time saver.

The really useful thing that array formulas can do is a custom operation on the subset of numbers using custom criteria - such as finding the median, max, or min, or using the LARGE() and SMALL() functions.

Array formulas are also easier to update and maintain than COUNTIFS() and SUMIFS() since array formulas can use regular IF() statement logic instead of being forced to use kludgy text based formulas like
=SUMIF(A:A, ">"& C5, B:B)

When you could use
{=SUM( IF(A:A>C5, B:B, 0) )}

# ¿ Apr 7, 2016 20:08

esquilax: Jan 3, 2003

Alfalfa posted:

I believe that works perfectly.

back to my first question now. When I change formula E2:T2 to $E2:T2 and then add new column on E, formula will move to $F2:U2 still.

It's a bit hacky, but you can
1) Change your fomula to D2:T2
2) Put something blank or arbitrary in column D that won't impact your formulas
2) Hide Column D
3) When you insert columns, insert before column E

The hard part is step 2, and making sure that whatever you put in column D won't affect your formulas.

# ¿ Apr 18, 2016 16:33

esquilax: Jan 3, 2003

huhu posted:

I have a web app that exports CSV files for the end user to download and print. However when they open the CSV file in Excel it strips off zeros for float values, I'd like there to be column widths, etc. Obviously that's not how CSV works. Is there a way to setup some kind of template for importing CSVs or am I better off having Python spit out Excel files instead which have already been formatted?

You can write a macro that imports a CSV, keeps leading 0's and formats the sheets correctly, but you'd have to train users to push a custom button and navigate to the file instead of just double clicking on it.

If you record a macro using the Data->From Text wizard it'll give you a good idea of the commands you need.

esquilax fucked around with this message at 19:37 on Jul 21, 2017

# ¿ Jul 21, 2017 19:35

esquilax: Jan 3, 2003

ShimaTetsuo posted:

Good luck convincing your users to run some random VBA code on their machine. It's also completely pointless: you already have code as part of your web app (in Python or whatever) that generates the CSV file, why would you go and create a second application in VBA to finish the job? Just output the file in one step.

I assumed by the statement "template for importing CSVs" that he had some leeway in how users use the program. I certainly have sent random VBA code to clients to help them format worksheets.

# ¿ Jul 21, 2017 20:53

esquilax: Jan 3, 2003

Sad Panda posted:

A student gets a level 9, their below target is 1-4, good is 5-6 and excellent is 7-9. The spreadsheet should see this and say "Excellent". I've come up with 2 solutions for this so far.

Solution 1. I managed to solve this using 3 bonus columns that just use LEFT() to get the 1/5/7 from those target ranges and then an INDEX(MATCH()) to look it up. See attached pic.

Solution 2. I took an average of the good column (so 5.5 in the above example). I then created a table combined with a Vlookup to see if they were above/on/below target.

Is there a better way that doesn't involve creating those extra columns? I asked some people and they just keep saying "Use a VLookup" but don't explain it. I'm pretty sure that Excel doesn't know that 1-4 means the numbers 1, 2, 3, 4 inclusive which would mean that a VLookup without my bonus columns from the attached picture wouldn't work.

If you use a vlookup with "true" instead of "false" then it will match the highest value that doesn't exceed your lookup number. So a table like:

0 Below Target
5 Good
7 Excellent

A vlookup will take a numeric score like 5.25, and will look it up on the table to output the words.

You could also do this via an index(match()) with a match type of 1, sort of like how you did. But generally just having a table in a hidden column off to the right is the best move here.

esquilax fucked around with this message at 18:58 on Apr 7, 2019

# ¿ Apr 7, 2019 18:54

esquilax: Jan 3, 2003

I have a group of data. I want to round each number to the nearest whole number, but also maintain the sum. This is often mathematically impossible but I'm trying my best.

e.g. my data set totals to 14 and is:

code:

2.8
4.4
4.35
2.45

Rounding each number individually would give me 13, and it needs to total to 14. I would like to for the results to somehow be rounded to:

code:

3 (2.8 rounds up)
4 (4.4 rounds down)
4 (4.35 rounds down)
3 (2.45 rounds up, due to having the largest fraction and the need to sum to 14)

I am able to make a rounding algorithm that does this via rounddown() and rank(), but it requires several steps and implementing tiebreaker criteria, and would be a pain to include any flexibility. Is there any easier way, or any functionality already baked into excel?

# ¿ Oct 9, 2019 21:19

esquilax: Jan 3, 2003

Because 1) the calculations need to work off of the rounded numbers, and 2) the little numbers that are visually shown on the page need to add up to the big number that is shown on the page, otherwise people get mad that the numbers don't add up.

# ¿ Oct 10, 2019 03:25

esquilax: Jan 3, 2003

fosborb posted:

oh!, then array formulas are your friend

{=SUM(ROUND(A1:A4,0))}

The total needs to remain unchanged.

For background, I have a group of people that are migrating into different buckets. There are (for example) 100 people in the initial sample, and I want a third to go to each of three buckets. Normally this would result in 33.33 (repeating of course) people going into each bucket. Normally I am ok talking about partial people, but but as a requirement of this model, partial people do not exist. So I need to make sure that the model can automatically put 34 in one bucket and 33 in the other two buckets so that we don't lose anyone and the numbers add up. And I also need to make sure the calculations that work off of those migrated counts use the 34/33/33 numbers and not off of 33.333 people.

And this model will be used going forward, so it needs to be flexible to work with an variable number of buckets, variable migration assumptions, and a variable number of initial people.

I have already made an algorithm to do this via a 5 step process, but am wondering if there's a better way to do it that would have been easier, or is less computationally intensive.

# ¿ Oct 10, 2019 03:43

esquilax: Jan 3, 2003

fosborb posted:

Is the sum of the original numbers always going to equal a whole number?

Yes. I am starting with a whole number of people and it will end up as a whole number of people.

# ¿ Oct 10, 2019 03:53

esquilax: Jan 3, 2003

Jethro posted:

Are you just trying to determine how many whole people to put in each bucket, or are you also doing calculations that need to do weird rounding? Like, your first example was an arbitrary set of numbers that happened to total to a whole number, but then you pivoted to putting M people in N buckets of approximately size M/N. If you're just bucketing, and you want the buckets to all be as close to equal as possible, then you have MOD(People,Buckets) Buckets of size CEILING.MATH(People/Buckets) and (Buckets-MOD(People,Buckets)) Buckets of size FLOOR.MATH(People/Buckets).

Or does "variable migration assumptions" mean the buckets can be any size?

It means the buckets can be any size. The second example was a simplified version.

In my initial example it started with 14 people (calculated based on a data intake, but it will always be a whole number) and the migration assumptions were 20.0% /31.429% /31.071% /17.5% (input by the user, in normal usage will typically be a multiple of 5%, from 0% to 100%, and it will always sum to 100%).

This was my solution which can be generalized. Paste into cell A1, and do text-to-columns using the vertical bar delimiter "|". In the actual model B2:B5 would be formula driven.

code:

|Initial Count|Dropped Decimals|Remainder|Arbitrary tiebreaker/highest first|remainder w tiebeaker|Rank|Number to add|New Total
Bucket 1|2.8|=ROUNDDOWN(B2,0)|=B2-C2|4|=D2+E2/(1000)|=RANK(F2,$F$2:$F$5, 0)|=IF(G2<=$C$7, 1, 0)|=C2+H2
Bucket 2|4.4|=ROUNDDOWN(B3,0)|=B3-C3|3|=D3+E3/(1000)|=RANK(F3,$F$2:$F$5, 0)|=IF(G3<=$C$7, 1, 0)|=C3+H3
Bucket 3|4.35|=ROUNDDOWN(B4,0)|=B4-C4|2|=D4+E4/(1000)|=RANK(F4,$F$2:$F$5, 0)|=IF(G4<=$C$7, 1, 0)|=C4+H4
Bucket 4|2.45|=ROUNDDOWN(B5,0)|=B5-C5|1|=D5+E5/(1000)|=RANK(F5,$F$2:$F$5, 0)|=IF(G5<=$C$7, 1, 0)|=C5+H5
|=SUM(B2:B5)|=SUM(C2:C5)||||||
|Additional people to add|=B6-C6||||||

New question - is there an easier way to pass along excel cell coding on SA other than this? The forums don't play nice with tab-delimited data and comma-delimited data doesn't work because my formulas have commas in them.

# ¿ Oct 10, 2019 18:56

esquilax: Jan 3, 2003

disaster pastor posted:

I'm currently using a very basic formula for weighted averages: (B2*0.1)+(C2*0.1)+(D2*0.1)+(E2*0.2)+(F2*0.25)+(J2*0.25)

This is for predictions, and I'd like to run a Monte Carlo experiment on it: do it, say, 5000 times with different weights. The trouble is I can't figure out how to constrain the weights. At the very least, I'd need them all to sum to 1, and I can't do that by just multiplying by RAND(). I could approximate it by doing RANDBETWEEN() (and dividing by 100) but that could still be quite a bit off in either direction.

I searched online and most of the solutions I found were to make five of them random and the sixth 1 minus the sum of the other multipliers, which won't work for me; not only am I not sure offhand how to write that formula so that it works 5000 times, but also, if I'm doing this 5000 times, many of those 1-SUMs will end up negative.

Is the RANDBETWEEN method my best option, or is there a smarter way to do this?

The quick and dirty method is to generate each random number individually then scale them all together at the end to sum to one.

E.g. You generate a "first set" of random numbers based on your chosen method.
[0.08, 0.1, 0.05, 0.2, 0.25, 0.3]
Which sums to 0.98. Divide each element by 0.98 to generate a second set of random numbers:
[0.816, 0.102 ...]

Then you ignore the first set of random numbers (which doesn't sum to 1), and use the second set (which does to 1) as your weights.

The way to implement it in excel is to use a bunch of columns off to the right for each row, or use a macro.

I couldn't gather from your post whether each weight is uniform from 0-100% or if it is constrained in some way (e.g. weight on B is expected to be normal with mean 0.1 and stdev 0.02) but this method works under any approach to generating the random weights.

Note that this is not mathematically rigorous and probably would not fly in an academic setting - it takes whatever probability distributions you start out with and "tweaks" them in order to fit into the constraints.

# ¿ Sep 28, 2020 14:56

esquilax: Jan 3, 2003

In that case, one caveat from my method is that the tweaks it does to probability distributions might generate weights outside of your range. E.g. if your randbetween() is designed to constrain the B weight randomly between 0.05 and 0.15, it might end up using something like 0.04 or 0.18 (which would be outside of the 0.05 to 0.15 range). It sounds like that's ok based on what you've said.

# ¿ Sep 28, 2020 17:42

esquilax: Jan 3, 2003

me your dad posted:

I'm losing my mind with dates. I have a column displaying dates in mm/dd/yyyy.

I am trying to split them into three columns, using the slash as delimiter.

No matter what I try, the result is three columns that display:
code:
1/1/1900         1/3/1900       7/12/1905
That's what 1/3/2020 gets converted to.

What the hell am I doing wrong? I have the date column formatted as date, along with the columns next to it. When I do text to columns, I am specifying each column during that operation to be formatted date. Nothing seems to work.

I have tried moving the date column to another column in the spreadsheet, I have tried bringing the column into Notepad++ to strip any formatting and pasting it back in.

Excel views the values 1, 3, and 2020 as those dates. You can use the day(), month() and year() functions as previously suggested, or just change the formatting on those columns.

To do this, you can use the Home->Number dropdown menu to on those three columns change the format from "Date" to either "Number" or "General". The shortcut for this is Ctrl+Shift+~

# ¿ Nov 3, 2020 18:02

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

# ¿ Feb 2, 2022 20:42

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

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

esquilax: Jan 3, 2003

it dont matter posted:

Can I ask a Google Sheets question here?

I've got two columns, A & B. Both have drop-down menus with YES or NO.

Can I use conditional formatting to colour B red only when A=YES and B=NO ?

Yes.

In the conditional formula menu, choose "Custom Formula Is" and set it to something like: =and((A2="YES"),(B2="NO"))

And the range should be your data in Column B. The custom formula you use should be the relevant formula for the top left cell of the conditional formatting range - the formula I wrote above was if cell B2 is the top of your data range.

esquilax fucked around with this message at 14:37 on Oct 25, 2022

# ¿ Oct 25, 2022 14:34

esquilax: Jan 3, 2003

it dont matter posted:

Yes that worked, thank you.

What if I also want B to turn red if it's empty? Would that be something with ISBLANK ?

You can use ISBLANK(B2) or something like: B2=""

You could implement that either by rewriting the first conditional formula to account for both No and [blank], or add a second conditional formatting formula.

# ¿ Oct 25, 2022 15:23

esquilax: Jan 3, 2003

Lib and let die posted:

Here's an incredibly dumb question:

Can I resize all my columns by just the length of the column header? I seem to recall doing that somehow in the past but I can't figure it out now.

grumble grumble loving salesforce export files

Highlight the cells you want to fit the columns to.

Home->Cells->Format->Autofit Column Width
Alt->h->o->i is the alt key shortcut

I think it messes up if you use word wrap.

# ¿ Nov 8, 2022 21:23

esquilax: Jan 3, 2003

FreshFeesh posted:

I would like to average all numbers where any of the following columns contain particular words, but my use of AVERAGEIF was giving me results that were way off (divide by zero errors, answers 1/4th expected values, et cetera). Is there a reasonable/rational way to match/average values based on the contents of multiple columns?

Sample Data:
code:
Number	Result
----------------------------
119	Re-test			
107	Pass	  2nd Pass	Re-test	
185	Re-test	  Rejected
135	Re-test			
175	Pass	  2nd Pass	3rd Pass	4th Pass
375	Pass	  Rejected		
189	Rejected
207	Re-test	  Pass 
Desired Output:
code:
Result		Average
----------------------------
Pass		216
2nd Pass	141
3rd Pass	175
Re-test		144
Rejected	250
It is intentional that if something matches multiple states (Pass, Rejected, whatnot) that the entry gets added into the average for each discrete result

In general, instead of AVERAGEIF you are probably better off doing a SUMIF divided by a COUNTIF. Does this solve your problem? It should only return a Div/0 error when you try to average a Result that doesn't exist in the data.

There are other ways that are a little more scalable but this is probably simplest with what you have already built.

# ¿ May 4, 2023 16:28

esquilax: Jan 3, 2003

FreshFeesh posted:

Unfortunately SUMIF provides weird results too when I call it across a range of columns.

In this example I would expect the answer to be 0, as the only line which contains the target text doesn't have a value in the sum field
code:
A	B	C		D
----------------------------------------------
NULL	Pass	2nd Pass	Timed Out

=SUMIF(B:D,"Timed Out",A:A)
44.998
Edit: Thanks for the deep dive, HootTheOwl. I'll give that a go

Oh, I was assuming you were already using a formula for each column on your AVERAGEIF functions the way that HootTheOwl is suggesting that you do. Yeah neither AVERAGEIF or SUMIF play nicely wth a criteria array that is a different size than the sum array.

# ¿ May 4, 2023 16:54

esquilax: Jan 3, 2003

You can use the =INDIRECT() function, and all you need to do is put your tab names in the first column, either via formula or any other method. The =INDIRECT() formulas should also all copy down fine.

code:

1	=INDIRECT(A1&"!B32")	=INDIRECT(A1&"!C32")
2	=INDIRECT(A2&"!B32")	=INDIRECT(A2&"!C32")
3	=INDIRECT(A3&"!B32")	=INDIRECT(A3&"!C32")

# ¿ Jun 22, 2023 01:35

esquilax: Jan 3, 2003

Ideal Paradigm posted:

Is there a way to filter a set of values from one sheet and apply it to two other sheets in the same workbook?

I have an excel workbook that has three sheets that have around 10,000 rows of values. The first column are anonymized identification numbers along with different values attached to those ID numbers in the neighboring columns (same row). For example, there would be an ID number in the first column, and then attached values such as favorite ice cream flavor in the second column, and then favorite animal in the third column (these aren't what are actually in the cells next to them, they're just numerical values).

The problem is that the ID numbers in the first two sheets don't all match the ID numbers in the third sheet. Hundreds to thousands of the ID numbers in the first and second sheet don't match the third sheet.

I would like to take the ID numbers from the third sheet, keep the attached values such as ice cream flavor and favorite animal, and then filter out the first and second sheet so that only ID numbers from the third sheet appear in the first and second sheet. The third sheet will be the "anchor" sheet where those are the identification numbers that matter. The first and second sheet only have some ID numbers that match the third sheet, and the first and second sheet don't necessarily match each other, there may be minimal overlap.

Is there a way to perform this type of filtering from one sheet to another? Thank you!

Are there any issues with adding in a hidden dummy column on Sheet 1 and Sheet 2 that reads Y/N based on whether the ID appears in Sheet 3?

You can do this using an If(iserror(vlookup())) formula. Vlookup returns an error, if the value you are trying to find doesn't exist in the lookup table.

# ¿ Nov 23, 2023 13:22

esquilax: Jan 3, 2003

Ideal Paradigm posted:

No, I can create a copy of the excel workbook and modify as needed apart from the original copy.

The problem is that sometimes on the third sheet, there are multiple entries for the same ID number in different rows (they're always grouped together though), since the columns next to those ID numbers will have a changed value. For instance, let's say the ID number is 1002, there may be three rows with 1002 all grouped together, one row after another, and then the three columns will have different dates since those dates signify when a measurement was taken. Then in the third column the quantity for that measurement is inputted for those ID numbers.

Your intent though is that since person with ID 1002 is listed at least once in Sheet 3, that it is a valid ID and any/all rows for person 1002 should be shown in Sheets 1 and 2? That's what the method I suggested does.

# ¿ Nov 23, 2023 22:24

esquilax: Jan 3, 2003

HootTheOwl posted:

It shouldn't be evaluating the day difference to 24, it should be evaluating it to 1. I do not know why for you it is evaluating it that way in yours.

here it is in my excel.
This behavior matches This website. Where basic subtraction is equivalent to executing the DAYS function.

Change E30 to 11/28/23. Does it still say 2 hours?

It's giving you the "right answer" because it is formatted as h:mm, which drops the number of days from the cell. If you change formatting to something custom like yyyy:mm:dd:hh:mm, you can see that the cell value is being treated in excel as January 23 1900, @ 2 AM. [h]:mm gives the elapsed number of hours.

Your formula is essentially telling excel to take 1/0/1900, add 24 days, add 1/24 days , and subtract 23/24 days.

Doing direct math on date and time values is a minefield of these kinds of errors, especially if you are importing data or manually entering it. It's best practice to use functions like hour() and do the math directly.

esquilax fucked around with this message at 16:46 on Nov 28, 2023

# ¿ Nov 28, 2023 16:31

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 06:15

esquilax: Jan 3, 2003

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.

# ¿ Mar 11, 2024 22:59

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

«‹›2 »