Combine unique data into one column from three different columns in three separate sheets

[luciddreamer_ah]

Dear Experts,

I have an Excel file, which has three different sheets which pulls data using different queries (Database and Powershell data extract..)

In a new sheet, I am looking for an option to create one column which can pull unique entries in the first column of all three sheets.

eg : Sheet A, Sheet B and Sheet C has the column 1 which has computer hostnames automatically pulled from different applications ( Opsi, WSUS, Spiceworks)

Sheet D column 1 should grab the unique hostnames in Column 1 (except the title of the column until the last ) from A,B & C.

Exception : Spiceworks fetches only hostname, not the FQDN. I am looking for a way to concatenate only for hostnames from Spiceworks sheet.

I have tried the below solution.

=IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$20)+($A$2:$A$20=""), 0)), INDEX($B$2:$B$7, MATCH(0, COUNTIF($D$1:D1, $B$2:$B$7)+($B$2:$B$7=""), 0))), INDEX($C$2:$C$12, MATCH(0, COUNTIF($D$1:D1, $C$2:$C$12)+($C$2:$C$12=""), 0))), "")

It cannot concatenate domain name to the hostname from Spiceworks and also it takes a lot of time to process the data each time.

Any solution with VBA or Power Query would be really grateful.

Thanks in Advance
A.H

 

[Saba Sabaratnam]

Hi A.H

Turn your data into table in each sheet.

Sheet A

Sheet B

Sheet C

From Get and Transform Data menu, select From table / Range

Go to File menu and Select Click and Load to and select Only Create Connection option from

Repeat the same process for table 2 and 3

You will have three connection as shown below

Go to Get and transform data and Append all tables.

Power Query will look like this

Right Click on the column and Remove Duplicates

see the final result for my data

Kind regards

Saba