The formula that I am trying to write in cell C1 will look for the first (or all, if multiple matches) starting row number of any given sequence of any number of row values written in column A amongst all values written in column B. A will have less or equal values than B and the vaues given in A are contanined in B starting from position C1.

  • 1
    I write this in good faith with your success in mind. What you've described is a difficult task, even in capable spreadsheet apps—a group of apps of which, I'd say, Numbers is not a member. It has too many missing core spreadsheet features and functions. Worse still, whereas Excel offers VBA scripting, and Sheets offers Apps Script, Numbers only has Applescript, which is very poorly suited to handling spreadsheet data. If you have any other software choices, even if just as a preliminary step before Numbers, even if just a shell script operating on a .csv, this task would be more feasible. – Joel Reid Apr 29 at 15:33
  • Ignoring scripts, a common approach would be to JOIN all non-empty rows in A with some rare delimiter into a single string, then also make text joins in B, each of the same number-of-rows-length as the A join, but with one join starting in each row of B. Then we can row-wise compare the static "joined string of A values" to each of the "joined strings of subsets of B values" into some array or helper column. I know of no way to do this in Numbers. – Joel Reid Apr 29 at 15:45
  • @JoelReid that’s exactly what I’m doing :) Since A and B takes a fixed set of values, I first used some hidden columns to convert those values to numbers, and then joining those in two strings, and finally find the substring obtained from A inside the string obtained from B. Two problems are that the number of values in A is not known and B is very large, so translating those into strings is a huge task (for my limited knowledge of Numbers fuctions) - I will really need some kind of loop here. – Claudio Floreani Apr 29 at 15:52

You must log in to answer this question.

Browse other questions tagged .