Formula to Extract Data

Dear Excel Users

I need a formula or technique that will pull the data easily from the Analysis Tab

◘This has about 5,000 rows with a number of different entities / countries in Column E - for now I have just shown two countries data.

◘The formula would look at columns E to K in and will populate in the Data Tab the following:

Entity - Specify which entity it relates to - in this case France and Germany
Category - From Column E in Data Sheet
Account Line - From Column F in Data Sheet
MIAP - Column F in Data Sheet
m_cost - Column I in Data Sheet
Var. - Column J in Data Sheet
% - Column K in Data Sheet

It will only pull data for the following:
If Variance >1% and $1m then pick the Category from Data Sheet, otherwise ignore
If Variance <1% and $1m then pick the Category from Data Sheet, otherwise ignore

So if Variance is say 1,713 but % is <0% - ignore

If Variance is 11,742 and % is 4% - take the value

If variance is (2,158) and Var. is (27%) - take the value

In other words the Absolute Value

You'll absolutly need a VBA macro.
I'll look into this but it might take some time.

Here is a simple macro that would transfer to your Analysis tab all entries fulfilling your requirements.

Please Login or Register  to view this content.

I also attached a workbook with the macro. Give it a try.
I had no clue about sub-totals or totals so they are part of the transfer. If you find a way to flag them, the macro can be modified to prevent their transfer.
The only clue I had about the Entity was its interior colorindex (42). All your data should have the same color setup.
Hope this helps. It is crude but it seems to work well.