1

I have been trying and searching how to append two lists in excel to use in a formula. The lists do not exist in columns, they are created using a formula. I want to combine the two lists in a single one, not to show the values but to use the new list in a formula. I am using excel 365 (UNIQUE function). Let me replace my initial text by a real small case.

I have an excel file with 3 work sheets. Sheet1 is:

enter image description here

Sheet2 is:

enter image description here

Now I want to run some analysis in Sheet3. In my example I want to count how many unique values from column A have column B containing one of the letters 'a', 'b, 'c', or 'd'. For instance, in Sheet1, the letter 'a' appears in all rows. Column A has 3 unique values. So my result for 'a' is 3. The letter 'b' does not appear for the case where column A is '3'. Therefore the result for 'b' is '2'.

So I create a Sheet3 to show my results. The first column contains a list of letters {a, b, c, d}. I then use the formula:

=COUNT(UNIQUE(FILTER(Sheet1!$A$1:$A$100, ISNUMBER(SEARCH(A1, Sheet1!$B$1:$B$100)))))

From inside out: the SEARCH function looks in cells B1 to B100 (I can live with specifying a larger range) where is the position of the value specified in column A (of the current sheet). If it does, then SEARCH returns a number. I check if the return value is a number (ISNUMBER) and use this to filter values in column A of Sheet1. I then apply the UNIQUE function to these values and finally count them.

Then I do the same with values in Sheet2. And it works. This is the output:

enter image description here

Column B is the number of unique values (as specified above) from Sheet1 and Column C the same from Sheet2.

So far so good. But now I want to have the counting of unique values globally. Not for each Sheet. One cannot just add the values from column B and C, as there might be an overlap. For example, the result for 'a' should be 3, not 5.

The solution here would be to grab the two unique lists (one from Sheet1 and the other from Sheet2), join them, UNIQUE this new list, and count. How do I join them ? That is my question.

Note that this 'counting of unique values' is just an example. I might want to find the maximum, or sort them, or find only prime numbers, or the average, or the median, or something else. So I need a general approach to join the results.

I got options close to a workable thing when all the data is in the same worksheet.

Finally, note that the data size I have is not huge, but it is large (thousands of lines at the most).

rpsml
  • 1,486
  • 14
  • 21
  • 1
    So say `{A,B,C}` and `{D,E,F,G}` becomes `{A,B,C,D,E,F,G}`, in that order? Maybe have a look over [here](https://stackoverflow.com/q/69837142/9758194) and [here](https://stackoverflow.com/q/62204826/9758194) for ideas. Let me know if we can mark your question as a duplicate or that you feel your query is very different. – JvdV Jan 28 '22 at 10:47
  • Yes, but the order doesn't really matter. – rpsml Jan 28 '22 at 10:48
  • Unfortunately there is no obvious command in ms365's Excel, but please note that I did include some links in my comment above that seem to come very close to what it is you'r trying to achieve. – JvdV Jan 28 '22 at 11:04
  • Yes, thanks. I am looking hard into them to see if anything will work. But so far it is not obvious. The first link requires the definition of tables, which I do no want to do. There might be a solution in combining `FILTER` and `INDEX` proposed in the second link, but I am still trying to figure it out. – rpsml Jan 28 '22 at 11:12
  • You don't actually need tables. You can use your arrays instead. – Rory Jan 28 '22 at 11:24
  • @Rory I would ask you to elaborate but that would be useless. I dug into lambda functions in excel and apparently you have to be an 'excel insider' to have access to this feature. I am not. – rpsml Jan 28 '22 at 11:32
  • Can you show us what it looks like on your sheet? i.e. the two lists that is, if that makes sense. If I understand you correctly then I may have an idea for you. – Skin Jan 28 '22 at 11:37
  • 1
    I'd suggest the `FILTERXML` approach unless you have large data sets. – Rory Jan 28 '22 at 11:40
  • @Skin: one of the problems I have is that the initial data does not come from the same sheet. So, for instance, I cannot define a large area and just filter the results from within. The amount of data is large (not huge). It is from hundreds to a few thousands of lines. I'll try to come up with a minimal example and post it. – rpsml Jan 28 '22 at 11:42
  • @Rory, exactly where I was heading. – Skin Jan 28 '22 at 11:44

1 Answers1

2

Here is something you could try:

=LET(x,{"A","B","C"},y,{"D","E"},z,CHOOSE({1,2},x,y),cnt,MAX(COUNTA(x),COUNTA(y)),seq,SEQUENCE(cnt*2),final,INDEX(z,MOD(seq-1,cnt)+1,CEILING(seq/cnt,1)),FILTER(final,NOT(ISERROR(final))))

Here both 'x' and 'y' variables are placeholders for your two (vertical) arrays. In this case I used: {"A","B","C"} and {"D","E"}. Assuming you just want to place the 2nd array directly under the 1st one, the above suggestion does just that:

enter image description here

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    Wow. It does. Bravo. I'll have to spend some time to understand it. But it works. As you stated, I just replaced the two `{...}` arrays by `UNIQUE(FILTER(Sheet1!$A$1:$A$100, ISNUMBER(SEARCH(A1, Sheet1!$B$1:$B$100))))` and its version for Sheet2. Thanks. – rpsml Jan 28 '22 at 12:32