Looking Up & Assessing Data

[Amosbroker]

Hello everyone!

I currently have a workbook that I use the index/match function along with IF statements. I have two tabs: first tab contains my clean list of data that pulls from my data export on tab 2. Below is a small snippet of the portion I am trying to find a solution. I want to pull in ID's that are ACTIVE in to my Tab 1 from Tab 2. Now I can do this with:

IF('[Incentive_5.31.18.xlsm]ESCore'!O29="Active",'[Incentive_5.31.18.xlsm]ESCore'!G29,"")

<tbody>
</tbody>

However, it gives blanks where there are disabled people. I want a clean list of only active people, I want it to skip over the disabled people. Is this something I can do with formula or will I need to create a VBA? I have other formulas tied to the ID across the row so I will need excel to look at each row by active status.

Tab 2 - Data Export

ID	Locale	FN	LN
ABC1	Disabled	Jill	SMith
ABC2	Active	John	Smith

<tbody>
</tbody>

Tab 1 - Cleaned up List

ID FN LN Div ABC1 John Smith North ABC2 Jill Smith South <tbody> </tbody>

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[Jerry Sullivan]

Hi Amosbroker,

A filtering operation like that can be done with formulas or VBA, but I think a simpler solution would be use one of Excel's features that are designed to filter data tables.

Options include PivotTables, AdvancedFilter and Power Query.