Find text within a column return true or false

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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
632
Office Version
  1. 2013
Platform
  1. Windows
Try

Sheet1

Book3
ABCDEF
1JordanCharlesLogged in
2wyzcoCharlesLogged 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)
 

wyzco

New Member
Joined
Sep 28, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
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
7JordanCharlesFALSE
8JordanCharlesTRUE
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.
 

wyzco

New Member
Joined
Sep 28, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Try

Sheet1

Book3
ABCDEF
1JordanCharlesLogged in
2wyzcoCharlesLogged 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)
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.
 

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
632
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
7JordanCharlesTRUE
8JordanCharlesTRUE
9AmandaAndersonTRUE
10AmandaAndersonTRUE
11MichaelBartleyTRUE
12DevonBernardTRUE
13AdijahBlairTRUE
14RajeBrittonTRUE
15KimaniBrownFALSE
16KyleBrownFALSE
17KharaCampbellTRUE
18JordanCharlesTRUE
19BriannaClarkesTRUE
20TishawnClarkesFALSE
21NathoniaDowerFALSE
22KebrinaEdmondsonTRUE
23JordaineEvansFALSE
24PharellFowlerTRUE
25TimothyGarveyTRUE
26DexalieGrayFALSE
27KristoffHackerTRUE
28OnielHaughtonFALSE
29JoshuaHeslopFALSE
30AbigailHyltonTRUE
31MsJarrettFALSE
32JavoyJohnsonFALSE
33KingsleyKeenTRUE
34AzahniLemoniasTRUE
35RashadMartinTRUE
36JhordanMaxwellTRUE
37DemarioMillerTRUE
38RamonMooreTRUE
39
Attendance
Cell Formulas
RangeFormula
D7:D38D7=ISNUMBER(MATCH("*"&CONCATENATE(A7," ",B7,",",""," "" ✔""")&"*",'9h (2021-09-24)'!A:A,0))
 
Solution

wyzco

New Member
Joined
Sep 28, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
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
7JordanCharlesTRUE
8JordanCharlesTRUE
9AmandaAndersonTRUE
10AmandaAndersonTRUE
11MichaelBartleyTRUE
12DevonBernardTRUE
13AdijahBlairTRUE
14RajeBrittonTRUE
15KimaniBrownFALSE
16KyleBrownFALSE
17KharaCampbellTRUE
18JordanCharlesTRUE
19BriannaClarkesTRUE
20TishawnClarkesFALSE
21NathoniaDowerFALSE
22KebrinaEdmondsonTRUE
23JordaineEvansFALSE
24PharellFowlerTRUE
25TimothyGarveyTRUE
26DexalieGrayFALSE
27KristoffHackerTRUE
28OnielHaughtonFALSE
29JoshuaHeslopFALSE
30AbigailHyltonTRUE
31MsJarrettFALSE
32JavoyJohnsonFALSE
33KingsleyKeenTRUE
34AzahniLemoniasTRUE
35RashadMartinTRUE
36JhordanMaxwellTRUE
37DemarioMillerTRUE
38RamonMooreTRUE
39
Attendance
Cell Formulas
RangeFormula
D7:D38D7=ISNUMBER(MATCH("*"&CONCATENATE(A7," ",B7,",",""," "" ✔""")&"*",'9h (2021-09-24)'!A:A,0))

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
 

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
632
Office Version
  1. 2013
Platform
  1. Windows
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))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
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))
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,697
Messages
5,765,987
Members
425,320
Latest member
Galin

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
Top