Return most occurring text entry from named range where three conditions met, one with wildcards

[Upex]

Hi All, hope you're well.

I cannot get this formula to work and don't get anything useful from evaluation etc:

=INDEX(Theme,MODE(IF((ReceivedDate>=DATEVALUE(W7))*(ReceivedDate<DATEVALUE(Y7))*(Product="*Tin*"),MATCH(Theme,Theme,0))))

What I'm trying to do is to return the most occurring entry from the named range 'theme' where:
the date (listed in named range ReceivedDate) is greater than or equal to the date as specified in W7
and
the date is also less than the date held in Y7
and
the product name, held in a named range 'product', contains 'tin' (using wildcards as can span several - Tin hat, Tin pan, Gal tin etc.


Any ideas on how to fix or better way to do it?

Many thanks,

 

[felixstraube]

Would this work?:

=LET(
ft, FILTER(Theme,(ReceivedDate>=W7)*(ReceivedDate<Y7)*ISNUMBER(SEARCH("Tin", Product))),
ut, UNIQUE(ft),
tcount, BYROW(ut, LAMBDA(x, SUM((ft=x)*1))),
t_c, HSTACK(ut, tcount),
FILTER(t_c, tcount=MAX(tcount))
)

It returns the Theme and the count for the max occurring values. If 2 or more themes appear the same number of times, it list all of them.

Book11.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Theme	ReceivedDate	Product
2	a	09/08/2024	SometextTinSometext			a	2
3	b	09/08/2024	Sometext			b	2
4	c	09/08/2024	Sometext
5	b	01/08/2024	SometextTinSometext
6	a	09/08/2024	Sometext																				From		To
7	a	01/08/2024	Sometext																				01/08/2024		10/08/2024
8	b	09/08/2024	SometextTinSometext
9	c	09/08/2024	Sometext
10	a	09/08/2024	SometextTinSometext
Sheet3

Cell Formulas
Range	Formula
F2:G3	F2	=LET( ft, FILTER(Theme,(ReceivedDate>=W7)*(ReceivedDate<Y7)*ISNUMBER(SEARCH("Tin", Product))), ut, UNIQUE(ft), tcount, BYROW(ut, LAMBDA(x, SUM((ft=x)*1))), t_c, HSTACK(ut, tcount), FILTER(t_c, tcount=MAX(tcount)) )
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Product	=Sheet3!$C$2:$C$10	F2
ReceivedDate	=Sheet3!$B$2:$B$10	F2
Theme	=Sheet3!$A$2:$A$10	F2

 

[Upex]

That works - fab - thanks so much.

Being cheeky. is there anyway to get it to put the count volume underneath the theme rather to its right? Or to ditch the value and just leave the theme (the value being right is in the way!

 

[Fluff]

Can you post a sample of the data that shows the problem.

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.

 

[felixstraube]

That works - fab - thanks so much.

Being cheeky. is there anyway to get it to put the count volume underneath the theme rather to its right?

That may be because there aren't any rows that meet the criteria. We could correct that with:

=LET(
ft, FILTER(Theme,(ReceivedDate>=W7)*(ReceivedDate<Y7)*ISNUMBER(SEARCH("Tin", Product))),
ut, UNIQUE(ft),
tcount, BYROW(ut, LAMBDA(x, SUM((ft=x)*1))),
t_c, HSTACK(ut, tcount),
IFERROR(FILTER(t_c, tcount=MAX(tcount)), "")
)

Or maybe some dates are stored as text. The same for dates in W7 and Y7.

As Fluff said, a sample of your data would be helpful.

 

[Upex]

Thanks both - I cannot use XL2BB (at work and blocked) and data is huge and I feel would only overcomplicate things (partly why I use named ranges vs column BDE etc).

The above works exactly right, thanks, I'm just after a tweak to move or remove the count volume, then its spot on - I did get an error at first - user error - I pointed to dates that weren't in data - when I updated the dates that were there, it tells me the most occurring theme and its count)

I guess therefore my questions are:

How can I remove the count all together so that just the theme is returned? the count is a nice extra but not needed, I simply need the theme to be shown.

as a nice to have, How do I amend the first formula so that instead of the result offering the theme and then its count horizontally (F2:G2 in above) that it does it vertically (F2:F3 - number under theme)?

There are hundreds of thousands of rows over hundreds of columns - getting a tied result is unlikely if that is perhaps an issue with turning the horizontal to vertical and I could easily live with the risk of a tie breaking it.

Many thanks,

 

[felixstraube]

Try this:

For just the themes:

=LET(
ft, FILTER(Theme,(ReceivedDate>=W7)*(ReceivedDate<Y7)*ISNUMBER(SEARCH("Tin", Product))),
ut, UNIQUE(ft),
tcount, BYROW(ut, LAMBDA(x, SUM((ft=x)*1))),
IFERROR(FILTER(ut, tcount=MAX(tcount)), "")
)

For the list transposed:

=LET(
ft, FILTER(Theme,(ReceivedDate>=W7)*(ReceivedDate<Y7)*ISNUMBER(SEARCH("Tin", Product))),
ut, UNIQUE(ft),
tcount, BYROW(ut, LAMBDA(x, SUM((ft=x)*1))),
t_c, HSTACK(ut, tcount),
IFERROR(TRANSPOSE(FILTER(t_c, tcount=MAX(tcount))), "")
)

Let me know if that works.

 

[Asbestos_Jen]

If I understand your request re: vertical arrangement correctly, you would just need to wrap the whole formula in a transpose():

=TRANSPOSE(LET(
ft, FILTER(Theme,(ReceivedDate>=W7)*(ReceivedDate<Y7)*ISNUMBER(SEARCH("Tin", Product))),
ut, UNIQUE(ft),
tcount, BYROW(ut, LAMBDA(x, SUM((ft=x)*1))),
t_c, HSTACK(ut, tcount),
FILTER(t_c, tcount=MAX(tcount))
))

 

[Upex]

Both of those work spot on felixstraube - many thanks, exactly what was needed - I don't get how it works, but it does and I really appreciate your time in helping, thank you.

Asebestos_Jen, that also worked a treat to 'rotate' the theme and volume - many thanks, I hadn't even considered that or the fact that the answer need not be that complicated etc

Thank you everyone - my spreadsheet works as I want it to - least for now!

 

[felixstraube]

Thanks for the feedback. Happy to help.