Populating a table based on the value match from another table

**wnarx** · 08-30-2012, 03:54 PM

I believe that the solution to this will be insultingly obvious, but since I'm only an occasional Excel user and not a native English speaker, I'm having a trouble formulating a Google query that would yield any satisfying answers.

Suppose I have the following table, let's call it Table 1:

Please Login or Register  to view this content.

What I would like to achieve is a Table 2 of the following form (populated with the data from Table 1, basing on the "Include" value):

Please Login or Register  to view this content.

A straightforward solution would be to use a pivot table based on the data from Table 1 (with an appropriate filter), but my requirement is that the Table 2 would automatically update after each change in Include column in Table 1, and all the supposed workarounds for pivot table auto-refresh are giving me headache.

Many thanks for your help!

**day92** · 08-30-2012, 06:41 PM

Hi wnarx and welcome to the forum. Is a VBA solution okay?

**wnarx** · 08-30-2012, 07:00 PM

Originally Posted by day92

Hi wnarx and welcome to the forum. Is a VBA solution okay?

Hi and thanks for the welcome. If there's no easy way to achieve this with formulas only, I'm OK with VBA as well.

**day92** · 08-30-2012, 07:33 PM

Give this a try..

Please Login or Register  to view this content.

**wnarx** · 08-30-2012, 08:00 PM

Thanks for your suggestion, but I'm afraid that's not at all what I was looking for (unless I'm missing something, which may be very well possible).

If I understand correctly, your code is for a one-off macro which simply deletes all the rows containing "No" in the C column.

Perhaps the description in my original post wasn't clear enough, which I have to blame again on my poor command of English, and for which I'm sorry.

What I am trying to achieve is to have two columns side by side. The first one is longer and contains items marked with either "Yes" or "No"; the second table contains only "filtered" data (marked with "Yes" in the first table), and updates automatically after each change in the first table.

Thanks for any further advice.

**Teethless mama** · 08-30-2012, 10:56 PM

In E2: =IFERROR(INDEX(Table1[Name],MATCH(0,IF(Table1[Include]="Yes",COUNTIF(E$1:E1,Table1[Name])),0)),"")

In F2: =IF(E2="","",VLOOKUP(E2,Table1[[Name]:[Category]],2,0))