FilterData & Copy

[Carpiem]

Hello,

I am hoping someone will share their expertise with filtering data by some criteria & then pasting the results to a separate sheet within the same workbook?


The attached file has 4 sheets: ?Customers?, ?Group-Codes?, ?SalesData?, ?Summary?.

?Customers? & ?Group-Codes? are provided solely to give some idea of the possible amount of data. The ?SalesData? sheet was limited to 3 Customers and 22 item Codes. Usually there would be 60 -100 Customers and 592 item Codes.

?SalesData? sheet provide sales figures for item Codes that belong to specific product Groups.

The ?Summary? sheet show total sales for the product Groups taken from the ?SalesData? sheet.
In the attached e the Customers were limited to 3 and item Codes to 22. More usually there would be 60 -100 Customers listed and 592 item Codes on the ?SalesData? sheet.

It takes an inordinate amount of time to copy each Cutomer name & Sales numbers from the ?SalesData? sheet then transpose this data onto a blank sheet? sum sales numbers? then copy this to the ?Summary? sheet. Can this be automated using vba?

With regards to the ?Summmary? sheet, I have code that is easily adapted to break out data by Account Manager and be placed on individual sheets.

Your advice and help is always appreciated. Thank you. :)

Brian

[lucas]

so..on the sales data sheet only you wish to copy data rows based on item codes to individual sheets?

[OBP]

I am having the same problem understanding the requirement.
I thought it would be based on group codes. lol

[OBP]

Looking at the data this looks like an Access database would be better suited.

[Carpiem]

Lucas - “so on the sales data sheet only, you wish to copy data rows based on item codes to individual sheets?”

………………………………………………………………………………..

Hello Lucas,

? The intent is to sum the Sales for each item Code that belongs to a specific product Group purchased by the Customer. Then copy the product Group sales to column “E” of the “Summary” sheet.

? To show up on the “SalesData” sheet a Customer would have to purchase at least 1 item (Code) tied to 1 product family (Group).

? There are 592 Codes available for investigation, which belong or are tied to 11 Groups. Or said a different way there are 11 Groups comprised of 592 Codes.

The attached file sheets “SalesData” & “Summary” are laid out for the scenario below.

1. Assuming we have only one Customer who is “Monde” in column “C” on the “SalesData” sheet.

2. The 22 item Codes (column A) are tied to 11 product Groups (column B). On investigation “Monde” purchased 14 item Codes tied to 7 product Groups.

3. The “Summary” sheet deals with product Group net Sales only. So “Monde” would take up 7 rows, each row would be a distinct product Group, each product Group would have a Sales value indicated.

A look at the “Summary” sheet would tell Account Manager “EF” (column B) that “Monde” has filled 7 of the measured 11 Groups along with Year To Date sales versus Target$$ set for a specific Group.

Each Account Manager would have a “Summary” sheet showing Customer(s) sales by Group(s).

A separate “Summary” or “Master” sheet would show all Customers sales by Groups.

Thank you,

Brian

[Carpiem]

Hello OBP,

Yes, always a struggle for me to explain things clearly. But you are right this application is tied to the Groups.

I agree it would be a breeze with Access.

On the downside, it has taken nearly 6 years for our Account Managers to get a grip on Excel. I shudder to think how long the transition to Access would take? :dunno

Thank you,

Brian

[OBP]

Brian, I have looked at your 2 sheets, am I correct in thinking that not all Customers take all Groups. i.e. Lucas doesn't take Controls, MCC and software?
Or is it just that there are no sales for that period.
If not all Cutomers take all controls, wouldn't it be better to have a Customer/Group table showing what customers take which groups?
What you asked in your first post about can this be automated with VBA, the answer is yes, I did something very similar for my company before I retired.

[OBP]

Brian, is the Sales Data sheet always in the same format, as shown?
Are the Customer Headings in alphabetical order accross the sheet?
On the Summary sheet do you Insert a Column at column "F" and copy Column "E" to the new column to enter the Q3 Data in Column "E" to preserve the YTD formula?
Is the Summary in Customer Alphabetical Order?

[OBP]

Where do you get the "Raw" data from for the Sales figures?

[OBP]

Brian, here it is.

[OBP]

Later version with insert quarter macro.