Friday, June 19, 2009

Google Analytics Data in Excel

Have you seen this? Very cool. (Thanks for finding this, Bart.) I haven't dug into it yet, but plan to soon...

Stay tuned.

Wednesday, June 17, 2009

Chat Support RE: Custom Number Formats

Here's a short one from today:

[15:39] meeboguest######: hi, Im trying to import 11 digit UPC numbers into my software, but can't find the correct format to keep the digits at 11. All my UPC numbers that start with a 0 are cut off and made 10 digits every time I try to import
[15:39] meeboguest######: I tryed formatting the columns into text and it still shows 10 digits if it start with 0.
[16:15] excel_geek: go to Format --> Cells...
[16:15] excel_geek: from the Number tab select Custom
[16:16] excel_geek: then replace where it says "General" with 11 zeros.
[16:16] excel_geek: tada!



Gotta love customer number formats.

Saturday, June 06, 2009

$50 Project - Parse Google Search Results within a Domain for First Result

I recently completed a $50 Project for a client who wanted a spreadsheet where he could have a list of domains in which to search and a list of search terms and return the URL for the first returned search result from Google while searching withing the specific domain. It took a little modification of some other stuff I've done with parsing Google search pages, but it seems to work nicely.

Excel_Geek Insiders, your file is on it's way.

Later,

Excel_Geek

Tuesday, June 02, 2009

Chat Support RE: Counting in Pivot Tables

Here's another one from yesterday:

[15:40] meeboguest######: hi excel_geek - looking for a cont function in a pivot table... can you help?
[15:40] excel_geek: cont?
[15:40] excel_geek: count?
[15:40] meeboguest######: oups.. "count"
[15:42] meeboguest######: have multiple entries by various "agents" and wnat to find how many agents are ther in total... regardless of the entries
[15:42] excel_geek: sure
[15:42] excel_geek: right click in the table detail part
[15:42] excel_geek: and pick field options
[15:42] excel_geek: then select "count"
[15:44] excel_geek: follow?
[15:44] meeboguest######: ok
[15:46] meeboguest######: it gives me the total count of entries, not count of unique individuals... (1 individual = multiple entries)...
[15:47] excel_geek: hmmm
[15:47] excel_geek: maybe i should back up
[15:48] excel_geek: so drag the agents field into the left part of the pivot table
[15:48] excel_geek: then drag any other column into the right (detail) part
[15:48] excel_geek: the right click the field you put in the right part and field settings to "count"
[15:50] meeboguest######: ok... i think i figured it out!
[15:50] meeboguest######: thx a bunch :)
[15:50] excel_geek: np
[15:50] excel_geek: thanks for stopping by
[15:50] meeboguest######: :D

10 Minutes to understanding. Not too bad. Some of that pivot table stuff is unintuitive.

Monday, June 01, 2009

Chat Support RE: =INDIRECT() Function

More and more I'm helping folks via the MeeboMe chat window I've embedded in the blog. I thought it'd be interesting for you all to see the sorts of conversations I'm having this way:

[14:36] meeboguest######: eric!
[14:36] meeboguest######: do you have a sec?
[14:36] XLgeke: for u?
[14:36] XLgeke: always
[14:36] XLgeke: and now it's over... ;)
[14:36] XLgeke: what's up
[14:36] meeboguest######: excel question
[14:36] meeboguest######: umm
[14:36] meeboguest######: trying to think how to explain
[14:37] XLgeke: reboot
[14:37] XLgeke: that should do it
[14:37] meeboguest######:
[14:37] meeboguest######: ok, I'm wondering if there's a way to dynamically reference a cell in a formula
[14:37] XLgeke: yes
[14:38] meeboguest######: so that if I was to reference cell B
[14:38] XLgeke: right
[14:38] XLgeke: use =INDIRECT()
[14:38] meeboguest######: and that number is a value generated in another cell
[14:38] XLgeke: so like
[14:38] meeboguest######: oooh
[14:38] XLgeke: =INDIRECT("B"&B1")
[14:38] meeboguest######: woah
[14:39] meeboguest######: nice
[14:39] XLgeke: in B1 you'd change the number for BX
[14:39] meeboguest######: right
[14:39] meeboguest######: I'll give that a shot
[14:39] meeboguest######: thanks!
[14:39] XLgeke: np

And that was it...less than 3 minutes and issue solved. This happens scores of times each month.