# Formula to extract table data

#### mopp1

##### Board Regular
I can accomplish this task fairly easily with a pivot table or a macro but wanted to see if a formula could be used so it is dynamic.

Given a table starting in A1, B1 like this

 Project Level AAA 1 AAA 2 AAA 2 AAA 3 AAA 3 BBB 1 BBB 2 BBB 2 BBB 2 BBB 4 CCC 1 CCC 2 CCC 3 DDD 1 DDD 2 DDD 2

<tbody>
</tbody>
Create a formula in D2 and E2 drag down to list the subset of Projects that have two or more level 2 items and the number of level 2 items. So for the example above the it would look like:

 Proj Number of Level 2 AAA 2 BBB 3 DDD 2

<tbody>
</tbody>

because AAA has two level 2s, BBB has three, and DDD has two. CCC is not listed because it only has one level 2 item listed.

The projects will always be contiguous (i.e., all AAAs together, not scattered) and all will start with a single level 1 item (no multiple level 1 items). Assume the Table is sorted so the Project names are smallest to largest and the levels for each project are smallest to largest (as depicted in the example).

My shot at a formula involved a matrix which could work on a small data set but is unworkable on large data sets (actual case in question has about 11000 rows).

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### oldbrewer

##### Board Regular
 Project Level AAA 1 AAA 2 AAA 2 AAA 3 AAA 3 AAA 2 ##### BBB 1 BBB 3 BBB 2 CCC 1 BBB 2 DDD 2 BBB 2 BBB 4 CCC 1 CCC 2 CCC 3 DDD 1 sumproduct gives this table DDD 2 DDD 2 easy to suppress the 1 against CCC ##### =SUMPRODUCT((\$A\$2:\$A\$17=F6)*(\$B\$2:\$B\$17=2)) I have taken it down to row 17 but you can make it row 100,000

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>

#### mopp1

##### Board Regular
Thanks. I can use
=IFERROR(INDEX(\$A\$2:\$A\$17,SMALL(IF(\$A\$1:\$A\$16=\$A\$2:\$A\$17,9E+99,MATCH(\$A\$2:\$A\$17,\$A\$2:\$A\$17,0)),ROW(1:1))),"")
to get the AAA, BBB, CCC, DDD column. Unless there's a simpler way?

Replies
7
Views
77
Replies
0
Views
44
Replies
11
Views
66
Replies
7
Views
77
Replies
1
Views
35