Display the matching data which is present in other sheet

[kamalakar1980]

HI Team,

i team trying to create a power pivot out to two source of data present in two different sheets in a single workbook.

there is a common column present in both of these sheets. can you please help me on this.

lets have a look on the below example :

sheet1 contains (Employee Table)
[TABLE="width: 415"]
<TBODY>[TR]
[TD]EmpID[/TD]
[TD]Name[/TD]
[TD]EmP Account Master[/TD]
[TD]Overall Status[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Kamal[/TD]
[TD]A1[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Ganesh[/TD]
[TD]A2[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Balaji[/TD]
[TD]A3[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Kamal[/TD]
[TD]A4[/TD]
[TD]Fail[/TD]
[/TR]
</TBODY><COLGROUP><COL span=2><COL><COL></COLGROUP>[/TABLE]
[TABLE="width: 415"]
<TBODY>[TR]
[TD]
Sheet2contains

[TABLE="width: 229"]
<TBODY>[TR]
[TD]EmpID[/TD]
[TD]Account Details Master[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]M1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]M2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]M3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]M4[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]


output should be

[TABLE="width: 485"]
<TBODY>[TR]
[TD]EmpID </SPAN>[/TD]
[TD]EmpAccountMaster</SPAN>[/TD]
[TD]AccountDetailsMaster</SPAN>[/TD]
[TD]Overll Status</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1</SPAN>[/TD]
[TD]A1</SPAN>[/TD]
[TD]M1</SPAN>[/TD]
[TD]Pass</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A4</SPAN>[/TD]
[TD]M4</SPAN>[/TD]
[TD]Fail</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>[/TD]
[TD]A2</SPAN>[/TD]
[TD]M2</SPAN>[/TD]
[TD]Pass</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>[/TD]
[TD]A3</SPAN>[/TD]
[TD]M3</SPAN>[/TD]
[TD]Pass</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL span=2><COL><COL></COLGROUP>[/TABLE]

 

[pete234]

You need a unique rep table in the middle of these tables.
You have Kamal twice so that won't work in this current set up.
So make a table of unique employees by ID.
Do this in another sheet and then create a linked table of all 3 sheets to make them power pivot.
Then create a relationship between the Emp Table to your Unique Emp Table and the master account table by ID to your new emp table.
You should make sure the ID will stay unique and isn't a position id, that could be replaced by another employee.
By this you should be able to pick the data from each table you need. Use the Employee ID from your new table to create the basis of your pivot.