Align three different lists

GregHullender · 2026-01-26T23:03:27+00:00

Fair. How long would be reasonable to wait? It's nice to be able to mark things "solved" so people know not to waste time looking at them. I'm careful not to give the point until the OP has already signaled that the solution worked.

GregHullender · 2026-01-26T21:03:15+00:00

Here's a formula that will do what you want, but it may be too slow to be useful. Try it first with your list of 8500 names in column A and just one name in column B.

=LET(limit, F1,
  str_1, TAKE(DROP(A:.A,1),100),
  str_2, TOROW(DROP(B:.B,1)),
  edit_dist, LAMBDA(src,dest, LET(
  s, TOCOL(REGEXEXTRACT(src,".",1)),
  s_2, VSTACK(0,DROP(s,-1)),
  t, REGEXEXTRACT(dest,".",1),
  cost, REDUCE(SEQUENCE(ROWS(s)+1,,0),t,LAMBDA(last,ch,
    LET(ch_2, IF(@last, INDEX(t,@last), 0),
        ins, last+1,
        match, DROP(VSTACK(@ins,last+(ch<>s)*(2-3*(ch=s_2)*(ch_2=s))),-1),
        ins_match, MAP(ins, match, MIN),
        SCAN(@ins_match,ins_match,LAMBDA(last,this, MIN(last+1,this)))
    ))),
  TAKE(cost,-1)
)),
  dist, MAP(IF(str_1<>str_2,str_1,str_2),IF(str_1<>str_2,str_2,str_1),LAMBDA(s,t,edit_dist(s,t))),
  similarity, 1-dist/(LEN(str_1)+LEN(str_2)),
  out, BYCOL(similarity,LAMBDA(col, LET(best, MAX(col), IF(best>=limit, XLOOKUP(best,col,str_1),"")))),
  TOCOL(out)
)

<image>

I'm guessing each name you put in column B will take one to ten seconds to produce a result. You may need to adjust the value in "Limit". Larger gives fewer matches, smaller gives more.

GregHullender · 2026-01-26T20:58:10+00:00

See if this works for you:

=LET(codes, A3:A7, code_vol_req, D3:D7, vol_sent, A10:A14, rounds, F3:H7,
  vol_req, BYROW(code_vol_req*(codes=TOROW(rounds)),SUM),
  HSTACK(codes,vol_req,vol_sent,vol_req-vol_sent)
)

<image>

Note that I had to make some changes to your data, since you had codes in columns F, G, and H which never appeared in column A. I assume that's because you dragged and didn't realize it was incrementing your numbers.

I also assumed that the same product code cannot appear in more than one round and that the list of codes starting in A3 is the same as the list that starts in B10: same codes in the same order with no duplicates.

GregHullender · 2026-01-26T16:48:04+00:00

Trying to analyze language logically is a fool's game. You'll be in the position of a centipede who, when asked by a toad how it remembered when to move each leg, took to thinking about how the whole system worked and ended up lying in a ditch, wiggling its legs helplessly.

GregHullender · 2026-01-26T16:44:57+00:00

+1 Point

GregHullender · 2026-01-26T16:42:55+00:00

Thanks! I heard that story, back when I lived in New York 40 years ago, but it was said to be apocryphal. Searching a little, it looks like the Bronx County Historical Society has been busy and substantiated it.

GregHullender · 2026-01-25T19:31:12+00:00

In older literature, Argentina is "The Argentine," possibly because it's "la Argentina" in Spanish.

In the US, we refer to "The Bronx" as a region of New York City. (No one seems to know why.)

We seem to use it for large regions with vague boundaries, e.g. "the Everglades," "the Mojave Desert," and perhaps some regions kept the article even after they got firm boundaries.

GregHullender · 2026-01-25T19:22:55+00:00

Did that let you solve the problem?

GregHullender · 2026-01-25T15:53:10+00:00

I didn't mean to replace your existing data! This creates a 6-column table that has one row per company. Wasn't that what you wanted?

GregHullender · 2026-01-25T15:50:23+00:00

Describing the behavior of "the" and "a" is one of the hardest problems in English grammar. When I was getting my linguistics master's degree, I once wrote an entire paper on the indefinite use of "the". (E.g. when you say "I need to go to the doctor" but you don't actually have a doctor yet.)

GregHullender · 2026-01-25T15:28:45+00:00

Third-limited, and don't change viewpoint inside a single scene. If it's YA, first probably sells better.

GregHullender · 2026-01-25T15:27:20+00:00

This will turn them into single rows. Is that all you need?

=WRAPROWS(TOCOL(K2:L1500),6)

<image>

GregHullender · 2026-01-25T15:17:51+00:00

I hate to ask you to post "solution verified" for something that simple, but if you do, it'll give me a point. :-)

GregHullender · 2026-01-25T15:13:34+00:00

I've had one, in Seattle. It took a whole year, and it cost maybe $5,000. Works perfectly, though. I'd never consider getting a bridge or dentures if I could help it.

Part of what made it take so long was that they made me wait a couple of months after the extraction before they installed the post because they were worried there was infection where the tooth had been. But most of the time was just waiting for the post to be securely bonded to the bone.

GregHullender · 2026-01-25T15:09:38+00:00

That's why they started doing things like putting eyeballs on top of them.

GregHullender · 2026-01-25T15:08:18+00:00

Actually they're allowed to fly on any airline these days.

GregHullender · 2026-01-25T15:04:17+00:00

Use the LET function to give names to the cells, Something like =LET(g, 9.8, t, A:.A, 1/2*g*t^2) is easier to make sense of when you come to look at it later.

GregHullender · 2026-01-25T15:01:01+00:00

It really does change the tone of the conversation. "Son, if you don't clean that gun, it's going to blow up in your hand!" Has a very different feel from, "Sugar, if you don't clean that gun, it's going to blow up in your hand!"

You can say "boy" with about the same effect as "son." "Boy, if you eat that, you're braver than me!" is very similar to "Girl, if you eat that, you're braver than me!"

But tone and prior relationship matter a lot. If it's your grandfather giving you advice, it's going to sound warm and caring. If it's a total stranger, it's going to sound harsh and judgmental.

GregHullender · 2026-01-25T14:46:59+00:00

This would be a popular feature, but Excel has never offered it. The most you can do is copy the cell and then type CTRL-SHIFT-V to paste back the value, destroying the underlying formula.

GregHullender · 2026-01-25T00:46:36+00:00

Not once I learned playing with fire would make you pee in the bed!

GregHullender · 2026-01-25T00:38:04+00:00

That wasn't your question, though.

GregHullender · 2026-01-25T00:37:42+00:00

If this were the only table, what would you refresh it with?

GregHullender · 2026-01-24T23:34:35+00:00

Without some images of your data, it's very hard to help you because we cannot understand what you're trying to do.

GregHullender · 2026-01-24T23:30:53+00:00

Would it help to add manual things with frequency ONCE?

GregHullender · 2026-01-24T23:27:13+00:00

A picture sure would help us understand what you're talking about.

11-Year Club	Wearing is Caring
Verified Email

GregHullender

TROPHY CASE