[Scott]

I'm hoping someone more knowledgeable than I with excel will be able to help me speed up my daily work a little bit.

Every day I have to send a couple of different sales reports to my manager. Essentially it indicates how much money has been brought in so far for the week.

Currently, I'm using a pivot table to format, and filter out any employees who do not apply to the sales report I'm sending (The raw data I use includes sales for every sales rep in every department in the company, and I only need x amount of reps in x amount of departments.) which works fine, however some of the teams I'm pulling numbers for have over 20 agents. It takes time to check, and double check through the pivot table and make sure I include them, and not someone with a similar name.

So what I want to do, is before I pivot the data, filter out any rows that do not include the names of the agents I want.

So essentially, delete any rows that do not have "John Smith" "Jane Doe" or "Dio" in Column K.

 

[Max]

Not as effective as just being able to say "delete everyone but x y and z", Couldnt you just sort by column k and delete around that within the department?

 

[Scott]

To clarify, I said 'department' when a more accurate word would have been 'publication'. I work for a magazine company, with hundreds of publications that the reps sell into. (Maybe not hundreds, but hundreds of codes are used to indicate which publication they sold into.)

When I'm pivoting, I only have to remove one or two publication codes, or remove all except for those one or two codes.

When I remove all except for the one or two codes, the vast majority of irrelevant sales reps is removed for me, since only a handful of reps sell into those publications, so the above becomes a non issue. It's only when I'm including every publication code except for one or two that it becomes a problem.

Using this week as an example, I'm dealing with 28k rows with agent names in Column K. I need 29 unique agent names, that are repeated numerous times in Column K (Each row is an individual sale for every rep in the company)

 

[Max]

Does excel have multi frame sorting still (sort first by publication, then by name? You could do that and simply delete the rows of people you dont want, since the sort is sure to have them seperate so you dont mix them up.

Otherwise, can you export/import with SQL? that would make it fucking easy, if a bit overkill :lol:

 

[Scott]

Does excel have multi frame sorting still (sort first by publication, then by name? You could do that and simply delete the rows of people you dont want, since the sort is sure to have them seperate so you dont mix them up.

Yeah, Excel can do that, but it doesn't help. It's easier to to do what I'm currently doing which is pivot the data, throw Column K in the table, deselect all, and then manually select the 29 reps I need.

The report that I take the data from has every rep, in every office in Canada, and has a unique row for each transaction they made that week. Way too many to sort alphabetically, and delete anyone who isn't part of the 29.

Otherwise, can you export/import with SQL? that would make it fucking easy, if a bit overkill :lol:

A little past my knowledge level at the moment. :lol:

 

[Max]

Im sure theres a way dude, but you might have to script it up, and I cant help you there :/

 

[Scott]

Yeah, I figured scripting would be the best/only option.

I have some crazy complex macros that I use to reduce a 6 hour job into 10 minutes plus wait time. It makes me look like the fucking man in the office. The only problem is I grafted it together from scripts I found online. :lol:

I don't know enough about VB to put together a decent script from scratch.

 

[BigM555]

I've done a lot of this kind of stuff just using the autofilter function but it sounds like your list might be a bit too lengthy for that.

The info on this page might be helpful. You could set up a list of the names you want deleted in one sheet and use it to delete rows in your list.

Sounds like you've dealt with VBA enough you should be able to see what's going on here. Not that complex.

 

[Scott]

I'd need it to delete all the names except the ones in the list, since there are hundreds of reps, and they can vary on a week by week basis. (Someone was on vacation, or just didn't sell anything this week for example, I don't want them to show up when I run the script next week.)

I'm trying to mod the script found in that link accordingly, so we'll see how that works out.

 

[Scott]

:woot: I think I got it figured out based off of the link you provided Mike.

I altered the script to match my data, and then removed "Rows(Lrow).Delete" from the ElseIf, and simply added "Else: Rows(Lrow).Delete", and it seems to work.

For one of my longer lists it takes a while to complete since my work computer is shit. (And it sounds like my fan is going to crap out :lol: ) But at least it it makes for good quality control.

:yesway:

 

[BigM555]

:woot:

 

[Dormant]

It looks like you have already solved this, but I thought I would just throw in an alternative suggestion - just to clarify is it the same 29 sales reps you are always looking to isolate? If so the easiest thing to do would be to use array formulas specific to those names (lets say from A1:A29). For example you would list the names of the sales reps on a standard new sheet, name Column K as a 'named range' (So for $K$2:$K$30,000 is called 'SalesReps') and if you wanted a tally of their total earnings (say in column L) you could use {=sum(if(SalesReps=A1,$L$2:$L$30000))} - The {} bits which you add onto the formula by hitting Ctrl Shift and Enter superpowers the formula so it will look for every instance in your data in Column K that matches criteria 1 (The sales rep's name in A1) and the second criteria which would be earnings (in column L) and then sum them up. You can then just drag the formula down so it applies to each name you have listed. The nice thing about this is all you have to do each time is change the data set and it automatically calculates it for you.

A useful tutorial on 'Array' formulas - Array Formulas | Excel Array Formula | Supercharge your formulas

Alternatively the other formula you could have used is GetPivot (runs a bit smoother for large operations) where you would replace the variable with the specific 29 names you are looking for. This fundamentally would do the same thing - it would look for the 29 people in the pivot table that you want data for and only spit that data out in your summary page. Again it would only need to operate in the same way and would automatically refresh every time you change the data. Useful link on Getpivot formula's: Excel Pivot Table Tutorial -- GetPivotData

Like I said, I realise that you look like you have already solved your problem but thought it would be helpful to know you can use formula functions very easily to do the same thing as VBA, but generally it's much easier to debug a formula error than it is a VBA code error. Also doesn't involve having to delete any rows which in terms of data management is best practice.

Sorry if I'm not being any help at all. :lol: