Data Correction

[ashish514]

I have a large data of families with the information about age, sex, marital status etc. of each member in the columns. The marital status column has somehow been inverted for each family during extraction. Let me explain this with an example. Following is the format of my data

<table width="265" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83"> <col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;width:62pt" width="83" height="17">Family sr. no</td> <td class="xl66" style="border-left:none;width:71pt" width="94" align="right"> Member Sr. no</td> <td class="xl66" style="border-left:none;width:66pt" width="88" align="right"> Marital Status</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">1</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">1</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">1</td> <td class="xl66" style="border-top:none;border-left:none" align="right">3</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">1</td> <td class="xl66" style="border-top:none;border-left:none" align="right">4</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">3</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">3</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">3</td> <td class="xl66" style="border-top:none;border-left:none" align="right">3</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">4</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">4</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">4</td> <td class="xl66" style="border-top:none;border-left:none" align="right">3</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">4</td> <td class="xl66" style="border-top:none;border-left:none" align="right">4</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">4</td> <td class="xl66" style="border-top:none;border-left:none" align="right">5</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> </tr> </tbody></table>

Now, for each family, the marital status codes (1 for married, 2 for unmarried, 3 for divorced, 4 for widow/widower) have been inverted. For example in family no.4, the actual marital status for member no. 1 is entered at member no. 5's place, no. 2's status is at no. 4's place and so one. So the actual sequence of marital status for those 5 members of family should be 11222 and not 22211. Hope I am clear enough

Is there any method which I can use to correct this data?? Otherwise the data is so large that correcting it manually will take about 20 hrs.

 

[shadow12345]

hmm, aside from re running the extration if those values are real then I did it like this.

highlight Col A and B
Sort by
Col A smallest to largest
Col B Largest to smallest

Then highlight Col A and B and C
Sort by
Col A smallest to largest
Col B smallest to largest

This will reverse the data.

Im not sure if this will work for the real data but its worth a try.

 

[John Davis]

I have a large data of families with the information about age, sex, marital status etc. of each member in the columns. The marital status column has somehow been inverted for each family during extraction. Let me explain this with an example. Following is the format of my data

<table width="265" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83"> <col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;width:62pt" width="83" height="17">Family sr. no</td> <td class="xl66" style="border-left:none;width:71pt" width="94" align="right"> Member Sr. no</td> <td class="xl66" style="border-left:none;width:66pt" width="88" align="right"> Marital Status</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">1</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">1</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">1</td> <td class="xl66" style="border-top:none;border-left:none" align="right">3</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">1</td> <td class="xl66" style="border-top:none;border-left:none" align="right">4</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">3</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">3</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">3</td> <td class="xl66" style="border-top:none;border-left:none" align="right">3</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">4</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">4</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">4</td> <td class="xl66" style="border-top:none;border-left:none" align="right">3</td> <td class="xl66" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">4</td> <td class="xl66" style="border-top:none;border-left:none" align="right">4</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">4</td> <td class="xl66" style="border-top:none;border-left:none" align="right">5</td> <td class="xl66" style="border-top:none;border-left:none" align="right">1</td> </tr> </tbody></table>

Now, for each family, the marital status codes (1 for married, 2 for unmarried, 3 for divorced, 4 for widow/widower) have been inverted. For example in family no.4, the actual marital status for member no. 1 is entered at member no. 5's place, no. 2's status is at no. 4's place and so one. So the actual sequence of marital status for those 5 members of family should be 11222 and not 22211. Hope I am clear enough

Is there any method which I can use to correct this data?? Otherwise the data is so large that correcting it manually will take about 20 hrs.

You could cut "Marital Status Column" and insert next to "Family Sr. no Column"

Then highlight both sort "Family Sr. no Column" ascending. "Marital Status Column" descending.

Cut and return "Marital Status" to it Original place.