computer/programming/excel wizards! help an idiot!

#!perl
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
my $startcol = 2;
my $endcol = 157;
my $startrow = 1;
my $endrow = 155;
my $fn = "filename";
my $excel = Win32::OLE->new ('Excel.Application','Quit');
my $wb = $excel->Workbooks->Open ($fn, 2, 1);
my $sheet = $wb->Worksheets (1);
my $row = $startrow;
while ($row <= $endrow) {
  if ($sheet->Cells ($row, 2) =~ /Sig/) {
    my $col = $startcol;
    while ($col <= $endcol) {
      if ($sheet->Cells ($row, $col)->{Value} < 0.05) {
        $sheet->Cells ($row, $col)->{Font}{Bold} = 1;
      }
      ++$col;
    }
  }
  ++$row;
}
$wb->Save ();
$wb->Close ();

if ($sheet->Cells ($row, 2) =~ /Sig/) {

should probably be

if ($sheet->Cells ($row, 2)->{Value} =~ /Sig/) {

(well, I did say I hadn't tested it)

From that, it's easy enough to derive a mean. But how can I get a standard deviation or use chi squared? (NB: This isn't MY spreadsheet or my data. I'm doing this as a favour in the hope I can cash in payback LATER, or summat). Is there a way of getting this without extrapolating the original data per question out of the summary?

The spreadsheet looks like this, or part of it, anyway.

hey so uh

Is there a way to append part of one sheet in a workbook to another specific sheet in another workbook? Batchstyle?

i seem to remember something from training once, but i don't remember any more than that i think it's possible. sorry.

ARE YOU TRYING TO HURT ME

=hurt(tracer a1:g75)

http://www.ozgrid.com/forum/showthread.php?t=42484

might or might not help. stay friendly, you.

ok thanks i'll check it out

i also found these:

http://www.rondebruin.nl/ado.htm

and

http://www.rondebruin.nl/merge.htm

mega basic question, sorry! i have an excel file saved to my desktop with about 10 different sheets contained in it, full of various notes, lists and databases. is it possible to use a search function to search through every single column in all the sheets at once? it seems at the moment i can only (using ctrl + f) search one single column on one single page, is there a way around this?

You can search an entire worksheet at once - are you selecting the column before you search? Don't do that - click on an empty cell anywhere on the worksheet, then ctrl-F. Not sure how to search the whole workbook at once though.

Select all the tabs.

Okay, here's how to search the whole workbook: Click just a single cell in the visible worksheet (don't select a column or row or section of either). Hold down the ctrl key and click each worksheet tab - the selected tabs will turn white. Now ctrl-f and Find All. It will find all instances across the selected worksheets.

(xpost)

RIGHTCLICK ON A TAB
SELECT ALL TABS
CTRL-F

XPOST

xxxxp Also select all the tabs. Don't forget that step.

great stuff, that's just saved me a lot of grief, thanks!

Hi why does excel 2007 run so shittily on my pc?

Things I can think of:

-My hard drive is pretty full, 17gb free of 455gb
-It's the blu edition of office

Basic cell formatting, using basic formulas etc takes about 5-10 seconds to process, so fucking annoying..

bump. seriously it works way better on my work pc which is way shitter. maybe it's becuase my home pc has vista on it?

your pc probably has spyware etc

I don't think so, everything else seems to run well..

other office apps slow?

do any quantitative visualization geeks familiar with outdated versions of microsoft spreadsheet software have any idea why excel (2003, unfortunately) won't just draw lines between points in this line graph? i mean, i know it's because there are gaps in every other column for each of the four rows, because there were lines before in previous gapless versions, but is there a way around it? i've exhausted all my right-click-on-the-graph options, and i refuse to settle for a lineless line graph.

http://img14.imageshack.us/img14/1455/avganoz.jpg

Excel 2007 lets you change how it handles empty cells, including the option to "Connect data points with line" which would solve your problem... however I can;t remember if there was a similar box in 2003.

oh, here you go

http://office.microsoft.com/en-us/excel-help/clarify-and-emphasize-data-in-an-excel-chart-HA001034608.aspx

Section on "Control how empty cells are plotted" looks like it does the trick?

yes, that did it! just had to check "interpolated." thank you!

I'm using Excel 2007 (for the first time after sticking to 2003 for as long as possible) on a Mac. Trying to do a Pivot Table (and PivotChart, with filter, but that seems impossible?), which I've done, but I can't get the row headings to filter. In Excel 2003 (on PC, anyway) there's an automatic filter button on the headings, so you can adjust your table to include just summed data of one type, then easily change it to view other types (e.g. different days). I can't find a way to get the filter drop-down button to appear. Can anyone help? I don't know if it's 2007 being awkward or the fact it's a Mac, which I'm new to. Googling seems to imply that I should be able to filter Pivot Table headings as normal.

I think you mean Excel 2008. When I double-click the row or column headers (the silver box with the name of the field) I get a dialog box that lets me select values to hide. Does that work for you?

Excel 2008, sorry yes. Double-clicking the silver bar headings gives me Pivot Table field settings, with the option to 'hide' a set of data but this isn't quite what I'm looking for as there doesn't seem to be an easy way of switching between the filtered data.

Yeah, not ideal. If you figure out a better way I'd be interested to know!

please help!

why sometimes when i put in a formula in a cell, and then i either double click the corner or drag down to repeat cell multiple times, does it not auto calculate the correct results, it just copies the same result from the first cell?

sorted, clue was in the question. for some reason Calculations were set to manual. i didn't even know that was an option

ALERT: IDIOT ASKING A QUESTION:

How can I do a running total, like in a check register? For example:

100
+1=====101
-10====91
+5=====96
and so on.

+1 101
-10 91

assuming this is the top left corner
you would want the 91 cell to be "=B1+A2" then you could copy and drag that it down the column and it would add up the rest

My name is Lex and I approve this message.

My favourite formula of late is like:

{=SUM(IF(F3:F3648=F3651,I3:I3648,0))}*

Which checks F3651 to get the value, looks for all instances of that value in F3:F3648, and then sums up the values in I3:13648 that correspond to matches in the F column. Useful in my case for plotting sales of certain brands - I can say how much Brand A sold, BRand B, etc simply by populating the I column with the sales figures.

*Curiously, you don't type the curly brackets, just the rest of the formula - but if you hit enter to get out of the formula it won't work, you have to hit CTRL + SHIFT + ENTER

And of course, always 'lock' your ranges by clicking F4 while they're selected, or manually entering them. Because when you paste a formula it will move your range relative to how much your pasted data moved.

Locked $A$1
Unlocked $A$1

I always want the row to stay but not column A$1
I always want the column to stay but not the row $A1

well I fucked up unlocked obviously, that's just A1

IF formula's are my favorite!! I thought I had done full on magic when I got my first IF forumula to work!

dlookup is my favorite, but if's good too

--->just typed =dlookup into Excel to see if that was anything other than a typo

I do love my vlookups. The if I just posted is like a vlookup that just sorts shit out for you on the go! It fuels many of my reports!

array formulas :o

=sum(countif(E:E,A2)) gets a lot of love from me.

Thanks, people! I forgot to bookmark this thread so I thought that my question went unanswered and I felt weird!

I have one more question and I PROMISE that's it for a long while: Is there some way to make newer entries push down older ones. As if you are stacking objects and the newest object on the stack is always at eye level? So that there would always be an open row where I could make new entries and the 1st one entered is on the bottom and the latest one entered is on top.

I want to use this for a document log.

Thanks for listening to me. Sorry my questions are so completely elementary!

I don't know a way, unfortunately. Short of adding a new row to the top each time, but that might mess with your headers... Someone else may have a way!

I like that sum/countif one. May implement!

Is there a way to use Custom Lists to sort a column so that the empty cells are on top instead of last?

If the non-empty cells are fixed, then you could reverse sort them in the custom list, and the blank should end up on top.

I've got a list of about 2000 names. Just three columns: ORGANIZATION | CONTACT NAME | EMAIL

There in Column 3 is where I'm having problems. Some of the email addresses are active, I can click on them and my Thunderbird lights up. But some of them just sit there like ordinary text.

I notice in the field for the active emails, there's a bunch of [409tru09uHYPERLINK;;; susie@thejuniorlea✧✧✧.com;"su✧✧✧@thejuniorlea✧✧✧.c✧✧"9823u5. How can I make it where ALL of the email addresses are like that?

I'm doing all of this in Google Drive, btw.

Learnt double vlookup, never looked back.

http://dailydoseofexcel.com/archives/2015/04/23/how-much-faster-is-the-double-vlookup-trick/

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

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

xp- i've automated most of the tedium out of my job with R :-)

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.

I’ll just do that for you for $100

Ha.

If there's no way out of this, I'd also be happy to hear some appropriate playlist recommendations.

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.

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.

yeah, i would be trying to pull keywords from the headlines and dumping them into a 3rd column

I dunno if this is of help, but it might ease the process a little bit
Assuming a file ala

col A | col b
-------------
LOL   | 50
LOL!  | 100
ROFL  | 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
      | 100
ROFL  | 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.

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.

Cool, I'm happy to hear it

ANNOUNCE XLOOKUP

https://techcommunity.microsoft.com/t5/Excel-Blog/Announcing-XLOOKUP/ba-p/811376

omg

index/match shurely

i guess i am still definitely a nerd because i actually am excited by this news

fixes the problems vlookup and hlookup have always had, ace. does it perform better with big spreadsheets than index/match?

thank god it defaults to exact match, 99% of the time the answer to the question "why has my vlookup done something nuts" (and god knows how many more times it happens unnoticed) is "because you have unsorted data and didn't set the optional 'exact match' parameter"

kind of feel Excel should warn you like it does when you sort on a selection and you might have wanted to sort the whole damn thing, tbh. I have deliberately used the default match before but only like once or twice - suppose that may be different for people who use it for statistical wranglings though.

anyway this is cool but I might not be able to use it much since it's nice to leave the formulae in and some of my colleagues will probably still be on Excel 2013 for the next decade

otm re exact match, was always an insane default behaviour

there's xmatch too

I guess the default made sense 30 years ago when computers were slower and we've had to live with it ever since for "backward compatibility".

Like how Excel doesn't know how many days were in the year 1900, for compatibility with Lotus 1-2-3...

whoa i'm stoked

when does it go live to everyone?

thank god it defaults to exact match, 99% of the time the answer to the question "why has my vlookup done something nuts" (and god knows how many more times it happens unnoticed) is "because you have unsorted data and didn't set the optional 'exact match' parameter"

OTM

truly in awe of all you Excel wizards, you're the real heroes

xp: otm, either that or lurking HIDDEN ROWS/COLUMNS

I am probably pulling down my pants and exhibiting my huge ignorance to the world, but how do I condition a cell's contents in Excel 2010 to achieve an IF...THEN... ELSE kind of formula? Say I want E10 to look at the contents of A10 and see if it is greater than 100, and apply one formula if it is, or another if it is not.

nested if loops are your man there aimless, a quick google would prob explain it better than i could tbh

for that specific, a simple if(a10>100, formula1, formulab) or very similar would presumably get you started

thx

My biggest gripe with Excel is that I have to use the F2 key to get to the formula bar. Like, who the hell finds it comfortable to use the Function keys?!

That’s actually quicker than what I do. I always use the mouse to click in the formula bar. I’ll have to remember F2.

OK, I have a problem that's been driving me nuts for a while. I have two columns of data, and the rows are groups. It's like this:

Col A. Col B.
person 1 | keyword 1
person 1 | keyword 2
person 1 | keyword 3
person 2 | keyword 1
person 3 | keyword 1
person 3 | keyword 3

I want to eliminate the duplicate rows and put each value in column 2 in it's own column, like this:

Person 1 | keyword 1 | keyword 2 | keyword 3
Person 2 | keyword 1 | |
Person 3 | keyword 1 | keyword 3 |

There's too much data for me to just copy/paste groups of rows and transpose them.

I've tried a pivot table, but that doesn't get me the result I need. It puts all the values of column B into a single row of labels at the top. I've tried various arcane formulae, but nothing seems to work.

fffffffffuuuuuuuuuccccccccccccckkkkkkkkkkkkk

Some clever solutions here: superuser.com/questions/453041

the excel answers I’m getting from google are bad but what you’re looking for is denormalizing into lists

or? if you search for 'vlookup that returns multiple values' there are some different methods that returns exact match results in its own cell.

'vlookup that returns multiple values' was definitely the right thing to search! Many thanks, Yerac. Tinkering with the formula here did the trick.

Good!

oh was going to suggest INDEX but couldn't remember the exact way I used it