A | B | C | D | E | F |
DATE | FREE TEXT ENTRY | FORMULALEFT | FORMULAMID | FORMULARIGHT | FORMULAMID |
09/01/18 | 234 park st northwest pl1235 | =LEFT(B3,10) | =MID(B3,5,11) | =RIGHT(B3,10) | =MID(B3,6,8) |
09/01/18 | pl1235 park st nw 234 | =LEFT(B4,10) | =MID(B4,5,11) | =RIGHT(B4,10) | =MID(B4,6,8) |
09/02/18 | park st 234 85 leafing rd | =LEFT(B5,10) | =MID(B5,5,11) | =RIGHT(B5,10) | =MID(B5,6,8) |
09/02/18 | cycle 20 beard park st | =LEFT(B6,10) | =MID(B6,5,11) | =RIGHT(B6,10) | =MID(B6,6,8) |
09/03/18 | 123 red cup blvd pl9090 | =LEFT(B7,10) | =MID(B7,5,11) | =RIGHT(B7,10) | =MID(B7,6,8) |
09/03/18 | left 123 blue avenue pl902 | =LEFT(B8,10) | =MID(B8,5,11) | =RIGHT(B8,10) | =MID(B8,6,8) |
09/03/18 | red cup blvd 123 pl9090 | =LEFT(B9,10) | =MID(B9,5,11) | =RIGHT(B9,10) | =MID(B9,6,8) |
09/04/18 | 89 e san juan pl northeast | =LEFT(B10,10) | =MID(B10,5,11) | =RIGHT(B10,10) | =MID(B10,6,8) |
<tbody>
</tbody>
I have a table which contains a:
COLUMN A Date column
COLUMN B text string column
COLUMN C a column with a LEFT formula that extracts 10 characters
COLUMN D a column with a MID formula that extracts 11 characters, after 5 spaces
COLUMN E a column with a RIGHT formula that extracts 10 characters
COLUMN F another column with a MID formula that extracts 8 characters, after 6 spaces
The problem and target is COLUMN B. I want to find all entries that match most or all of the words in this cell but this is a free text field and 100 employees have completed the entries in various order. Want to count all of those with the same entry as 1 but only if it has the same date.
I created COLUMNS C through COLUMN F in a effort to cut up the text string and then search for it throughout the table but I'm not doing a good job of it NOR have I been able to ONLY count if has the same date.
For example the table below shows:
2 for 09/01/2018 of those with "234" "park" "st"
1 for 09/02/2018 of those with "park" "st" "234" "85" "leafing" "rd"
1 for 09/02/2018 of those with "cycle" "20" "beard" "park" "st"
1 for 09/03/2018 of those with "123" "red" "cup" "blvd"
1 for 09/03/2018 of those with "left" "123" "blue"
1 for 09/03/2018 of those with "red" "cup" "123"
1 for 09/04/2018 of those with "89 e san juan pl"
I know I cannot use all of the words/contents located in the cell but would like to find most. I would appreciate your help immensely...I have 3 years of data like this.