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

luciddreamer_ah

New Member
Joined
Feb 18, 2021
Messages
1
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Saba Sabaratnam

Board Regular
Joined
May 26, 2018
Messages
196
Hi A.H

Turn your data into table in each sheet.

Sheet A

1613970011447.png


Sheet B


1613970025474.png


Sheet C

1613970055419.png


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

1613970127681.png



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

1613970297582.png



Repeat the same process for table 2 and 3

You will have three connection as shown below

1613970365000.png


Go to Get and transform data and Append all tables.



1613970547887.png



Power Query will look like this

1613970649900.png


Right Click on the column and Remove Duplicates

see the final result for my data


1613970723324.png


Kind regards

Saba
 

Watch MrExcel Video

Forum statistics

Threads
1,127,339
Messages
5,624,103
Members
416,011
Latest member
chengkoonwing

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top