2 Replies Latest reply on Jul 5, 2016 10:46 AM by Ivan Young

# Compare two data sets from Excel and display details of the difference between two sets...

Hi,

I have two data sets in Excel

Excel 1

------------

Emp Id         Emp Name     Date of birth          Department

1                    A                    1/1/1999               AA

2                    B                    1/2/1999               BB

3                    C                    1/3/1999               AA

4                    D                    1/4/1999               AA

5                    E                    1/5/1999               BB

Excel 2

------------------

Emp Id         Emp Name     Date of birth          Department

1                    A                    1/1/1999               AA

2                    B                    1/2/1999               BB

5                    E                    1/5/1999               BB

I can do the row count of the above and show case that in the grid as follows:

Department        Excel 1          Excel 2          Difference (Excel 1 - Excel 2)

---------------------------------------------------------------------------

AA                         3                    1                        2                          -- This basically says there are 2 rows for AA in Excel 1 more than Excel 2

BB                         2                    2                        0                          -- This basically says there is no difference

Now when I click the Difference 2 above for AA, it should display the records from Excel 1 which are not in Excel B as follows

Emp Id         Emp Name     Date of birth          Department

3                    C                    1/3/1999               AA

4                    D                    1/4/1999               AA

How can I do this?

Need help urgently.

• ###### 1. Re: Compare two data sets from Excel and display details of the difference between two sets...

Hi Parag,

This is what I could manage. let me know if this helps in some way. While creating action from difference it takes all the 3 employees, which I couldn't fix.

Dashboard

Output (After action)

Ivan Young, can probably the best person to help us out.

Thanks and Regards,

Ashish Chaudhari

• ###### 2. Re: Compare two data sets from Excel and display details of the difference between two sets...

Hi Parag and Ashish,

Thanks for pinging me, I'm on 9.2 so I can't open your twbx Ashsih.  I'm not entirely clear on how the two data sets are related  but I'm going to assume that we are blending.  In order to generate the desired output using blending we are going to make Excel 1 the Primary and Excel 2 the Secondary data source.

Once the data sources are set up we create our Measures.

Primary Name Count: COUNTD([Name]) - Create this as a Measure for Excel1

Secondary Name Count:  COUNTD([Name]) = Create this a Measure for Excel2

Not in Secondary: [Primary Name Count] - [Sheet2 (Not In)].[Seconday Name Count] - Create this as a Measure for Excel1

We then create our source sheet. - When we select a measure we want to display the records that are in the Excel 1 with no match in Excel 2.

We then create our target sheet.

The trick to getting the output correct is to use the secondary data source Name as an exclude filter.  We will exclude all non-null values.

Then we put it together on a dashboard and create our action filter.

Attached is a twbx.  Let me know if you have any questions.

Regards,

Ivan