computer/programming/excel wizards! help an idiot!

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