'How to get unique values from a specific column from multiple sheets in a separate sheet using Excel or Google sheets?
Project1(Sheet1)
[Packages] [VersionNumber] [License]
ABC 1.0.1 MIT
Project2(Sheet2)
[Packages] [VersionNumber] [License]
ABC 1.0.1 MIT
XYZ 2.0.1 Apache
Expected Output: SummarySheet
[Packages] [VersionNumber] [License]
ABC 1.0.1 MIT
XYZ 2.0.1 Apache
How can I write a query to get unique values based on the Packages Columns in the Summary Sheet? thanks in advance!!
Solution 1:[1]
Try
=QUERY(UNIQUE({Sheet2!A2:C;Sheet3!A2:C}),"select * where Col1 is not null")
Solution 2:[2]
For Excel Office 365, assuming Sheet1 and Sheet2 are consecutive worksheets within the workbook and that A2:C3 contains the relevant data in both:
=LET(?,Sheet1:Sheet2!A2:C3,UNIQUE(INDEX(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,?)&"</b></a>","//b"),SEQUENCE(COUNTA(?)/3,3))))
Note that it would not be a good idea here to 'cover your bases' by replacing the range A2:C3 with, for example, a range comprising entire columns (A:C).
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | Jos Woolley |