0

I have a large O365 Excel spreadsheet. Sheet 1 contains multiple rows with comma separated values, together with some descriptive text, in the format:

 |            A              |    B
------------------------------------------
1| ID-A-01,ID-A-02,ID-C-02   |  lion
------------------------------------------
2| ID-A-02,ID-A-03,ID-B-01   |  monkey
------------------------------------------
3| ID-A-01,ID-B03,ID-C02     | dolphin
------------------------------------------
[etc]

Sheet 2 contains a report in the format:

 |            A              |    B
-----------------------------|-----------
1| ID-A-01                   |  
-----------------------------|   lion
2| ID-A-02                   |  monkey
-----------------------------| dolphin
3| ID-A-03                   |  
------------------------------------------
4| ID-B-01                   | 
------------------------------ 
5| ID-B-02                   |  monkey
------------------------------  dolphin
6| ID-B-03                   |
------------------------------------------
7 | ID-C-01                  |  lion
------------------------------  dolphin
8 | ID-C-02                  |
-------------------------------------------
[etc]

The problem is Sheet 2, Column B. These are merged cells. I'm trying to automatically populate them by comparing the relevant ranges in Sheet 2, Column A (A1:A3,A4:A6,A7:A8, etc) with each of comma separated lists in Sheet 1, Row A, and then executing a textjoin on Sheet 1, Column B for each match that's found.

I can achieve this for single cells on Sheet 2 with: =IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Sheet1!B1:B3,ISNUMBER(SEARCH(Sheet2!A1,Sheet1:A1:A3)))),"")

This would correctly return 'lion/dolphin' for the value 'ID-A-01' and 'lion/monkey' for 'ID-A-02', etc. But I can't get it work when I used a range of lookup values.

I've seen some posts online that indicate XML filters might work, perhaps by converting the ranges in Sheet 1, Column A into comma separated lists too, but I'm not familiar enough with Excel's XML functions to know how to make that approach work.

Does anyone have a neat solution that doesn't involve a mess of nested textjoins and filters? (I'm going to need to use this formula for hundreds of cells so it needs to be easily repeatable and modifiable) Appreciate Excel is not really designed to do this, and I'd have solved it easily with PHP/MySQL, but unfortunately Excel is the only option available.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
sycorax
  • 1
  • 1
    What logic can we use to determine where a given set of merged cells begin and ends? Looking at your example, I might assume that we can look at the central part of the IDs, i.e. the "A" in "ID-A-01", which becomes a "B", etc., though please confirm. Obviously a formula cannot detect merged cells, so there needs to be some defining logic. – Jos Woolley May 19 '23 at 12:13
  • I'd probably re-arrange the needs on sheet two. You are encoding information in merged cells - what is this based on? I'd recommend if there is a category field, then using that as another column, and then if still required, using a pivot table on this new categorised table after you've done simpler lookups. My general rule of thumb, avoid merged cells! – RowanC May 19 '23 at 12:34
  • @RowanC unfortunately I'm stuck with the layout, otherwise I agree it would be infinitely preferable to avoid merged cells – sycorax May 19 '23 at 12:42
  • @Jos Woolley - that's exactly right - on Sheet 2 the ID's are all grouped according to the central element of the ID ("-A-", "-B-", "-C-", etc), so that can used to determine where each merged cell in Column B begins and ends. – sycorax May 19 '23 at 12:44
  • @sycorax - per my response to your comment below my soln... could you also provide some sample / desired output tabular (as opposed to try to describe it in words - which is also good - but some screenshot(s) of the table in resp. sheets + desired output to clearly illustrate - will make the intended goal v. clear / less eye squinting :) ☺ ta – JB-007 May 27 '23 at 16:55
  • @sycorax (also revert to my response to your comment below my soln below pls - not sure this needs VB - but now I'm not sure I've understood exactly what you're hoping for because I was under the impression I produced the desired outcome -- apparently not ?) thanks – JB-007 May 27 '23 at 16:56

1 Answers1

1
=IFERROR(BYROW(E5:E10,LAMBDA(x_,TEXTJOIN("|",1,FILTER(C5:C7,--ISNUMBER(SEARCH(x_,B5:B7)))))),"")

byrow example

Notes:

Similar to my soln here, Office 365 features the fantastic byrow and bycol functions which, when combined with a straightforward lambda that is analogous to let, allows one to express functions such as sum, max/min, filter etc as a series. Previously such functions could not return a series as a result (either they returned an error, the first result or the operation applied to the entire series one hoped to have returned).

JB-007
  • 2,156
  • 1
  • 6
  • 22
  • Hi - I've just tried this but it seems to just achieve the same result as the formula I posted above - i.t it gives individual results for the value sin your Column E? My challenge is that in my example Sheet 2, Column B contains merged cells. I need the formula produce one consolidated response. For instance, search against ID-A-01, ID-A-02 and ID-A-03 and give a consolidated result in Column B. Ideally with any duplicate entries removed. I think I'm concluding this just can't be done without a helper row. – sycorax May 20 '23 at 14:55
  • is the screenshot I provided above in my soln incorrect (if so, how exactly? could you reference a particular cell range in your reply - e.g. if you want consolidation then must the entire array in range G5:G10 appear in one cell? is the issue with what I have that 'monkey' appears twice? although these are unique if you consider that they correspond to 2 different IDs / dolphin and donkey in E/F./ are you looking for this to reproduce what I have in cols E and F as well? I don't exactly get what is wrong - this does what you wanted as far as I can see?? might be how you implemented my sol – JB-007 May 27 '23 at 16:52