Help

Re: Linking Cells Containing the Same Partial Text

1306 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Sean_Lieberman
4 - Data Explorer
4 - Data Explorer

This is a quirky one.

I’ve got a text column I want to compare against itself. It’s all short text.

Example:

Butterfly
Safari Hat
Fly fishing
Spiderman
Widowmaker
Makerbot
Grey Mouse
Mouse Trap
etc.

I would like to search this column and find partial text at the end that matches partial text at the beginning of another cell (and vice versa). So Butterfly matches with Fly fishing. If it could dynamically link these matches, great. I can count from there. If it just counts them, that works too. In this scenario, Butterfly should match with Fly fishing, and Fly fishing should match with Butterfly. If something else ended in “butter”, that’d count towards Butterfly. Make sense? If this is too weird and there is no easy answer, I totally get it. It would just help with a fun way to represent data that I’m toying with. Many thanks.

4 Replies 4
Sean_Lieberman
4 - Data Explorer
4 - Data Explorer

I imagine this would be a vlookup with wildcards in Excel, but I’m not a master of that either.
But maybe it’s a ‘find’ or something else here?

I can’t think of a way to do this. Keep in mind that while Airtable’s grid view looks a lot like a spreadsheet, it’s structurally very different because it’s a database. Records (rows) have no knowledge of each other, at least not natively. There are some table tricks you can use to do certain types of cross-row comparisons, but what you’re looking to accomplish is actually fairly complex. You’re trying to match a character sequence of unknown length between an unknown number of records. If the character sequence length were fixed—i.e. matching the last five characters with the first five characters—there may be certain ways to pull off a variation of that idea, but even there it would be very limited. Opening it up to try to find matches of any length is way more complex.

I’m all for weird, but even with the tricks that I know can kinda-sorta work, they will only just barely work in the right circumstances. Sorry.

Sean_Lieberman
4 - Data Explorer
4 - Data Explorer

Thanks for taking the time to think about my dilemma.

I may just have to approach it like a database instead. Or do it manually. I am beginning to realize how very complex this thing could get.

David_Shaw
6 - Interface Innovator
6 - Interface Innovator

Wildcard searches within a field are very common in other Bases. And yes I’ll mention Alpha again! Even the free ones! It is extremely annoying to try to add, for example, an inventory item that is linked to your line items or invoice.
I really don’t want to hunt through 100 different Hallmark Card, or Socket wrenches (you name it) when I want the “Fluffy Dog” or “Hammer”.
Other systems, and even a web search, will start popping up answers with just a few letters typed in. (“fluf” or “craft”. Heck - don’t even need capitals!!!
So what’s the problem here???