How to get unique values from a specific column from multiple sheets in a separate sheet using Excel-CodePudding

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!!

CodePudding user response：

Try

=QUERY(UNIQUE({Sheet2!A2:C;Sheet3!A2:C}),"select * where Col1 is not null")

CodePudding user response：

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