List items that appear 5 times

[Ashish Mathur]

Hi,

I have data in range A2:E5 as seen in the image. My objective is to list down the items that appear five times. I have been able to solve this by writing this formula in cell H4 =TOCOL(A2:E5) and then this one in cell I4 =CHOOSECOLS(FILTER(HSTACK(UNIQUE(H4#),COUNTIF(H4#,UNIQUE(H4#))),COUNTIF(H4#,UNIQUE(H4#))=5),1)

I get the correct result i.e. A and C in I4:I5. I'd like to solve this question with creating the formula in cell H4.

I'd like to solve this with the LAMBDA function without using any spare columns.

Thank you.

 

[Peter_SSs]

As I mentioned to you in another thread ..

.. better if you can provide sample data in a form that can be copied for testing: MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

But thanks for updating your version details following that other thread.

"My objective is to list down the items that appear five times."
I don't think there is such in that example, but if something was listed more than 5 times do you want it included in the list, or only things that are listed exactly 5 times?

 

[Ashish Mathur]

As I mentioned to you in another thread ..



But thanks for updating your version details following that other thread.

"My objective is to list down the items that appear five times."
I don't think there is such in that example, but if something was listed more than 5 times do you want it included in the list, or only things that are listed exactly 5 times?

Thank you for replying. I would not like to download any tool. You may however download the workbook from here.

I want to get a list of items that appeared exactly 5 times.

Thank you for your help.

 

[Peter_SSs]

I would not like to download any tool. You may however download the workbook from here.

Seriously? You don't want to download anything but you expect your helpers to do exactly that?
You can just copy/paste directly from Excel as a not-so-good alternative.

 

[Ashish Mathur]

Hi,

Here is the data. I'd like to get a list of the items that appeared exactly 5 times. The answer should be A and C. I do not want to use formulas or Power Query. It will be great if you can share a LAMBDA function solution.

2011	2012	2013	2014	2015
A	A	A	A	A
B	B	C	C	B
C	C	D	D	C
F	E	E	E	E

Thank you for your help.

 

[Peter_SSs]

I do not want to use formulas ... It will be great if you can share a LAMBDA function solution.

Surely if you use a LAMBDA, that will still involve a formula in the end anyway?

I'll leave the LAMBDA to somebody more skilled with that but I don't really see the advantage of that over a reasonably short, worksheet formula.

23 01 14.xlsm
	A	B	C	D	E	F	G
1	2011	2012	2013	2014	2015
2	A	A	A	A	A		A
3	B	B	C	C	B		C
4	C	C	D	D	C
5	F	E	E	E	E
Exactly 5

Cell Formulas
Range	Formula
G2:G3	G2	=LET(n,UNIQUE(TOCOL(A2:E5)),FILTER(n,COUNTIF(A2:E5,n)=5,""))
Dynamic array formulas.

 

[Ashish Mathur]

Surely if you use a LAMBDA, that will still involve a formula in the end anyway?

I'll leave the LAMBDA to somebody more skilled with that but I don't really see the advantage of that over a reasonably short, worksheet formula.

23 01 14.xlsm
	A	B	C	D	E	F	G
1	2011	2012	2013	2014	2015
2	A	A	A	A	A		A
3	B	B	C	C	B		C
4	C	C	D	D	C
5	F	E	E	E	E
Exactly 5

Cell Formulas
Range	Formula
G2:G3	G2	=LET(n,UNIQUE(TOCOL(A2:E5)),FILTER(n,COUNTIF(A2:E5,n)=5,""))
Dynamic array formulas.

Hi,

Thank you very much for sharing this solution. It is working as expected.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.