Extracting data from summary tab to different tabs based on criteria

You've posted this in the Programming Forum, but you can achieve it with 3 basic formulae. Are you interested in a formula-based solution, which will automatically update itself with new/deleted data?

Pete

Try the attached.

Pete,

I am interested in any method which will work.
Formula based would be interesting as i can use that formula on other worksheets that i have .

looking forward to yoru response

AB33

Can you discuss how your VBA works ?

This must be faster

Can you discuss how your VBA works ?
Not every single line of the code.
The code looks at column A rows(Each cell in column A), it creates a sheet name for each row if the sheet has not created it yet. It copies the header in row 1 of each name and also copies the row for each name.
The code first clear all cells except the two sheets(The master sheet and another one) are excluded.
It then goes down column A and check if the tab for each cell exists, if it does, it does not create the tab again, but copy the row and header to that tab.

Formula based solution attached. I've used a helper column in the main sheet with this formula in Q2:

Formula:

Please Login or Register  to view this content.

This is copied down beyond your existing data to accommodate extra data being added.

In the subsidiary sheets I have put the sheet name/RM code in R1 and used Q as a helper again with this formula in Q2:

Formula:

Please Login or Register  to view this content.

Then I have this formula in A2:

Formula:

Please Login or Register  to view this content.

which is copied across to P2, although I've made a slight amendment to P2 to account for the blank cells:

Formula:

Please Login or Register  to view this content.

Then the formulae in row 2 can be copied down as far as you need (I've copied to row 20, as can be seen from the asterisks). Then you can just copy that sheet, rename it and put the appropriate RM code in R1. For three of the sheets I have copied the formulae down to row 50 to ensure that all the data is being picked up.

Hope this helps.

Pete

Hi Ab33
Thank you for your explanation, but i don’t think your code is fit for purpose

Jindon – thank you for your solution, your code seems to work exactly how i want it
Can you explain the logic behind your code ?

I do have one more favour though
Aim – On the “Rm Top 20” Tab, pull the top 20merchants from the “Rm Monthly By Rep Code” tab with the highest “Contribution R12” Column

Can this also be done but with a separate macro ?

pete_Uk

Your logic is soo simple , why didnt i think of that

1) Find out unique "RM code" and autofilter to extract relevant rows.
2) If sheet for particular "RM code" is missing, add one and paste the data.

Top 20
Copy whole data to "RM Top 20" then sort by Col.N in descending order and cut down to first 20 rows.

Please Login or Register  to view this content.

Thank you for your explanation, but i don’t think your code is fit for purpose
How and why? Which bit is missing?

If you want a formula-based approach to evaluate the top-20, then you can put this formula in R2 of the main sheet:

Formula:

Please Login or Register  to view this content.

and copy down. I've coloured this green in the attached file, to avoid confusion with the other helper column.

Then you can copy the R04 sheet, rename it to Top_20, and change the formula in Q2 to this:

Formula:

Please Login or Register  to view this content.

Copy down, and the rest of the sheet will automatically adjust.

Hope this helps.

Pete