Find text within a column return true or false

W

wyzco

New Member
Joined
Sep 28, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
I have a column that contains a list of data in each cell. The Name followed by a "✔" if the person was present and the 7th "" indicates how much time the individual logged in.
shown below
Jordan Charles, " ✔", "", "", "9:48", "10:07", "20", "1"

I would like to search if a Name and ✔ is present in any cell.
When I utilize this formula to search for the name only (A18:B18) it works.
Excel Formula: 
=SUMPRODUCT(--ISNUMBER(SEARCH(A18:B18;'9h (2021-09-24)'!A:A)))=COUNTA(A18:B18)
2021-09-28 (1).png

However when a search that includes Name and "✔" (A18:C18) which indicates if the person is present it fails.

Is there any way I could search to determine if the name is in the list and if the person was present (✔) or even to use the 7th "" which indicates if the person logged in?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Try

Sheet1

Book3
ABCDEF
1JordanCharles✔Logged in
2wyzcoCharles✔Logged in
3JordanCharlesNot Logged in
4
5
Sheet1
Cell Formulas
RangeFormula
E1:E3E1=IF(COUNTIFS('9h (2021-09-24)'!A:A,Sheet1!A1,'9h (2021-09-24)'!C:C,Sheet1!C1)>0,"Logged in","Not Logged in")



Another sheet "9h (2021-09-24)"

Book3
ABCDE
1JordanCharles✔
2wyzcoCharles✔
3
4
5
6
9h (2021-09-24)
 
Upvote 0
My apologies, this would be what is on the data sheet consisting of the names and ✔.

Book4.xlsx
A
1Attendance for:,"9h"
2Date:, "2021-09-24", "Time:", "9:35", "Meet ID:", "sfh-szqv-eri"
3
4Names, "2021-09-24", "Email", "Comments", "Arrival time", "Last Seen", "# of Checks", "Joined", "Details"
5Amanda Anderson, " ✔", "", "", "9:40", "10:07", "28", "1"
6Michael Bartley, " ✔", "", "", "9:35", "10:07", "33", "1"
7Devon Bernard, " ✔", "", "", "9:35", "9:37", "35", "3", "9:38 (32min) [ 10:09 ]","9:35 (1min) [ 9:35 ]","9:36 (2min) [ 9:37 ]"
8Adijah Blair, " ✔", "", "", "9:37", "10:07", "31", "1", /
9Raje Britton, " ✔", "", "", "10:02", "10:09", "8", "1"
10Kimani Brown, "", "", ""
11Kyle-Anthony Brown, "", "", ""
12Khara Campbell, " ✔", "", "", "9:35", "10:07", "33", "1"
13Jordan Charles, " ✔", "", "", "9:48", "10:07", "20", "1"
14Brianna Clarkes, " ✔", "", "", "9:35", "10:07", "33", "1"
15Tishawn Clarkes, "", "", ""
16Nathonia Dower, "", "", ""
17Kebrina Edmondson, " ✔", "", "", "9:37", "9:51", "27", "3", "9:37 (12min) [ 9:48 ]","9:49 (3min) [ 9:51 ]","9:56 (12min) [ 10:07 ]"
18Jordaine Evans, "", "", ""
19Pharell Fowler, " ✔", "", "", "9:43", "10:08", "26", "1"
20Timothy Garvey, " ✔", "", "", "9:44", "10:06", "23", "1"
21Dexalie Gray, "", "", ""
22Kristoff Hacker, " ✔", "", "", "9:42", "9:49", "22", "2", "9:50 (14min) [ 10:03 ]","9:42 (8min) [ 9:49 ]"
23Oniel Haughton, "", "", ""
24Joshua Heslop, "", "", ""
25Abigail Hylton, " ✔", "", "", "9:37", "10:07", "31", "1"
26Ms. S. Jarrett, "", "", ""
27Javoy Johnson, "", "", ""
28Kingsley Keen, " ✔", "", "", "9:52", "10:07", "16", "1"
29Azahni Lemonias, " ✔", "", "", "9:49", "10:07", "19", "1"
30Rashad Martin, " ✔", "", "", "9:36", "10:07", "32", "1"
31Jhordan Maxwell, " ✔", "", "", "9:37", "10:07", "31", "1"
32Demario Miller, " ✔", "", "", "9:37", "9:37", "31", "2", "9:38 (30min) [ 10:07 ]","9:37 (1min) [ 9:37 ]"
33Ramon Moore, " ✔", "", "", "9:39", "10:07", "29", "1"
9h (2021-09-24)


The next sheet consist of formula extracting the information from the data sheet
Book4.xlsx
ABCDE
4Sept. 13, 2021Sept. 14, 2022
5First NameLast NamePresent symbol to search for
6
7JordanCharles✔FALSE
8JordanCharles✔TRUE
Attendance
Cell Formulas
RangeFormula
D7D7=SUMPRODUCT(--ISNUMBER(SEARCH(A7:C7,'9h (2021-09-24)'!A:A)))=COUNTA(A7:C7)
D8D8=SUMPRODUCT(--ISNUMBER(SEARCH(A8:B8,'9h (2021-09-24)'!A:A)))=COUNTA(A8:B8)


I hope that better shows what I am trying to achieve.

That is to search within 9h (2021-09-24) sheet to find a name and ✔, if all are within a cell, return True on Attendance sheet.
 
Upvote 0
Sufiyan97 said:
Try

Sheet1

Book3
ABCDEF
1JordanCharles✔Logged in
2wyzcoCharles✔Logged in
3JordanCharlesNot Logged in
4
5
Sheet1
Cell Formulas
RangeFormula
E1:E3E1=IF(COUNTIFS('9h (2021-09-24)'!A:A,Sheet1!A1,'9h (2021-09-24)'!C:C,Sheet1!C1)>0,"Logged in","Not Logged in")



Another sheet "9h (2021-09-24)"

Book3
ABCDE
1JordanCharles✔
2wyzcoCharles✔
3
4
5
6
9h (2021-09-24)
Click to expand...
I repost the using the mini-sheet to provide more clarity. my apologies and thank you for your assistance thus far. I would greatly appreciate if you can review my updated explanation to see if/how it could be resolved.
 
Upvote 0
Try

Book5
AB
1Attendance for:,"9h"
2Date:, "2021-09-24", "Time:", "9:35", "Meet ID:", "sfh-szqv-eri"
3
4Names, "2021-09-24", "Email", "Comments", "Arrival time", "Last Seen", "# of Checks", "Joined", "Details"
5Amanda Anderson, " ✔", "", "", "9:40", "10:07", "28", "1"
6Michael Bartley, " ✔", "", "", "9:35", "10:07", "33", "1"
7Devon Bernard, " ✔", "", "", "9:35", "9:37", "35", "3", "9:38 (32min) [ 10:09 ]","9:35 (1min) [ 9:35 ]","9:36 (2min) [ 9:37 ]"
8Adijah Blair, " ✔", "", "", "9:37", "10:07", "31", "1", /
9Raje Britton, " ✔", "", "", "10:02", "10:09", "8", "1"
10Kimani Brown, "", "", ""
11Kyle-Anthony Brown, "", "", ""
12Khara Campbell, " ✔", "", "", "9:35", "10:07", "33", "1"
13Jordan Charles, " ✔", "", "", "9:48", "10:07", "20", "1"
14Brianna Clarkes, " ✔", "", "", "9:35", "10:07", "33", "1"
15Tishawn Clarkes, "", "", ""
16Nathonia Dower, "", "", ""
17Kebrina Edmondson, " ✔", "", "", "9:37", "9:51", "27", "3", "9:37 (12min) [ 9:48 ]","9:49 (3min) [ 9:51 ]","9:56 (12min) [ 10:07 ]"
18Jordaine Evans, "", "", ""
19Pharell Fowler, " ✔", "", "", "9:43", "10:08", "26", "1"
20Timothy Garvey, " ✔", "", "", "9:44", "10:06", "23", "1"
21Dexalie Gray, "", "", ""
22Kristoff Hacker, " ✔", "", "", "9:42", "9:49", "22", "2", "9:50 (14min) [ 10:03 ]","9:42 (8min) [ 9:49 ]"
23Oniel Haughton, "", "", ""
24Joshua Heslop, "", "", ""
25Abigail Hylton, " ✔", "", "", "9:37", "10:07", "31", "1"
26Ms. S. Jarrett, "", "", ""
27Javoy Johnson, "", "", ""
28Kingsley Keen, " ✔", "", "", "9:52", "10:07", "16", "1"
29Azahni Lemonias, " ✔", "", "", "9:49", "10:07", "19", "1"
30Rashad Martin, " ✔", "", "", "9:36", "10:07", "32", "1"
31Jhordan Maxwell, " ✔", "", "", "9:37", "10:07", "31", "1"
32Demario Miller, " ✔", "", "", "9:37", "9:37", "31", "2", "9:38 (30min) [ 10:07 ]","9:37 (1min) [ 9:37 ]"
33Ramon Moore, " ✔", "", "", "9:39", "10:07", "29", "1"
34
35
36
37
9h (2021-09-24)


The next sheet consist of formula extracting the information from the data sheet

Book5
ABCDE
1
2
3
4Sept. 13, 2021
5First NameLast NamePresent symbol to search for
6
7JordanCharles✔TRUE
8JordanCharles✔TRUE
9AmandaAnderson✔TRUE
10AmandaAnderson✔TRUE
11MichaelBartley✔TRUE
12DevonBernard✔TRUE
13AdijahBlair✔TRUE
14RajeBritton✔TRUE
15KimaniBrown✔FALSE
16KyleBrown✔FALSE
17KharaCampbell✔TRUE
18JordanCharles✔TRUE
19BriannaClarkes✔TRUE
20TishawnClarkes✔FALSE
21NathoniaDower✔FALSE
22KebrinaEdmondson✔TRUE
23JordaineEvans✔FALSE
24PharellFowler✔TRUE
25TimothyGarvey✔TRUE
26DexalieGray✔FALSE
27KristoffHacker✔TRUE
28OnielHaughton✔FALSE
29JoshuaHeslop✔FALSE
30AbigailHylton✔TRUE
31MsJarrett✔FALSE
32JavoyJohnson✔FALSE
33KingsleyKeen✔TRUE
34AzahniLemonias✔TRUE
35RashadMartin✔TRUE
36JhordanMaxwell✔TRUE
37DemarioMiller✔TRUE
38RamonMoore✔TRUE
39
Attendance
Cell Formulas
RangeFormula
D7:D38D7=ISNUMBER(MATCH("*"&CONCATENATE(A7," ",B7,",",""," "" ✔""")&"*",'9h (2021-09-24)'!A:A,0))
 
Upvote 0
Solution
Sufiyan97 said:
Try

Book5
AB
1Attendance for:,"9h"
2Date:, "2021-09-24", "Time:", "9:35", "Meet ID:", "sfh-szqv-eri"
3
4Names, "2021-09-24", "Email", "Comments", "Arrival time", "Last Seen", "# of Checks", "Joined", "Details"
5Amanda Anderson, " ✔", "", "", "9:40", "10:07", "28", "1"
6Michael Bartley, " ✔", "", "", "9:35", "10:07", "33", "1"
7Devon Bernard, " ✔", "", "", "9:35", "9:37", "35", "3", "9:38 (32min) [ 10:09 ]","9:35 (1min) [ 9:35 ]","9:36 (2min) [ 9:37 ]"
8Adijah Blair, " ✔", "", "", "9:37", "10:07", "31", "1", /
9Raje Britton, " ✔", "", "", "10:02", "10:09", "8", "1"
10Kimani Brown, "", "", ""
11Kyle-Anthony Brown, "", "", ""
12Khara Campbell, " ✔", "", "", "9:35", "10:07", "33", "1"
13Jordan Charles, " ✔", "", "", "9:48", "10:07", "20", "1"
14Brianna Clarkes, " ✔", "", "", "9:35", "10:07", "33", "1"
15Tishawn Clarkes, "", "", ""
16Nathonia Dower, "", "", ""
17Kebrina Edmondson, " ✔", "", "", "9:37", "9:51", "27", "3", "9:37 (12min) [ 9:48 ]","9:49 (3min) [ 9:51 ]","9:56 (12min) [ 10:07 ]"
18Jordaine Evans, "", "", ""
19Pharell Fowler, " ✔", "", "", "9:43", "10:08", "26", "1"
20Timothy Garvey, " ✔", "", "", "9:44", "10:06", "23", "1"
21Dexalie Gray, "", "", ""
22Kristoff Hacker, " ✔", "", "", "9:42", "9:49", "22", "2", "9:50 (14min) [ 10:03 ]","9:42 (8min) [ 9:49 ]"
23Oniel Haughton, "", "", ""
24Joshua Heslop, "", "", ""
25Abigail Hylton, " ✔", "", "", "9:37", "10:07", "31", "1"
26Ms. S. Jarrett, "", "", ""
27Javoy Johnson, "", "", ""
28Kingsley Keen, " ✔", "", "", "9:52", "10:07", "16", "1"
29Azahni Lemonias, " ✔", "", "", "9:49", "10:07", "19", "1"
30Rashad Martin, " ✔", "", "", "9:36", "10:07", "32", "1"
31Jhordan Maxwell, " ✔", "", "", "9:37", "10:07", "31", "1"
32Demario Miller, " ✔", "", "", "9:37", "9:37", "31", "2", "9:38 (30min) [ 10:07 ]","9:37 (1min) [ 9:37 ]"
33Ramon Moore, " ✔", "", "", "9:39", "10:07", "29", "1"
34
35
36
37
9h (2021-09-24)


The next sheet consist of formula extracting the information from the data sheet

Book5
ABCDE
1
2
3
4Sept. 13, 2021
5First NameLast NamePresent symbol to search for
6
7JordanCharles✔TRUE
8JordanCharles✔TRUE
9AmandaAnderson✔TRUE
10AmandaAnderson✔TRUE
11MichaelBartley✔TRUE
12DevonBernard✔TRUE
13AdijahBlair✔TRUE
14RajeBritton✔TRUE
15KimaniBrown✔FALSE
16KyleBrown✔FALSE
17KharaCampbell✔TRUE
18JordanCharles✔TRUE
19BriannaClarkes✔TRUE
20TishawnClarkes✔FALSE
21NathoniaDower✔FALSE
22KebrinaEdmondson✔TRUE
23JordaineEvans✔FALSE
24PharellFowler✔TRUE
25TimothyGarvey✔TRUE
26DexalieGray✔FALSE
27KristoffHacker✔TRUE
28OnielHaughton✔FALSE
29JoshuaHeslop✔FALSE
30AbigailHylton✔TRUE
31MsJarrett✔FALSE
32JavoyJohnson✔FALSE
33KingsleyKeen✔TRUE
34AzahniLemonias✔TRUE
35RashadMartin✔TRUE
36JhordanMaxwell✔TRUE
37DemarioMiller✔TRUE
38RamonMoore✔TRUE
39
Attendance
Cell Formulas
RangeFormula
D7:D38D7=ISNUMBER(MATCH("*"&CONCATENATE(A7," ",B7,",",""," "" ✔""")&"*",'9h (2021-09-24)'!A:A,0))
Click to expand...

I tried using the formula
Excel Formula: 
=ISNUMBER(MATCH("*"&CONCATENATE(A7," ",B7,",",""," "" ✔""")&"*",'9h (2021-09-24)'!A:A,0))
However when I paste it, it give a formula error, I suspect it has to do with the commas, as it suggest changing to semi-colon however it keeps failing.
Can you paste the exact formula you used or is it a issue with how I am inputting the formula?

2021-09-29.png
 
Upvote 0
Just paste this formula in cell D7 in Attendance sheet and drag down

Excel Formula: 
=ISNUMBER(MATCH("*"&CONCATENATE(A7," ",B7,",",""," "" ✔""")&"*",'9h (2021-09-24)'!A:A,0))
 
Upvote 0
If your system uses semicolons as separators you would need to use
Excel Formula: 
=ISNUMBER(MATCH("*"&CONCATENATE(A7;" ";B7;",";"";" "" ✔""")&"*";'9h (2021-09-24)'!A:A;0))
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
validate a character string within a cell to a list and return the value found
2
Replies
10
Views
921
Peter_SSs
Peter_SSs
L
  • Solved
Looking for an sequence of consecutive days within an array of dates while ignoring weekends
Replies
6
Views
579
Anonymous1378
A
K
  • Question
AVERAGEIFS / SUMIF with multiple criteria
Replies
3
Views
830
arthurbr
arthurbr
K
  • Question
help with VBA to find specific text within a string and within a specific range
Replies
1
Views
2K
kbishop94
K
K
  • Question
Cell Formatting When Using Index Match
Replies
4
Views
641
kxellis26
K

Forum statistics

Threads
1,214,976
Messages
6,122,541
Members
449,089
Latest member
davidcom

Share this page

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