Unique value from database

[Adnan Halai]

Hi Team,

thank you for amazing platform.

I was working on one raw database where in I want to derived unique value of two columns. I have used Pivot table but when I connect data with Formula, Due to blank in pivot, formula didnt work properly.

Can you some please help. Attached is the sample file.

 

[NARAYANK991]

Hi ,

If you use Excel 2010 and above , you can check the item named Repeat Item Labels to ensure that there are no blank Row Label cells in the pivot table.

Narayan

 

[Chihiro]

If you have access to PowerQuery. This is very easy.

1. Data Get & Transform (Excel 2016) or PowerQuery (Excel 2010, 2013) tab and choose "From Table"

2. Remove all but Employee Name & Project code

3. Highlight both columns and right click ->"Remove Duplicates"


Similar operation can also be done via MS Query.

Follow steps outlined in my post found in link.
http://forum.chandoo.org/threads/su...m-range-of-unique-data-set.30416/#post-181482

To update connection string via VBA when file name or location changes. See bottom of linked page and modify code to suite your need.
http://www.contextures.com/excelmsquerycartesian.html

Use SQL statement like below. Replace path with your file path.

SELECT DISTINCT `TEST$`.`Employee Name`, `TEST$`.`Project code`
FROM `C:\YourFilePath\Data Collection.xlsx`.`TEST$` `TEST$`