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:
[TABLE="width: 371"]
<tbody>[TR]
[TD="class: xl64, width: 371"]
IF('[Incentive_5.31.18.xlsm]ESCore'!O29="Active",'[Incentive_5.31.18.xlsm]ESCore'!G29,"")[/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Locale[/TD]
[TD]FN[/TD]
[TD]LN[/TD]
[/TR]
[TR]
[TD]ABC1[/TD]
[TD]Disabled[/TD]
[TD]Jill[/TD]
[TD]SMith[/TD]
[/TR]
[TR]
[TD]ABC2[/TD]
[TD]Active[/TD]
[TD]John [/TD]
[TD]Smith[/TD]
[/TR]
</tbody>[/TABLE]

Tab 1 - Cleaned up List
[TABLE="width: 462"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]FN[/TD]
[TD]LN[/TD]
[TD]Div[/TD]
[/TR]
[TR]
[TD]ABC1[/TD]
[TD]John[/TD]
[TD]Smith[/TD]
[TD]North[/TD]
[/TR]
[TR]
[TD]ABC2[/TD]
[TD]Jill[/TD]
[TD]Smith[/TD]
[TD]South[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.