Nov 16 2021 12:01 PM
Nov 16 2021 12:01 PM
I'm using the formula below to find people who match the name in cell G407. The "Moraga" worksheet in the Lafayette file has cells with a lot of text, including text that matches the name in G407. The formula below gives me the first instance of a match. I want to change the formula below to find the 2nd match in the Moraga worksheet. Any help would be greatly appreciated.
A cell in the Moraga worksheet might look like this: smith trust, john smith, candy and john smith trustee. If I am looking for a Smith, this cell would work and I would get the value or text in the corresponding column E.
Thanks in advance for your help.
Nov 16 2021 01:43 PM
Nov 16 2021 02:52 PM - edited Nov 16 2021 03:59 PMSolution
@mtarler I have 365. I have it almost working now. Sometimes, the formula returns information from the first cell/column ("C") and other times it returns info from the next cell/column ("D"). I just figured out when it does this: if there is not another instance of the G407 term, the formula gives me the number in cell D - it shifts to the right, instead of down. It would be much better if it can give me the result from the only time the name can be found. If you can fix this, I will be grateful.
IGNORE my other messages.
Nov 16 2021 03:17 PM
Nov 16 2021 03:24 PM
Nov 16 2021 07:30 PM
Nov 19 2021 12:24 AM
Nov 19 2021 12:46 AM
Nov 19 2021 06:34 AM
with respect to only whole words that creates a problem because if you just add a space before and after then if that word is the 1st or last word in the cell then it probably won't have a space. it would be so much easier if i had a sheet to play so I create a mini sheet (attached) created this LET() formula:
=LET(in, '[Lafayette.xlsx]Moraga'!$C:$P, key, G407, inC, INDEX(in, , 1), inCloc, IFERROR(SEARCH(key,inC),-1), keyfinds, TRIM(SWITCH(inCloc,-1,FALSE,1,LEFT(inC,LEN(key)+1),MID(inC,inCloc-1,LEN(key)+2))), pass, key=keyfinds, fset, FILTER(in,pass,0), out,IFERROR(INDEX(fset,2,),INDEX(fset,1,)), out )
As for how it returns the additional cells is that the formula is using
... FILTER('[Lafayette.xlsx]Moraga'!$C:$P, ...
so it pulls columns C:P and filters for only the rows that meet the criteria that follows in:
which basically searched each value in column C for the value in G407 and returns true if found or false if not found. in the above LET() version i do a bit more to first determine which rows "pass" or not and then use that "pass" variable to filter the "in"put range.
BTW, in that LET() statement you define the area to search next to "in", the search key you want to look up next to "key" and it assumes you are using the 1st column based on the inC (i.e. in column) being set to 1 in the line: inC, INDEX(in, , 1),
Nov 19 2021 03:46 PM
@mtarler I'm feeling SUPER guilty about your time, but I'm going to update you a sample spreadsheet so you can see what the Moraga file looks like. I (actually, YOU) have it working pretty well, with one glaring exception. When there is only 1 record in the Moraga file that matches the name, the formula returns a number (from the adjacent cell). That's okay because I then sorted the spreadsheet and changed the formula for all of the numbers from 2 to 1. It returned the correct information for the homeowner, BUT it did not return all of the other information in the adjacent cells. I'm guessing that somehow the second iteration triggers everything else (when it's a 2, not a 1). Is there a way to get the 1 to have the same impact? Just to clarify, if the search term is Poggetto and there isn't another Poggetto in the file, the formula will return 38 because it's in the cell adjacent to the cell that includes Poggetto.
Also, I'm having trouble following your explanation about how to only get standalone versions of the search term, but I will try again when I'm more alert. Thanks so much for all of your help. When we're finally done here, I'd love to send you a thank you gift.
Nov 19 2021 04:04 PM
Nov 19 2021 04:32 PM
@mtarler Hi Matt, I just saw your profile - that's how I know your name. I've attached the same spreadsheet, but I added two rows at the bottom (from the Moraga spreadsheet). Your formula was perfect for one of the names, but it didn't find the other one. I hope this helps. Honestly, I don't understand how your formula really works so I was hesitant to play with it.
Nov 19 2021 05:20 PM
@marcwithak629 I can't find where you pasted the formula into that sample sheet but I think I know the problem.
on line 15 is the name value: "Richard J Choo, May Y CHOO"
the formula searches and finds the FIRST instance of Choo and checks if a) it is at the start or end of the field and has a 1 space on the opposite side OR b) it is in the middle somewhere with a space on BOTH sides. In this case there is a "," comma after the name. NOT a case I took into account.
I added that specific case into the logic. Are there other special cases???
Nov 19 2021 06:48 PM
@mtarler I am having trouble understanding how I should change my most recent formula (prior to your adding the comma logic). I'm attaching a word document with the current formula and a few brief comments. I'm hoping that you can update the formula to reflect your comma logic. Then, I will copy and paste it into my spreadsheet. I really don't understand how all of this works, so I'm just copying and pasting whatever you provide.
Nov 20 2021 07:13 AM
@marcwithak629 OK here is an updated formula for you to try using your input range and search cell:
=LET(in, [blockshopperLamorindawithPurchaseDates.xlsx]Moraga!$C:$P, key, H966, inC, INDEX(in, , 1), inCloc, IFERROR(SEARCH(key,inC),-1), keyfinds, TRIM(SWITCH(inCloc,-1,FALSE,1,LEFT(inC,LEN(key)+1),MID(inC,inCloc-1,LEN(key)+2))), pass, IFS(key=keyfinds,TRUE, (key&",")=keyfinds,TRUE,TRUE,FALSE), fset, FILTER(in,pass,0), out,IFERROR(INDEX(fset,2,),INDEX(fset,1,)), out )
Nov 21 2021 01:30 PM - edited Nov 21 2021 01:34 PM
You're freaking amazing! Thank you so much!!!
P.S. My dad is also a Cornell alum!
Nov 21 2021 03:53 PM
@mtarlerBelow is a small spreadsheet with names that didn't work and my theories. I'm guessing this will be a simple fix. Thanks (yet) again. The system wouldn't let me upload the spreadsheet.
|Column H||My Theory||Blockshopper File|
|Kish||Hyphen before & no space after kish||Aimee Falk-Kish, Jeremy M. Kish|
|Nakhle||Space before, and comma after||Ali Antoun Nakhle, Carol Theokary|
|Kung||Comma after and no space after last name.||Chung Y Kung, Joanna W Kung|
|Helder||Commas or no space after||George K Helder, Judith M Helder, Tr Helder|
|Walloch||Commas or no space after||Justin R Walloch, F. Walloch Walloch, Jennifer B. Walloch|
|McKillen||Hmmm? 2nd McKillen has two spaces.||Ross Sakamoto, Angela McKillen, Sakamoto R, Angela McKillen R, Tr Ross|
|Volkmer||Commas and no space after||Werner G Volkmer, Gisela R Volkmer, Volkmer|
|McLeod||Comma and no space after||William D McLeod, Mary A McLeod|
|The names here are from column H in our file (the ones we're looking for). The Blockshopper File is a copy of what is in our Blockshopper file.|
|None of these searches worked because the name did not have two spaces before and after. Keep in mind that if the name is the last name in the cell, it won't be followed by a space. Perhaps it can have a space and/or hyphen before or after|
Nov 21 2021 06:59 PM