computer/programming/excel wizards! help an idiot!

I have an excel table with client details in each row. one field is their manager’s name, another is their status in a range from A-F.

can I use excel to run through the data and count and how many each manager has at each status (or betw a range of statuses?) and how would I ‘automate’ this? macros?

I think you could just do that in a pivot table.

pivot table u&k for tasks like that

pivot table is yr friend

I've come unstuck in a pivot table and it's driving me mad. I have two columns, SCHOLARS (a count of names) and EMPLOYED (a count of those who have a work address), with YEAR as rows. I want to add another column to show the percentage of scholars who got a job each year. As both the columns had already been counted, I assumed I'd be able to add a calculated field with the formula =EMPLOYED/SCHOLARS but I get a #div/0 error. What am I doing wrong?

Have you tried pasting the values of the pivot table and adding the calculating column in there?

Yep, that'd do the job. It was just biting at me that I couldn't work out a way to do it in the pivot, but I guess why make things any harder for myself.

Thx!

pivots have halved the time I spend on certain things and filled that time with other things

It's swings and roundabouts.

otm, this is totally one of them. Annual statistics exercise, so will be much easier next year- but setting it up's been a fucker

I can't remember if I was ever able to add columns into PTs to do things like that - it's been a while. Wish they were just a touch more malleable!

Hi there sorcerers, I have a dilemma.

I'm working with a new ordering system at work and it's slowing me down. I need some excel magic.

Every week I am going to receive an order spreadsheet.
The order spreadsheet is set up so that each row is a unique order for an account.
The items needed are listed on the header row. This week, there are 9 items for each order
And column A lists the account numbers
each row of cells under the header row contain the order qty's needed for each account.

for example:
Row 2: A2 = acct #, b2 = qty for item 1, c3 qty for item 2, d4 = qty for item 3

My dilemma is that for the orders to be loaded into our system, the data needs to be put into a template which requires the following setup:

Column A: account number
Column B: item number
Column C: quantity

So using this week's order as an example, since there are 9 items, the first account number will have to repeat 9 times in column a; the item numbers go in column b as a set of 9, and they have to repeat for every new account number. And then the order quantities go in column c.

(NB: as far as this weekly order goes, the number of items changes each week, it could be 4 items one week and 10 items the next week, there's no fixed number).

This whole template thing is a new process for me, and everyone's kind of backwards in handling it. And I didn't know enough about excel to come up with an easy way of manipulating the data, so I was reduced to a whole afternoon of cutting and pasting 4000 lines. Which sucks because I know that there is a way to shape the data into the format I need! I just don't know how.

Do any of you have any ideas? This is way out of my depth, but what I'm hoping for is something that I can use each week to rearrange the data.

If you need I can try to create some dummy screenshot examples of what I need when I get home...I know it's kind of hard to visualise.

oh and my primary reason for needing help is that in my role as data-handler, my aim is to touch the source data as little as possible to generate the orders. The less I have to manually manipulate it, the less chance there are of me creating user-errors, and mindless cutting and pasting for 4 hours is a surefire way to get all kinds of wacky errors.

This miiiight be something a pivot table can do, I'm having a hard time visualizing though. If you can email it to me I could take a look..?

:D

vlookup ftw

Sounds like a job for SUMIFS

got sum new ifs in dis week if yr innerested

Did you get it sorted? It looks like ffm has sorted it out, in which case :D

My feeling is that if the number of items changes each week you probably need a VB macro, but if it's the same (or you know there'll always be at most 20 so you can pretend there are always exactly 20 and just make the extra item quantities 0 and have a lot of 0 rows and maybe filter them out again later), or if you don't mind some manual work each week as long as it's not an entire afternoon of cutting and pasting, you could use worksheet functions

but I have not thought about it super deeply, so, hoping you've already found a better way!

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 :)

there has been a ticker tape parade, ffm presented with keys to the city, not a dry eye in the place

It's true! I felt like a modern day Mother Teresa.

Oh man I love a good vlookup

i am a fan now

Just remember to never ever merge cells. They will just screw everything up. AVOID AT ALL COSTS

yessir

Xp - highlight, right click format, alignment, center across selection

boom goes the dynamite

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)

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

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.

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!

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)

Can't do text to columns in Google, my biggest complaint.

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.

Like converting data laid out in a PivotTable to a regular range? Can't you just copy and paste/paste values?

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?

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.

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.

I don't understand why but it works! Thanks,

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 | RATE
STATE2 | JANUARY | RATE
STATE3 | JANUARY | RATE
STATE4 | JANUARY | RATE
STATE5 | JANUARY | RATE
STATE1 | FEBRUARY | RATE
STATE2 | FEBRUARY | RATE
STATE3 | FEBRUARY | RATE
STATE4 | FEBRUARY | RATE
STATE5 | FEBRUARY | RATE
etc.

What I would like it to look like is

____ ____ | JANUARY | FEBRUARY | MARCH
STATE1 | __RATE___ | __RATE___ | _____RATE
STATE2 | __RATE___ | __RATE___ | _____RATE
STATE3 | __RATE___ | __RATE___ | _____RATE
STATE4 | __RATE___ | __RATE___ | _____RATE
STATE5 | __RATE___ | __RATE___ | _____RATE

How could I do this?

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

Pivot table. Add a row above each header state, month, rate. Then pivot. Add state to row, month to column, and sum the rate.

Oh dear Lord, that did it. Thank you both.

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.

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)

use index match not vlookup

(imperative)

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

yeah index match is certainly more efficient once you start to go beyond a couple hundred rows

A good reason for INDEX MATCH over VLOOKUP or HLOOKUP is you can search right->left or bottom->top