I sent her a file with a vlookup programmed into it, and instructions on how it works so that she can hopefully modify the range if needed.. I did think about a macro but I figured everyone should know how to do a sweet vlookup :)
― she started dancing to that (Finefinemusic), Saturday, 4 October 2014 19:46 (nine years ago) link
there has been a ticker tape parade, ffm presented with keys to the city, not a dry eye in the place
― difficult-difficult lemon-difficult (VegemiteGrrl), Saturday, 4 October 2014 23:46 (nine years ago) link
It's true! I felt like a modern day Mother Teresa.
― she started dancing to that (Finefinemusic), Sunday, 5 October 2014 00:51 (nine years ago) link
Oh man I love a good vlookup
― rad het chilly poppers (Stevie D(eux)), Sunday, 5 October 2014 00:54 (nine years ago) link
i am a fan now
― difficult-difficult lemon-difficult (VegemiteGrrl), Sunday, 5 October 2014 00:59 (nine years ago) link
Just remember to never ever merge cells. They will just screw everything up. AVOID AT ALL COSTS
― rad het chilly poppers (Stevie D(eux)), Sunday, 5 October 2014 02:30 (nine years ago) link
yessir
― difficult-difficult lemon-difficult (VegemiteGrrl), Sunday, 5 October 2014 03:31 (nine years ago) link
Xp - highlight, right click format, alignment, center across selection
boom goes the dynamite
― i'd rather be arrested by you folks than by anybody i know (art), Sunday, 5 October 2014 03:56 (nine years ago) link
Hmm, don't merge cells ever, or don't merge cells when you use vlookup? I merge cells sometimes to get a nice clean subcategorised table but it's true that things often go mysteriously screwy with merged cells around
I like a good vlookup as long as I remember to set the mysterious final parameter to FALSE, which tells it "hi, my lookup table might not necessarily be sorted, and also if a value isn't in the table then tell me and don't just go making some shit up based on the imaginary ordering you think the lookup table's in but it isn't"
not explaining that very well and hell I was bored so I messed around with Google docs and: fun with vlookup(to remind myself as much as anything tbh)
― club mate martyr (a passing spacecadet), Sunday, 5 October 2014 15:03 (nine years ago) link
I don't like merged cells with or without vlookups. I'd use in moderation though when my boss absolutely demanded a certain visual style.
god I miss all this stuff
― Drop soap, not bombs (Ste), Sunday, 5 October 2014 15:11 (nine years ago) link
I used to make a lot of reports that people typed into and merged cells were very nice there. But they can be a nuisance if copying and pasting or sorting or filtering.
― she started dancing to that (Finefinemusic), Sunday, 5 October 2014 15:45 (nine years ago) link
lol apc I think I told VG "put the last bit as false, I don't know why, just do it" ;) will check our yr google doc when not on my phone!
― she started dancing to that (Finefinemusic), Sunday, 5 October 2014 15:46 (nine years ago) link
PS I think it's nice that Google docs spreadsheets now do p. much everything I ever do in Excel, including having the same worksheet formula functions/syntax
Microsoft might not think this is nice, of course, but then given that dates are weird in Excel for the sole reason that they were weird in Lotus 1-2-3, well...
(haven't used their "scripts" so no idea how they compare to macros; haven't used their charts either but charts are p. horrible in Excel if you ask me, which you did not)
― club mate martyr (a passing spacecadet), Sunday, 5 October 2014 15:58 (nine years ago) link
Can't do text to columns in Google, my biggest complaint.
― pplains, Sunday, 5 October 2014 18:28 (nine years ago) link
As an alternative to the vlookups, there's a nifty macro to do the unpivot-ing thing here:
http://dailydoseofexcel.com/archives/tag/unpivot/
The second one (UnPivot_snb) looks like the way to go. Kind of lame that Excel doesn't have unpivot built in, IMO.
― o. nate, Monday, 6 October 2014 03:01 (nine years ago) link
Like converting data laid out in a PivotTable to a regular range? Can't you just copy and paste/paste values?
― rad het chilly poppers (Stevie D(eux)), Monday, 6 October 2014 11:58 (nine years ago) link
Probably a dumb conditional formatting question, but hey, this the idiot's thread!
My sheet is organized with clients as the header, and each row is a date, and the cells contain percentages. At the bottom of each column is a minimum %, and I want to highlight cells in a column that are less than that column's min %. Is there a way to do that with a single rule, because it seems I have to create a rule for each column to do that?
― A-Hanisi Coates (Leee), Sunday, 19 April 2015 21:56 (nine years ago) link
Select one of the cells you want to format, go to conditional formatting and choose Use a formula to determine.. then use the formula =OR(cell that you want to format < minimum value cell), make sure you use $ to fix that last cell. Use the format painter to apply the conditional formatting to the other cells.
― nate woolls, Monday, 20 April 2015 09:03 (nine years ago) link
That $ lock could get tricky if you keep adding rows to this sheet or copy format to a new column, but what Nate described is easiest method of doing this.
― One bad call from barely losing to (Alex in SF), Monday, 20 April 2015 12:00 (nine years ago) link
I don't understand why but it works! Thanks,
― A-Hanisi Coates (Leee), Monday, 20 April 2015 17:14 (nine years ago) link
I'm trying to create a timeline.
What I've got is a spreadsheet of five states and their unemployment rates for the past 12 months.
Currently, the spreadsheet is organized as:
STATE1 | JANUARY | RATESTATE2 | JANUARY | RATESTATE3 | JANUARY | RATESTATE4 | JANUARY | RATESTATE5 | JANUARY | RATESTATE1 | FEBRUARY | RATESTATE2 | FEBRUARY | RATESTATE3 | FEBRUARY | RATESTATE4 | FEBRUARY | RATESTATE5 | FEBRUARY | RATEetc.
What I would like it to look like is
____ ____ | JANUARY | FEBRUARY | MARCHSTATE1 | __RATE___ | __RATE___ | _____RATESTATE2 | __RATE___ | __RATE___ | _____RATESTATE3 | __RATE___ | __RATE___ | _____RATESTATE4 | __RATE___ | __RATE___ | _____RATESTATE5 | __RATE___ | __RATE___ | _____RATE
How could I do this?
― pplains, Wednesday, 10 February 2016 17:04 (eight years ago) link
Do you want it to be just a regular series of data? If you're ok with a PivotTable, I think that would give you exactly what you need, though they can be a bit of a pain to set up. But if you did that, you could just copy and paste the data into a regular sheet
― police patrol felt the smell of smoke and found that goat burns (Stevie D(eux)), Wednesday, 10 February 2016 17:06 (eight years ago) link
Pivot table. Add a row above each header state, month, rate. Then pivot. Add state to row, month to column, and sum the rate.
― One bad call from barely losing to (Alex in SF), Wednesday, 10 February 2016 17:09 (eight years ago) link
Oh dear Lord, that did it. Thank you both.
― pplains, Wednesday, 10 February 2016 19:00 (eight years ago) link
Does anyone understand the fourth/sorted?/last argument for vlookup? I don't think I've ever encountered a situation where setting it to true led to correct results.
― write sed fread (Leee), Thursday, 8 September 2016 19:43 (seven years ago) link
tbh I mainly just forget to set it to False by accident and then get stupid results but this is my understanding:
if you set it to True (which is sadly the default) it only works correctly on a sorted list, and if the value doesn't appear in the list it returns the info for the "closest" match (the highest value < or == the value you typed in iirc, so if you search for 99 and it has rows with 70 and 100 but not 99 it'll give you the data for 70 instead of 100)
this dates back to when computers were just not powerful enough to search through an unsorted list in a sensible amount of time, so I guess it used to be the only option and now it's the default for reasons of backward compatibility
it runs faster in this mode but for a non-sorted list the results are completely wacky, and even with a sorted list most of the time I'd rather know that the value I'm looking for isn't there, because I mainly use it as a crude database: what is the data for the exact record with this ID?
but I guess this use would have worked for something like a ready reckoner, where you want to look up (say) which city you could get to with 15 gallons of gas, and it looks at the sorted distance/city table and says "well, San Diego is 14 gallons away, how about that for a day trip"
(uhh I'm bad at examples, sorry. there must be a much more convincing real world example. I have actually had a couple of occasions where it was a workable option but I can't remember what they were)
― a passing spacecadet, Thursday, 8 September 2016 20:50 (seven years ago) link
use index match not vlookup
― flopson, Thursday, 8 September 2016 21:08 (seven years ago) link
(imperative)
― flopson, Thursday, 8 September 2016 21:09 (seven years ago) link
but yea spacecadet has got it iirc. in index match the last argument is 0 for exact, 1 for nearest lowest, -1 nearest at most as large, +1 nearest at least as large
― flopson, Thursday, 8 September 2016 21:12 (seven years ago) link
yeah index match is certainly more efficient once you start to go beyond a couple hundred rows
― laraaji p. henson (Stevie D(eux)), Friday, 9 September 2016 01:41 (seven years ago) link
A good reason for INDEX MATCH over VLOOKUP or HLOOKUP is you can search right->left or bottom->top
― nate woolls, Friday, 9 September 2016 07:39 (seven years ago) link
also you can look up arrays (although this gets slow fast)
― flopson, Friday, 9 September 2016 13:58 (seven years ago) link
ugh fuck arrays unless you absolutely positively need them
― laraaji p. henson (Stevie D(eux)), Friday, 9 September 2016 16:04 (seven years ago) link
Learnt double vlookup, never looked back.
http://dailydoseofexcel.com/archives/2015/04/23/how-much-faster-is-the-double-vlookup-trick/
― barbarian radge (NotEnough), Friday, 9 September 2016 16:20 (seven years ago) link
would love to encourage all Excel users advanced enough to be doing lookups to dabble in some scripting, I've been suggesting https://automatetheboringstuff.com to people
― slathered in cream and covered with stickers (silby), Friday, 9 September 2016 16:21 (seven years ago) link
i've googled my problem but it hasn't helped, hope maybe someone here can help - for the last few days when i try to open spotify, in both chrome and IE, i get a error message. not on my 'puter r/n so i'm being unintentionally vague. but it's some kind of message mentioning problem with ssl (whatever that means). i cleared ssl, and deleted cache, as i saw that recommended but that didn't change matters. . . ?
― Bandol soleil for the St. Tropez tan (outdoor_miner), Friday, 9 September 2016 16:30 (seven years ago) link
xp- i've automated most of the tedium out of my job with R :-)
― flopson, Friday, 9 September 2016 17:02 (seven years ago) link
I hesitate to even ask. Apparently, I have an impossible task, and the messageboards peppered with spreadsheet quarterbacks aren't helping.
I have, like, 5000 Web page titles in one column and the number of pageviews for each in another. I am looking for the average number of pageviews culminated from the list.
But I can't just SUM it up and divide it by 5000 because some of the page titles are duplicates or at least paternal twins of others. For example "The Circus is Coming to Town", "UPDATE: The Circus is Coming to Town", and "The Cicus is Coming to Town" all have different number of pageviews, but they all really only belong to one story (that had its title changed a few times.)
What I've been doing is slowly going down the list sorted alphabetically (I've F+R'ed the UPDATES: with nothing, but the stories are still there.) When I come to a bunch, I highlight the two or four cells with the pageviews in Column B, click on SUM, and keep moving.
After I've gone through 500 or so, I scroll all the way down to the end of the rows to where the sums have been appearing. Now I've got 500 cells in Column B with formulas in them that read things like "=SUM(B235:B237)". So that I know which article each cell corresponds to, I input =A237 or whatever the last three numbers were from Column B. That way, the title of the last story will appear there.
My plan after this will be to take this section of =Axxx | =SUM(Byyy:Bxxx, paste it into a new document and get rid of the formulas. Then adding that back to the original lineup. I will have an extra article now with each bunch, but then I can delete the others (whichever ones don't have the highest number), and THEN get my average.
There MUST be a different way to do this though. And as it is now, it is a PAIN IN THE ASS to just create the A column since I have to manually type out the information on each one.
If this thread doesn't get updated again for another two-and-a-half years, I'll understand.
― pplains, Friday, 18 January 2019 02:02 (five years ago) link
I’ll just do that for you for $100
― I have measured out my life in coffee shop loyalty cards (silby), Friday, 18 January 2019 02:06 (five years ago) link
Ha.
If there's no way out of this, I'd also be happy to hear some appropriate playlist recommendations.
― pplains, Friday, 18 January 2019 02:12 (five years ago) link
i'm trash at excel but my first thought would be to add a new column and use a formula to search keywords from column A, trying to tag rows that you can sum up later. i'm curious about how many rows out of the 5000 are realistically dupes.
― call all destroyer, Friday, 18 January 2019 02:19 (five years ago) link
A lot.
Much of it has to do with the header that appears at the top of the browser, which is different depending upon whether someone hits the site from an enewsletter, from Google, or from an internal page.
There aren't that many headlines that get changed, but enough to keep me off-balance.
― pplains, Friday, 18 January 2019 02:54 (five years ago) link
yeah, i would be trying to pull keywords from the headlines and dumping them into a 3rd column
― call all destroyer, Friday, 18 January 2019 03:03 (five years ago) link
I dunno if this is of help, but it might ease the process a little bitAssuming a file ala
col A | col b-------------LOL | 50LOL! | 100ROFL | 50
If "LOL" and "LOL!" are the same page, then average pageviews should be 100.What you could do is delete the value in col A for one of the duplicates, and find unique pages with COUNTA ( cf https://support.office.com/en-us/article/counta-function-7dc98875-d5c1-46f1-9a82-53f3219e2509 )
-------------
LOL | 50 | 100ROFL | 50
Then you can write something like
total page views = sum(B1:B3)unique pages = COUNTA(A1:A3)Average = total / unique
Of course, this still means you have to go through the whole document and hit delete on any field that contains what you believe to be a duplicated page.But at least you wouldn't have to write individual SUMs for various subsets etc.
― Øystein, Friday, 18 January 2019 12:06 (five years ago) link
Holy cats, Øystein, I think that's going to do the trick.
I had spent three days doing it the other way and had gotten midway through the C's. In about two hours, I'm between the M's and the N's. using your method.
From A2 to A2804, I had 1,804 actual (unique) stories.
Thank you so much for the help.
― pplains, Friday, 18 January 2019 23:12 (five years ago) link
Cool, I'm happy to hear it
― Øystein, Saturday, 19 January 2019 09:40 (five years ago) link
ANNOUNCE XLOOKUP
https://techcommunity.microsoft.com/t5/Excel-Blog/Announcing-XLOOKUP/ba-p/811376
― sktsh, Wednesday, 4 September 2019 16:19 (four years ago) link
omg
― Seany's too Dyche to mention (jim in vancouver), Wednesday, 4 September 2019 19:07 (four years ago) link
index/match shurely
― theRZA the JZA and the NDB (darraghmac), Wednesday, 4 September 2019 19:51 (four years ago) link
i guess i am still definitely a nerd because i actually am excited by this news
― Nhex, Thursday, 5 September 2019 05:25 (four years ago) link