Formula to extract table data

mopp1

Board Regular
Joined
Apr 4, 2017
Messages
74
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).
 

Some videos you may like

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
Joined
Apr 11, 2010
Messages
11,003
ProjectLevel
AAA1
AAA2
AAA2
AAA3
AAA3AAA2#####
BBB1BBB3
BBB2CCC1
BBB2DDD2
BBB2
BBB4
CCC1
CCC2
CCC3
DDD1sumproduct gives this table
DDD2
DDD2easy 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
Joined
Apr 4, 2017
Messages
74
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,275
Messages
5,527,726
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top