CODEWITHSUNDEEP
excelexcel-formulaexcel-365
rpsml
rpsml

Reputation: 1498

Appending two lists in excel

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).

Upvotes: 1

Views: 1951

Answers (2)

DataBunny
DataBunny

Reputation: 185

A new set of array functions makes this even more obvious. UNIQUE(VSTACK(list1, list2)) is all you need.

Upvotes: 1

JvdV
JvdV

Reputation: 75910

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

Upvotes: 2

Related Questions