I have been asked to produce a report of the top 25 customers per sales territory based on FY 07 sales. I have downloaded a report from our accounting software of all accounts with gross sales for the year. I have imported it into Excel and sorted it first by territory, then by FY 07 Sales in descending order.
Is there an easy way to summarize each territory including just the top 25 customers from each group short of manually counting the first 25 records in each territory and deleting all the records below them?
If this can be done with features already built into Excel, that would be great. But if a macro is required, that's fine, too. I'm just looking for the quickest way to get this done but still automated to some extent, since I get asked for this sort of report often enough that I am getting tired of manually selecting the rows and am convinced there must be a better way.
If a macro is required, I consider myself to be somewhere between a beginner and intermediate VBA user (closer to beginner), so please type slowly and use small words!
Just need a push in the right direction here.
I get several reports regarding sales/shipping/etc. However, in the customer name column might on orders might include dozens of names for a customer I might want to group together, e.g., "IBM", "I.B.M", "IBM-CA", IBM-NY", "Int. Bus. Mach.", etc. So, if somebody wants me to spit out a report saying what IBM, AT&T, and GM did this month, and they each have multiple entries, I'm out a luck without sitting down and determining each individual line, which isn't feasible.
What I'd like is to be able to take which ever report is sent to me, add a column, run the original column through something and populate the new column with the master customer name; Then I could do my pivots against that column. I'm fine with just leaving any companies I haven't chosen to group blank or putting them in an "Other" group. I could then run through the Others and add them to my table of names if needed. My initial feeling is that I'd put the permanent table in access (with Customer Name as Primary Key and Master Customer as my group names) and run some macro against that, but I'm a little stuck on the mechanics of what to do.
Can anybody point me in the right direction?
Hello Mr. Excel forum!
I have a data set that looks as follows (with many SKU columns, not just 3, but for the sake of illustration):
Customer ID
SKU 1
SKU 2
SKU 3
10000060
1
1
10000418
1
10000592
1
1
10001205
10001231
1000131
1
1
10001681
10001707
10002006
1
10002239
10002278
10002418
10002479
1
10002871
1
10002985
1
10003043
1
10003061
10003191
10003271
1
A "1" in any of the columns indicates that a customer did make a purchase of that SKU.
Now, what I would like to do is to be able to somehow identify the most common SKU bundles/combinations, and I would like my output to show up as 1) first listing out for me all of the unique SKU combinations possible and then 2) telling me exactly how many customers bought that unique SKU combination, e.g. SKU 1 and 2 together = 1 customer, SKU 1 and 3 together = 2 customers, SKU 2 and 3 together = 0 customers, SKU 1 and 2 and 3 togethre = 0 customers, etc.
Is there a way for Excel 2007 to do this? A friend mentioned that this might be something that needs to be done in Access, but I've never used it before - any help is appreciated, many thanks in advance!
My work involves scheduling of various people. I have their information in
files but I would like to create a database that I could access at any time,
containg all important information needed for scheduling.
I created a userform and Excel spreadsheet. Now I want to collect the information from this form to create a database of all customers that fill out the form.
Also made a Criteria Range on a separate sheet and then a Table based on this Criteria range. I then placed formulas directly under each header, corresponding to the Estimate Form created by the UserForm. Then I created a Macro that copied the cell values of each column to the table and then hit tab on the last column and then stopped the macro. I then pasted this macro to the userform hoping that each time I hit close on the userform that the values would not only be placed in the Estimated form for the client to see but also into the Table/Customer Database. This way my database will be populated as people seek estimates for possible purchases.
Current Customer's Name/ Address/ State /Zip /Customer / Model/ CD Player/ Power Doors & Windows/ Remote Starter/ Sunroof/ 4X4 /KitCar Estimate
Joe Schmeling /Max Louis Drive /SD /9877 /Pick-Up/ $200 / $- /$650 / $850 / $-/ $21,200
this is what the Criteria Range looks like (1st Row) and corresponding Values (second Row) I Separated them using the "/" symbol.
Ok now that ive thouroughly confused you too, Is there anyone that can tell me how I can create a database from names that are submitted from my UserForm in VBA?
Using a VBA process I need to identify and list in an Excel worksheet the field names of an Access and or an SQL database table.
I was able to identify duplicates in column A; however now I want to see if information tied within the identifed rows are same as highlighted in yellow. Please see example below. Employee ID 456 are duplicates, but have identifical information tied to it; however employee ID 123 have identical IDs, but have different information in column B. How can I identify those?
Thank you!
Column A Column B
employee ID
Job title
123
Excel Expert
123
Programmer
456
Assistant
456
Assistant
Can anyone tell me how to prepare a query for top 10 customers? I've got 3 fields: Customer; Net Sales; Year; I've set the Net Sales to be descending. (all are grouped; net sales is "sum") In the SQL I put SELECT TOP 10 . . . . , but the return is displaying all customers (multiple lines for same customer) instead of a sum.
Regards
excell 2007
Need some help been messing with this for 2 hours
I have 2 files
file 1 is the master and has idnum and a reason code
file 2 is call list file
in the call list file cell U2 is where the formula should be and then copied down
I have one there but just can't get it to work
I want to compair the call list customer idnum to the master list idnum and return the number in the master list next to it
for example call list cell A2 idnum is 28 in the master list 28 is found and the number next to it is 3.
Note idnum in the call list are not necessarily in the master list and vis versa
using: excel 97
I have a set of about 20,000 customers which have bought a particular product in 2008, 2009 and 2010.
What I want to do is be able to delete those customers that have bought in 2010 and only look at those that haven't bought recently so that I may go back and contact them.
Thanks for any help in advance.
