Turkeybone posted:

Ok, so generally I am comfortable with Excel and VBA, but not coding for outlook. Does anyone know of good resource?

Basically, at my new job what ends up happening is that I have to send 35 personalized emails pretty often, and my predecessor would click new email 35 times, write in the 35 names (and the appropriate cc), then paste the contents of one of 35 excel spreadsheets into said emails. So while this obviously seems like something to be automated, I've just never coded for outlook before, and it's a Bad Thing if people get the wrong emails (sales reporting, more or less).

melon cat posted:

I'm having some trouble getting a stacked bar chart to behave the way I want it to. This is what I keep getting, but it isn't what I want:



I want:
The Dates on the X-Axis
$ dollar amounts on the Y-Axis
The series labels to be shown in $ dollars
... and the stacked bars to vary in height according to their total dollar amount.

melon cat posted:

I'm having some trouble getting a stacked bar chart to behave the way I want it to. This is what I keep getting, but it isn't what I want:

I want:
The Dates on the X-Axis
$ dollar amounts on the Y-Axis
The series labels to be shown in $ dollars
... and the stacked bars to vary in height according to their total dollar amount.

How the heck do I get this chart to behave the way I want it to?

my cat is norris posted:

Veskit posted:

Have you tried using pivot charts yet? Most complex sumifs are solved by that.

DukAmok posted:

Do you have an example of your data? Easier to just mess with that. Generally, you're going to want your dates going down the rows, your series labels going across, and values in all the other cells.

Example:

esquilax posted:

There are a few types of stacked bar charts. You picked one that always adds to 100%.

Right click -> Change Chart Type -> Stacked Column

melon cat posted:

I tried that, but now the charted data looks even more bizarre. The bars are super-skinny, it repeats the X-Axis categories dates (Jul-13, Jul-13, Jul-13...), and the bars won't line up with X-Axis categories.

And even if I could get the bars to vary in height, the question remains- how do I get the stacked bars to show the %, but the vertical axis to show the dollar amount?

That's the type I'm using, and it's still displaying them at equal height.

Boris Galerkin posted:

I want to make a spreadsheet where I have three columns: WEEKLY, MONTHLY, and YEARLY. I want to be able to enter in a number into say a MONTHLY row, and then have Excel be smart enough to automatically calculate =VALUE*12 and put it into YEARLY and =VALUE*12/52 and put that into WEEKLY. I also want it to be flexible/smart enough so that if I typed in VALUE into WEEKLY then it would automatically do =VALUE*52 and =VALUE*52/12 and put them into the appropriate columns in the row. Same goes if I typed in something into YEARLY.

Can excel do that?

e: The motivation is e.g., Costco membership is $55/year whereas Tonx Coffee is $6/week, so I just want to enter in my values into the right column and have it auto calculate the other values.

TheEffect posted:

My employer has requested that I (after already making it perfectly clear that I don't know squat about Excel aside from making graphs) make an attempt at updating a bit of Excel VB scripting that a previous employee had written. The Excel script basically turns this:



Into this:




The code in question can be found here:

http://pastebin.com/pxTMDPLm


Can anyone please please tell me which part of that script I need to remove to make it NOT parse the second (phone #) column?

EAT THE EGGS RICOLA posted:

The script isn't parsing the second column, it's jut grabbing that whole block and dumping it into your spreadsheet.

It's probably easiest for you to just delete B5:B1million after the rest of the data is dumped in there, moving the column to the left.

melon cat posted:

A question about creating charts using number data.

I'm using Excel 2010. Every time I create a chart (Select Data > F11 to create pie chart in new worksheet) Excel keeps creating a pie chart with a lot of white space. But I don't want that white space, and it won't let me resize the area outside of the bounding box.



Why is it doing this, and how can I eliminate that white space?

esquilax posted:

When you create a chart in a separate sheet, the size of the sheet is dependent on the page size. So it's 8.5x11 by default. You can change the size of the whitespace by changing the page dimensions.

Not sure how to stop that though, or if it's possible to create a square page. Maybe adjusting the margins would work.

edit: Yeah, adjusting the margins works. Just set the right margin to 3.3" or whatever and it becomes a square

melon cat posted:

Unfortunately that solution isn't working for me. It looks exactly the same.

I tried to centre-align the chart so it doesn't look too terrible, but the heck do you Centre-align charts, any way? All of my alignment tools are grayed out.

Veskit posted:

Bro do yourself a favor and learn power pivot. You can either install it onto your excel 2010, or it comes in the newer versions. It's semi complicated, has its own programming language, and is loving powerful. It'll do everything you want and more! THE POSSIBILITIES GO FORTH.

sofokles posted:

for i from firstRow to lastRow
for j from firstColumn to lastColumn
if cells(i,j).value <> "" then
if cells(i,j).value IS NOT IN RANGE/LIST/ARRAY CALLED UNIQUE VALUES then
UNIQUECOUNT = UNIQUECOUNT + 1
RANGE/LIST/ARRAY CALLED UNIQUE VALUES(UNIQUECOUNT)=(Cells(i,j).value)
OCCURENCES(VALUE) =OCCURENCES(VALUE) + 1
VALUEOCCURRENCESADDRESS = i
else
OCCURENCES(VALUE) =OCCURENCES(VALUE) + 1
VALUEOCCURRENCESADDRESS = i
endif
next
next



Howto?

FAN OF NICKELBACK posted:

code:

dim cUnique as collection
set cUnique = new collection
On Error Resume Next
     For Each Cell In Range.Cells
          cUnique.Add Cell.Value, CStr(Cell.Value)
     Next Cell
On Error GoTo 0

Now cUnique is a collection of unique values from your range.

Brain In A Jar posted:

For reasons beyond my control, I have a 600Mb CSV file I need to wrangle with for work, and in particular I need to sort and search within it. Obviously, this is way too bloated for Excel to even look at, and even if it weren't, the number of columns (and also possibly rows) is above the document limit and will cause Excel to crap out anyway.

I've found a program called Delimiter that will allow me to view the file, but because it only accesses parts at a time, I still can't search it, edit it, or filter it down into something usable.

Does anyone know a good solution for this, bearing in mind that I am not a coder for a living, do not normally have to deal with this, and got a C+ in my intro to Java course in college?

Smithersnz posted:

Do you have MS Access? You could import it into there with the CSV import tool, take out what data you don't need, then export it back into excel.

Brain In A Jar posted:

For reasons beyond my control, I have a 600Mb CSV file I need to wrangle with for work, and in particular I need to sort and search within it. Obviously, this is way too bloated for Excel to even look at, and even if it weren't, the number of columns (and also possibly rows) is above the document limit and will cause Excel to crap out anyway.

I've found a program called Delimiter that will allow me to view the file, but because it only accesses parts at a time, I still can't search it, edit it, or filter it down into something usable.

Does anyone know a good solution for this, bearing in mind that I am not a coder for a living, do not normally have to deal with this, and got a C+ in my intro to Java course in college?