Macro to identify vendors who had promotion Sales

[chandramouliarun]

Hi All,

Hope you are doing well!.. I have a dataset that has the vendor information along with if the vendor had sales promo (Yes)...Basically the Non promo sales is identified by No and the promo sales is identified by Yes...If the vendor has both promo and non promo sales it has both Yes and no and the overall sales is identified by the sum of Yes and No sales for the vendor...

Now I need to create the output in the (present in the second tab of my attached sheet....-Expected output)..

1) that identifies the vendors that have had promo's and calculates the total sales for 2 different timeframes , the promo sales for two different timeframes and calculate the percentage difference...
2) Now if the vendors sales is only through promotion Yes then its total sales is equal to the promotion sales..

Can you please help me with a macro for this !..Really appreciate your help!!...

Thanks,
Arun

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[chandramouliarun]

Hi Richard,

Apologies!..I have uploaded the file now...Sorry somehow it got missed...

[chandramouliarun]

Seeking some help here!!..

[JeteMc]

Perhaps the following formula based solution will help.
1. Two rows are added above to the table on the 'Expected Output' sheet to give the starting and ending week numbers. Note that these two rows could be hidden for aesthetic purposes.
2. C1:Q1 on the 'Data' sheet are numbers custom formatted "Week" 0
3. The formula for Total Sales is:

Please Login or Register  to view this content.

4. The formula for Promo Sales is:

Please Login or Register  to view this content.

Note that some vendor names contain extra spaces (I removed them on the copy of the sample file).
Let us know if you have any questions.