Data Extraction and Best Formula

[Excel-Access]

I would be grateful if anyone could help compute a formula. to extract the data from the Data Tab. I have listed the requirments in the Analysis tab but have also summarised this below. at the moment my worked example have pulled the data manually.

I need a formula 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

◘The formula would look at columns E to K and will populate in the Analysis 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 wil 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

Many Thanks in advance

 

[Luke M]

Not able to do this with a formula, but here's a macro that does the job.

 

[Excel-Access]

Luke

Thank you for this.

May I ask how best to run the macro when I add about 5000 rows please?

 

[SirJB7]

Hi, Excel-Access!

At a first glance and without a deeper analysis Luke M's macro seems to do the job for any number of entries in rows starting at 227. This is the related part of the code:

With Worksheets("Data")
    'Where is the last row of data
    lastRow = .Cells(.Rows.Count, "E").End(xlUp).Row

   
    'Where is header row for outputting data to?
    recordRow = 2
   
    'Where do records on data row begin?
    Set DataRange = .Range("E227:E" & lastRow).SpecialCells(xlCellTypeConstants)
End With

Hope I'm not misreading anything.

Regards!

 

[Luke M]

SirJB7 is correct, it's meant to handle any number of rows. You would just need to adjust where the starting point is.

 

[Excel-Access]

Thanks a million to everyone who contributed and especially Luke M