Find Multiple Match Results Where You Use * in the Search Criteria Across Multiple Columns and Rows

[BrerRabbit]

I'm doing a basic membership database for my job in Excel which requires that I do a list type report of which members attended which classes. There are various classes/activities on various days of each month.

I can do a count countif sum , etc of how many LL classes there are using "*"& "LL"&"*". However I'm having difficulties doing a report using array formulas across columns using this as find criteria bcuz of how I'm listing the classes in each cell.

Due to constraints and not wanting to explode the size of the db I'm putting the date, class and location in each cell using a set criteria: 19/4 LL CAL - date, class/activity attended, and location. In the actual db each month is actually across 4 columns.

This image is a very small example of how I've set up the very basic membership database. Essentially I'm looking for the First Name and Mobile if they did LL on any day in the months in question.

 

[alansidman]

I would change the layout to a normalized table/range so that you can then do analysis which allow for pivotting and/or lookups

Book2
	A	B	C	D	E	F	G	H	I	J
1	ID	Surname	Firstname	Mobile	Month	Activity		Activity	Surname	Mobile
2	1001	Jones	James	0408238654	March	LGC		LGC	Jones	0408238654
3	1002	Johann	Scarlett	0412555898	April	19/4 LL CAL
4	1002	Johann	Scarlett	0412555898	March	19/3 LL CAL
5	1003	Jett	Jone	0458238654	April	19/4 LL CAL
Sheet1

I would then create a parameter query in Power Query

The first table (table1) is the data in columns A:F
The Mcode for that table is as follows

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Activity], Table3)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Surname", "Mobile"})
in
    #"Removed Other Columns"

the parameter lookup is located in column H
and the mcode for that lookup is

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Activity " = Source{0}[#"Activity "]
in
    #"Activity "

The result is located in columns I:J

 

[BrerRabbit]

I would change the layout to a normalized table/range so that you can then do analysis which allow for pivotting and/or lookups

Book2
	A	B	C	D	E	F	G	H	I	J
1	ID	Surname	Firstname	Mobile	Month	Activity		Activity	Surname	Mobile
2	1001	Jones	James	0408238654	March	LGC		LGC	Jones	0408238654
3	1002	Johann	Scarlett	0412555898	April	19/4 LL CAL
4	1002	Johann	Scarlett	0412555898	March	19/3 LL CAL
5	1003	Jett	Jone	0458238654	April	19/4 LL CAL
Sheet1

I would then create a parameter query in Power Query

The first table (table1) is the data in columns A:F
The Mcode for that table is as follows

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Activity], Table3)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Surname", "Mobile"})
in
    #"Removed Other Columns"

the parameter lookup is located in column H
and the mcode for that lookup is

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Activity " = Source{0}[#"Activity "]
in
    #"Activity "

The result is located in columns I:J

 

[BrerRabbit]

I'm trying to stay away from Pivot Tables as the membership db is essentiallty data entry. Pivot tables don't update automatically, the table has to be refreshed before the pivot table will update. Also, I can't get the pivot table to work the way I've worked the cells.

I'm familiar with VB but not with power query. Unfortunately I can't access either with the computer at work. I don't know why. I also can't update my original question to include these. So I do apologize. I am grateful for your effort. So I essentially need formulae, even with helper cells.

 

[alansidman]

Before you adopt any solution, you will need to normalize your data.

and for your information

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.