Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to Extract Unique Values from Multiple Columns using HSTACK & UNIQUE Functions. Let’s get started
SAMPLE DATA
The screenshot below shows the three columns sample dataset.
HSTACK Function
The HSTACK Function appends arrays horizontally and in sequence to return a larger array.
Syntax
=HSTACK(array1,[array2],…)
UNIQUE Function
The UNIQUE Function returns a list of unique values in a list or range.
Syntax
=UNIQUE(array,[by_col],[exactly_once])
Extract Unique Values from Multiple Columns using HSTACK & UNIQUE Functions
To extract the unique values across the three columns:
In cell F1, execute the formula: =VSTACK(A1:C1,IFNA(HSTACK(UNIQUE(A2:A10),UNIQUE(B2:B10),UNIQUE(C2:C10)),””))
Click Enter. The formula will still to columns to the right and the rows at the bottom being an array formula
Based on the above formula, we used the VSTACK to return the column headers contained in cell A1:C1. The three UNIQUE functions inside the HSTACK function, returns the unique values from range A2:A10, B2:B10 and C2:C10 respectively. Finally, the IFNA error handling function was deployed to handle the potential errors that will arrive as a result of the result not being the same in equal length.
The screenshot below, shows the result
See you in the next video