Excel Help

useful info
1

We don’t have a help topic here, so I thought I’d just tack this into here rather than make yet another topic.

I have some quoting data I have to look at six ways from Sunday and I’ve decided that I should be using a pivot table. What I’m wondering is if there is a way to make the columns come out as “Answer1” and “Total of all other answers”. Just two columns instead of the 15 or so I am ending up with.

The specific data is the company that we showed on the quote, or that we declined the quote entirely. And the answer can be more than one carrier, so in theory I have several hundred potential answers, all I really care about in this report is did we quote it, or decline it. If I only show the decline responses, then I don’t show the total number of responses. I also lose a whole row of data in the “sold” category, since any group declined usually doesn’t end up selling.

I’ve likely put this in the most confusing way possible but I’m stuck int eh weeds on this one right now.

2

I’m not sure of a way to do it in the pivot table itself - could you use an IF statement in the data itself in a new column and use that in the pivot? Something like:

=IF([Column X] = “Declined”, “Declined”, “Not Declined”)

That might be your easiest way.

3

Moved this to the useful information category, since this seems to be a good thread to start.

4

Ahh, I hadn’t thought of modifying the data itself. If I keep it simple I can just paste the new data into a template, or even write a macro to make the changes to the data.

Damn, that’s the way I prefer to do things, I was just tangled in Salesforce crap as far as data restrictions.

5

You should ask Nick Burns. If there is something he doesn’t know about Excel I have no idea what it could be.

1 Like
6

Exactly. The dude has a picture of him and the guy who wrote Excel framed on his wall.

1 Like
7

I could see you doing this via an IF formula and a pivot table. Quick question though, are you asking for the total of all other answers for each row? If so, just use SUM and set it up as it’s own column, you can then use this column and Answer1 column as a Count of values in the Pivot Table. If not, then use an IF formula to bring the data together into a single column then continue with the count of values in the Pivot table.

EDIT: As for Salesfarce, I’ve been having to brush up on my Excel-fu as the reporting isn’t where it should be with this. I’ve actually found out (with our version of it, anyway), your access level determines the data that returns, the fields you can actually see report data on, and so on. So, $Low-level_luser may see different results than a $Supervisor. And $VP_Guy, who may see different results than $Reporting_person when trying to compare counts they don’t understand. Fun and hilarity are in abundance and are constantly ensuing.

8

So, I’ve got a fairly common problem where we’ve got a list of data and there are duplicates. I’ve been able to extract my duplicates, but I need to identify or categorize my duplicates a bit more and was wondering if anyone could help. Here’s what I’m trying to do:

Column A contains the field by which these duplicates were identified, let’s say, by a person’s Special ID (A record always has this Special ID). Column B contains the respective Number ID for this person. A person only obtains a Number ID at a specific point in the process, and so, if there are duplicates, it’s possible that both (or all) records have a Number ID, or that only some of the records have a Number ID. Well, I need to identify the SET of records where not all records within that set have a Number ID as these are handled differently than those where all of the records have a Number ID.

Any help or direction to look would be appreciated!

EDIT: I think that I may have a way of doing it. Anyone know how to assign numbers to sets of duplicates? Conditional formatting does highlighting this way. Is it able to assign numbers to the sets instead?

9

Ohh, another stupid Excel question.

We have a 31k row excel document with subtotals by X. The individual running this project wants to delete all data on X that have a subtotal of 0. How the hell can this be done without deleting data lines for other Xs that have a 0 as the value but don’t subtotal to 0?

10

Found workaround.

Vlookup to sheet2 looking for 0 and giving back “Remove”. Copy formula and paste into visible cells only of compressed subtotals. Scroll for “remove” uncompress subtotal, highlight rows and delete by hand. Do this about a thousand times.

11

You could also sort the table by the subtotal and delete all of the rows where the subtotal is 0.

12

But then you have to “unsort” in order to get the formulae to work. :frowning: I remember trying to do the same thing a while ago. I can’t remember how I fixed it. I don’t think I did.

13

Have to delete the source rows too. And since you don’t know until it’s totaled whether you need to delete it or not you can’t just sort before subtotaling.

And it’s for an audit, so wheeee! At least it’s not my project and I made life easier on her.

14

When I have formulas that can get broken by a sort, I use absolute cell references. Those do not get changed by a sort. for example:

=SUM($A$1:$A$365)

will total all of the cells, regardless of a sort, inserted rows or deleted rows. You can also use ($A1) so that the column reference doesn’t change or (A$1) so the row reference doesn’t change. This works for copying formulas as well, the un-absolute reference will change according to the paste.

15

31k rows, something like 13k subtotals. I think the crappy workaround is quicker. :smile:

16

Filter by that field, and only show zeros?

17

First thing I thought of, can’t remember why it didn’t work. Something with the subtotaling.