Extract values, ignore blanks and sort by count, based on name selected from drop down

[JJG123]

Hello, and thanks for clicking into my posting. I am hoping that someone can offer some guidance with what I am trying to achieve. I am admittedly an excel novice, so I am having a difficult time finding a solution.

The sample data below (beginning from column C) is pulled into a workbook (there are dozens of columns and about 1,000 rows). Except for the true/false column - data in other columns contain repeated values and blanks. My goal is to create a dynamic table in a separate worksheet, based on the data associated with specific manager names. The values should change based on the name selected from a drop down.

Manager names can appear in any of the name columns. To get the manager names into one column, I created a helper column with the following array formula (credit to exceljet website): =INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0)) / where range1 = row search range and range 2 = named range 'Managers'


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Manager (helper)[/TD]
[TD]TRUE/FALSE[/TD]
[TD]Name1[/TD]
[TD]NAME2[/TD]
[TD]CATEGORY[/TD]
[TD]NAME3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AA[/TD]
[TD]TRUE[/TD]
[TD]AA[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BB[/TD]
[TD]TRUE[/TD]
[TD]BB[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CC[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD]CC[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]AA[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AA[/TD]
[/TR]
</tbody>[/TABLE]

Question 1: is there a non-array formula that I can use in the helper column that would achieve the same result?

Question 2: what formula would be required to extract a unique distinct list from the 'Categories' column, sorted by count of occurrence (highest to lowest), and exclude blanks, based on drop down selection (manager name)? This will help me create the rest of the desired table, which will include additional columns/formulas (pivots would not work in my case).

Thank you in advance for any guidance that you can offer.

 

[Eric W]

Welcome to the forum.

Try:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L
Manager (helper)	TRUE/FALSE	Name1	NAME2	CATEGORY	NAME3	Managers	Manager	Category	Count
AA	AA	AA	AA
BB	BB	CC
CC	CC	BB
	XX
AA	AA
AA	AA

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=IFERROR(INDEX(Range2,LOOKUP(2^20,MATCH(C2:F2,Range2,0))),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
[TD="align: left"]=IF(K2<>"",COUNTIFS($A$2:$A$10,$J$2,$E$2:$E$10,K2),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]{=IFERROR(INDEX($E$2:$E$10,MODE(IF($A$2:$A$10=$J$2,IF($E$2:$E$10<>"",IF(COUNTIF($K$1:$K1,$E$2:$E$10)=0,MATCH($E$2:$E$10,$E$2:$E$10,0)*{1,1}))))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Range2[/TH]
[TD="align: left"]=Sheet1!$H$2:$H$5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The A2 formula doesn't require Control+Shift+Enter, but it still does some internal array processing, I don't think you can get away from that. The E2 formula requires CSE, I don't know how to get around that.

Hope this helps.

 

[JJG123]

Thank you very much for responding. I tried entering the formula referenced in K2, but I keep getting the error message, "You've entered too many arguments for this function."

 

[Eric W]

The formula works for me. Sometimes the formatting distorts the formula, but it appears to be correct. Did you have to change the ranges? If so, what does your formula look like now? What version of Excel do you have?

 

[JJG123]

I am using Excel 2010. It seems I entered the formula incorrectly the first time, as excel now accepts it without error. Although, it is not extracting the values in the way I expected it to (maybe I could have phrased my initial request differently).

The formula extracts some, but not all values from the category field. As I pull the formula down, it is extracting duplicate values. I'd like to extract each distinct value and avoid duplicates, based on the drop down selection.

Notes:
- The main data table (named 'OI') and the desired data table are in different worksheets
- The drop down with the manager names is in cell A4 of the worksheet where desired table will be located
- I entered the formula in cell A53 of the worksheet where desired table will be located

Here's what the formula looks like in my file:
{=IFERROR(INDEX(OI[CATEGORY],MODE(IF(OI[Manager]=$A$4,IF(OI[CATEGORY]<>"",IF(COUNTIF($A$52:$A52,OI[CATEGORY])=0,MATCH(OI[CATEGORY],OI[CATEGORY])*{1,1}))))),"")}

 

[Eric W]

Try this:

=IFERROR(INDEX(OI[CATEGORY],MODE(IF(OI[Manager]=$A$4,IF(OI[CATEGORY]<>"",IF(COUNTIF($A$52:$A52,OI[CATEGORY])=0,MATCH(OI[CATEGORY],OI[CATEGORY],0)*{1,1}))))),"")
with CSE.

That parameter is critical in this case.

 

[JJG123]

That. Was. Beautiful. It works just as I had hoped! Thank you very much for your patience and for sharing your expertise.

 

[Eric W]

I'm very glad it works for you.