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

[nadiae]

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl74, width: 92"]Credit letter[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 216"]
<tbody>[TR]
[TD="class: xl74, width: 216"]Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 36"]
<tbody>[TR]
[TD="class: xl74, width: 36"]Age[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]RELATION[/TD]
[TD]CNIC[/TD]
[TD]R/B limit[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl74, width: 132"]HOSPITALIZATION[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 57"]
<tbody>[TR]
[TD="class: xl74, width: 57"]Normal[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 71"]
<tbody>[TR]
[TD="class: xl74, width: 71"]Cesarien[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD="class: xl74, width: 54"]EMP.ID[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]A-123[/TD]
[TD]JORDAN[/TD]
[TD]25[/TD]
[TD]EMPLOYEE[/TD]
[TD]205[/TD]
[TD]2500[/TD]
[TD]YES[/TD]
[TD]YES[/TD]
[TD]YES[/TD]
[TD]2500[/TD]
[/TR]
[TR]
[TD]A-123[/TD]
[TD]MARIA[/TD]
[TD]20[/TD]
[TD]SPOUSE[/TD]
[TD][/TD]
[TD]2500[/TD]
[TD]YES[/TD]
[TD]YES[/TD]
[TD]YES[/TD]
[TD]2500[/TD]
[/TR]
[TR]
[TD]A-124[/TD]
[TD]ZEM[/TD]
[TD]35[/TD]
[TD]EMPLOYEE[/TD]
[TD]206[/TD]
[TD]2600[/TD]
[TD]YES[/TD]
[TD]YES[/TD]
[TD]YES[/TD]
[TD]2600[/TD]
[/TR]
[TR]
[TD]A-124[/TD]
[TD]EM[/TD]
[TD]25[/TD]
[TD]EMPLOYEE[/TD]
[TD]206[/TD]
[TD]2600[/TD]
[TD]YES[/TD]
[TD]YES[/TD]
[TD]YES[/TD]
[TD]2600[/TD]
[/TR]
</tbody>[/TABLE]

I WANT THIS COLUMN IN BELOW MENTION FORMAT


[TABLE="width: 500"]
<tbody>[TR]
[TD]credit card[/TD]
[TD]name[/TD]
[TD]age[/TD]
[TD]relation[/TD]
[TD]cnic[/TD]
[TD]dependant 1[/TD]
[TD]relation[/TD]
[TD]age[/TD]
[TD]dependant 2[/TD]
[TD]relation [/TD]
[TD]age[/TD]
[TD]dependant 3[/TD]
[TD]relation[/TD]
[TD]age[/TD]
[TD]R/B limit[/TD]
[TD]Hospitalization[/TD]
[TD]Normal[/TD]
[TD]Cesarien[/TD]
[TD]EmpId[/TD]
[TD]Remarks[/TD]
[/TR]
[TR]
[TD]A-123[/TD]
[TD]JORDAN[/TD]
[TD][/TD]
[TD]EMPLOYEE[/TD]
[TD]205[/TD]
[TD]MARIA[/TD]
[TD]SPOUSE[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2500[/TD]
[TD]YES[/TD]
[TD]YES[/TD]
[TD]YES[/TD]
[TD]2500[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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)