Extract Value that appears >=N times based on a criteria from a different column.

[bertkelmer]

I have tried searching and google with minimal luck. I am trying to help someone who is not excel savy at work. I am trying to extract a value that appears x times or more based on a criteria that is in a different column. For example, the report below has data on all the cost centers (column A) this person is responsible for. They are interested in cost center 34 and want to extract the name of the person that appears 2 or more times from column b. So based on this criteria, the yield would be John (cost center 34, appears twice), and the 5-10 rows below would be blank.

I've tried formulas that pull values that appear >=2 times but the yield is John, Kelly and Jim when I drag the formula down. I am interested only on cost center 34.


A B
34 John
34 John
34 Adam
25 Kelly
25 Kelly
25 Kelly
12 Jim
12 Jim
12 Jim

 

[jtakw]

Re: Help: Extract Value that appears >=N times based on a criteria from a different column.

Hi,

Not sure how your data is laid out, may be this will help:

Book1
	A	B	C	D	E	F	G
1	34	John			Criteria	Cost Center	Frequency
2	34	John	John	John		34	2
3	34	Adam
4	25	Kelly
5	25	Kelly
6	25	Kelly
7	12	Jim
8	12	Jim
9	12	Jim
Sheet185

Cell Formulas
Range	Formula
C1	=IF(AND(A1=F$2,COUNTIF(B$1:B1,B1)>=2),B1,"")
D1	=IF(AND(A1=F$2,COUNTIF(B$1:B1,B1)>=G$2),B1,"")

C1 formula uses cell reference F2 for Cost Center.
D1 formula uses cell reference F2 for Cost Center, and G2 for Frequency.

Either formula copied down.

 

[Vijaykumar Shetye India]

Re: Help: Extract Value that appears >=N times based on a criteria from a different column.

Dear bertkelmer,

Transfer you data to cells A2 to B10.
In cell F2, enter the Cost Centre (34)
In cell F3, enter the Minimum Count (2)
In cells E6 to E20, enter the Numbers 1, 2, 3, ..
In cell C2, enter the below formula and drag it down.
=IF(A2<>$F$2,"-",IF(COUNTIFS($A$1:A2,A2,$B$1:B2,B2)>1,"-",IF(COUNTIFS(A2:$A$1000,A2,B2:$B$1000,B2)>=$F$3,MAX($C$1:C1)+1,"-")))

In cell F6, enter the below formula and drag it down.
=IFERROR(INDEX($B$2:$C$1000,MATCH(E6,$C$2:$C$1000,0),1),"-")

Change the cell references as required.

I hope this is will solve your problem.

Regards,

Vijaykumar Shetye,
Panaji, Goa, India

<colgroup><col style="width:48pt" width="64" span="4"> <col style="mso-width-source:userset;mso-width-alt:4807;width:101pt" width="135"> <col style="width:48pt" width="64"> </colgroup><tbody>
[TD="class: xl65, width: 64"]
[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 135"] [/TD]
[TD="class: xl65, width: 64"] [/TD]

[TD="class: xl66, align: right"]34
[/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl65, align: right"]1
[/TD]
[TD="class: xl65"]
[/TD]
[TD="class: xl65"]COST CENTRE[/TD]
[TD="class: xl65, align: right"]34[/TD]

[TD="class: xl66, align: right"]34
[/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl65"]-[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]NUMBER OF ENTRIES[/TD]
[TD="class: xl65, align: right"]2[/TD]

[TD="class: xl66, align: right"]34[/TD]
[TD="class: xl65"]Adam[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65"]
[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl66, align: right"]25[/TD]
[TD="class: xl65"]Kelly[/TD]
[TD="class: xl65"]-[/TD]
[TD="class: xl65"]
[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]

[TD="class: xl66, align: right"]25[/TD]
[TD="class: xl65"]Kelly[/TD]
[TD="class: xl65"]-[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"]John[/TD]

[TD="class: xl66, align: right"]34[/TD]
[TD="class: xl65"]Adam[/TD]
[TD="class: xl65"]-[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65"]Adam[/TD]

[TD="class: xl66, align: right"]12[/TD]
[TD="class: xl65"]Jim[/TD]
[TD="class: xl65"]-[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65"]-
[/TD]

[TD="class: xl66, align: right"]12[/TD]
[TD="class: xl65"]Jim[/TD]
[TD="class: xl65"]-[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65"]-[/TD]

[TD="class: xl66, align: right"]12[/TD]
[TD="class: xl65"]Jim[/TD]
[TD="class: xl65"]-[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65"]-[/TD]

</tbody>

 

[bertkelmer]

Re: Help: Extract Value that appears >=N times based on a criteria from a different column.

Thank you for the input but this ins't exactly what I'm looking for. The results would be in a separate spreadsheet (ie. Dashboard), and non blank values would be listed first. Below is something similar I worked on a while ago, where the layout of the data had a name and a count column. On the dashboard I was able to pull names that had a count of >1, I would drag the formula down to about 10 cells so that in case three names had a count of >1 they would come up, otherwise blank. I works great with data that has the layout below. But the layout in OP is different. Column A being the cost center number, and column B being the names, which are repeated multiple times. So the goal is to have a formula that does something similar to the formula below (pull a name that appears more than x times), with a different data lay out (see table b.).

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=104E8B]#104E8B[/URL] , align: left"]Name[/TH]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=104E8B]#104E8B[/URL] , align: left"]Count[/TH]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=104E8B]#104E8B[/URL] , align: left"]
[/TH]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=104E8B]#104E8B[/URL] , align: left"]Results in different sheet (ie. Dashboard)[/TH]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=104E8B]#104E8B[/URL] , align: left"]Formulas[/TH]
[/TR]
[TR]
[TD="align: left"]John[/TD]
[TD="align: left"]2[/TD]
[TD="align: left"][/TD]
[TD="align: left"]John[/TD]
[TD="align: left"]=IFERROR(INDEX($A$2:$A$5,IFERROR(SMALL(IF($B$2:$B$5>1,ROW($B$2:$B$5)-ROW($A$2)+1),ROWS(B$2:B2)),"")),"")[/TD]
[/TR]
[TR]
[TD="align: left"]Adam[/TD]
[TD="align: left"]0[/TD]
[TD="align: left"][/TD]
[TD="align: left"]Jim[/TD]
[TD="align: left"]=IFERROR(INDEX($A$2:$A$5,IFERROR(SMALL(IF($B$2:$B$5>1,ROW($B$2:$B$5)-ROW($A$2)+1),ROWS(B$2:B3)),"")),"")[/TD]
[/TR]
[TR]
[TD="align: left"]Kelly[/TD]
[TD="align: left"]1[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"]=IFERROR(INDEX($A$2:$A$5,IFERROR(SMALL(IF($B$2:$B$5>1,ROW($B$2:$B$5)-ROW($A$2)+1),ROWS(B$2:B4)),"")),"")[/TD]
[/TR]
[TR]
[TD="align: left"]Jim[/TD]
[TD="align: left"]3[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"]=IFERROR(INDEX($A$2:$A$5,IFERROR(SMALL(IF($B$2:$B$5>1,ROW($B$2:$B$5)-ROW($A$2)+1),ROWS(B$2:B5)),"")),"")[/TD]
[/TR]
</tbody>[/TABLE]

b.[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=104E8B]#104E8B[/URL] , align: left"]A[/TH]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=104E8B]#104E8B[/URL] , align: left"]B[/TH]
[/TR]
[TR]
[TD="align: left"]CC[/TD]
[TD="align: left"]Name[/TD]
[/TR]
[TR]
[TD="align: left"]34[/TD]
[TD="align: left"]John[/TD]
[/TR]
[TR]
[TD="align: left"]34[/TD]
[TD="align: left"]John[/TD]
[/TR]
[TR]
[TD="align: left"]34[/TD]
[TD="align: left"]Adam[/TD]
[/TR]
[TR]
[TD="align: left"]25[/TD]
[TD="align: left"]Kelly[/TD]
[/TR]
[TR]
[TD="align: left"]25[/TD]
[TD="align: left"]Kelly[/TD]
[/TR]
[TR]
[TD="align: left"]25[/TD]
[TD="align: left"]Kelly[/TD]
[/TR]
[TR]
[TD="align: left"]12[/TD]
[TD="align: left"]Jim[/TD]
[/TR]
[TR]
[TD="align: left"]12[/TD]
[TD="align: left"]Jim[/TD]
[/TR]
[TR]
[TD="align: left"]12[/TD]
[TD="align: left"]Jim[/TD]
[/TR]
</tbody>[/TABLE]

 

[bertkelmer]

Re: Help: Extract Value that appears >=N times based on a criteria from a different column.

Thank you vijaykumar, but I'm looking for something else. I replied to the thread before yours, but it needs to be approved by the mods before anyone can see it.

 

[Aladin Akyurek]

Re: Help: Extract Value that appears >=N times based on a criteria from a different column.

Book1
	A	B	C	D
2	34	John		cost center
3	34	John		34
4	34	Adam		freq >=
5	25	Kelly		2
6	25	Kelly		List
7	25	Kelly		John
8	12	Jim
9	12	Jim
10	12	Jim
Sheet1

In D7 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$10,SMALL(IF(FREQUENCY(IF($A$2:$A$10=$D$3,MATCH($B$2:$B$10,$B$2:$B$10,0)),ROW($A$2:$B$10)-ROW(INDEX($A$2:$B$10,1,1))+1)>=$D$5,ROW($A$2:$B$10)-ROW(INDEX($A$2:$B$10,1,1))+1),ROWS($D$7:D7))),"")

 

[bertkelmer]

Re: Help: Extract Value that appears >=N times based on a criteria from a different column.

Thank you!!! Sooo close!!. One minor thing that doesn't make it work is the parts of the formula that has the range ($A$2:$B$10). My actual data has other columns in between and I think that is affecting it. I could be wrong.

ROW($A$2:$B$10)-ROW(INDEX($A$2:$B$10,1,1))+1)>=$D$5,ROW($A$2:$B$10)-ROW(INDEX($A$2:$B$10,1,1))+1)

 

[bertkelmer]

Re: Help: Extract Value that appears >=N times based on a criteria from a different column.

Please disregard last reply. It works like a charm. THANK YOU!!!

 

[Aladin Akyurek]

Re: Help: Extract Value that appears >=N times based on a criteria from a different column.

Please disregard last reply. It works like a charm. THANK YOU!!!

You are welcome.