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

bertkelmer

New Member
Joined
Aug 15, 2018
Messages
5
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
ABCDEFG
134John  CriteriaCost CenterFrequency
234JohnJohnJohn342
334Adam
425Kelly
525Kelly
625Kelly
712Jim
812Jim
912Jim
Sheet185
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
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



34
John1

COST CENTRE34
34
John- NUMBER OF ENTRIES2
34Adam2
25Kelly-
25Kelly- 1John
34Adam- 2Adam
12Jim- 3-
12Jim- 4-
12Jim- 5-

<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>
</tbody>
 
Upvote 0
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.).

NameCount
Results in different sheet (ie. Dashboard)Formulas
John2John=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)),"")),"")
Adam0Jim=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)),"")),"")
Kelly1=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)),"")),"")
Jim3=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)),"")),"")

<tbody>
</tbody>

b.
AB
CCName
34John
34John
34Adam
25Kelly
25Kelly
25Kelly
12Jim
12Jim
12Jim

<tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
Re: Help: Extract Value that appears >=N times based on a criteria from a different column.


Book1
ABCD
234Johncost center
334John34
434Adamfreq >=
525Kelly2
625KellyList
725KellyJohn
812Jim
912Jim
1012Jim
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))),"")
 
Upvote 0
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)
 
Upvote 0
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!!!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top