Data transpose in row from different column but primary information should not repeat

[nadiae]

Credit letter <tbody> </tbody>	Name <tbody> </tbody>	Age <tbody> </tbody>	RELATION	CNIC	R/B limit	HOSPITALIZATION <tbody> </tbody>	Normal <tbody> </tbody>	Cesarien <tbody> </tbody>	EMP.ID <tbody> </tbody>
A-123	JORDAN	25	EMPLOYEE	205	2500	YES	YES	YES	2500
A-123	MARIA	20	SPOUSE		2500	YES	YES	YES	2500
A-124	ZEM	35	EMPLOYEE	206	2600	YES	YES	YES	2600
A-124	EM	25	EMPLOYEE	206	2600	YES	YES	YES	2600

<tbody>
</tbody>

I WANT THIS COLUMN IN BELOW MENTION FORMAT

credit card

name

age

relation

cnic

dependant 1

relation

age

dependant 2

relation

age

dependant 3

relation

age

R/B limit

Hospitalization

Normal

Cesarien

EmpId

Remarks

A-123

JORDAN

EMPLOYEE

205

MARIA

SPOUSE

20

2500

YES

YES

YES

2500

<tbody>
</tbody>

I WANT ALL DEPENDENT INFORMATION IN ONE ROW. HERE CREDIT LETTER IS THE PRIMARY KEY AFTER COMING IN ONE ROW IT SHOULD NOT REPEAT. NEXT ROW WILL START WITH A-124 SECOND EMPLOYEE AND HIS DEPENDENT INFORMATION.

REGARDS,
NADIA

 

[Tianbas]

Hi Nadia,

Can you explain how these rows are related beside the Credit letter ? How do you know if the row belongs to a person you want to have in column 2 or in one of the dependant columns ?

regards,
Tianbas

P.S. something seems to be wrong with your Shift-key

 

[nadiae]

postimg.org/image/66hmttpuh/


postimg.org/image/ignku4e1f/


I am new to this blog so dont know how to upload the image. please paste above mention links in your browser to review the files.

so please click on external links to view the actual excel and required excel sheet.


Regards,
Nadia

 

[Tianbas]

I think Power Pivot / Power BI might not be the best option to solve your issue. It would be easier in a database or even in standard Excel. If this is not possible you could try to import the table twice in PowerPivot filtering the second on relation=employee and then try to add calculated columns for the data of the dependants. But as I cant see a column or a clear rule that says this row is dependant 1 and this is dependant 2 this will be difficult.

Maybe one of the more experienced guys can help

 

[scottsen]

So, you want the "wide" style (more columns, less rows). That's generally opposite of what I try to do in Power Pivot... but that does leave me with a question

Can you confirm you are using the Power Pivot product? (which is separate addin from "standard" Excel)