Using UNIQUE with non-adjecent columns on different sheets

by

Since finding the Union of several ranges is a quite usefull function on its own, I use a LAMBDA to do that. The output of that can then be passed to UNIQUE

The Lambda, which I call, unimaginatively, UNION

=LAMBDA(tabl1, tabl2,
        LET(rowindex, SEQUENCE(ROWS(tabl1)+ROWS(tabl2)),
            colindex, SEQUENCE(1,COLUMNS(tabl1)),
            IF(rowindex<=ROWS(tabl1), 
               INDEX(tabl1,rowindex,colindex),  
               INDEX(tabl2,rowindex-ROWS(tabl1),colindex)
            )
        )
 )

Then

=UNIQUE(Union(tblFruits1[Name],tblFruits2[Name]))

gives the result you seek

enter image description here

More Related Contents:

  1. Excel UNIQUE Across Columns
  2. How to split texts from dynamic range?
  3. Calculation is wrong, Rounding wrong
  4. Excel formula checklist in employees
  5. Sequence of numbers based on start and end value of 2 cells [closed]
  6. I have an Excel data sheet that I would like to extract data from into a new sheet
  7. Error in finding last used cell in Excel with VBA
  8. Excel VBA – How to add dynamic array formula
  9. Getting unique values in Excel by using formulas only
  10. TEXTJOIN for xl2010/xl2013 with criteria
  11. Vlookup using 2 columns to reference another
  12. Difference between date and time w/out work week Excel
  13. Ignore Duplicates and Create New List of Unique Values in Excel
  14. Excel Formula for Inventory
  15. Count Unique values with a condition
  16. Two column lookup in table array using INDEX and MATCH
  17. Concatenate column headers if value in rows below is non-blank
  18. If two cells match, return value from third
  19. What does — do in Excel formulas?
  20. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  21. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  22. Using the value in a cell as a cell reference in a formula?
  23. Countif With Multiple OR Criteria
  24. Excel – extracting top 5 values
  25. Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted)
  26. CountIf With Filtered Data
  27. How to count cells in a range with a value less than another cell in excel?
  28. Extract the last substring from a cell
  29. How to extract text within a string of text
  30. Excel: How to gather unique values in one column that are associated with duplicates in another column?

Leave a Comment