Hi Everyone,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
I am trying to create a script that will clean data in the table using a specific way.
<o></o>
Here is the file with the original data and the results:<o></o>
http://www.box.com/s/64907aed4d2371cc33e5
<o></o>
Basically script should go through the Code 1 column comparing codes, if the code matches then it looks into Code 2 column and do some changes, if required, to Code 2 column only. Changes are based on some rules.<o></o>
Rule 1, if values are alike in Code 2 column (matching 4 letters or more), and all lines have the same date then overwrite all values with the longest value.<o></o>
Rule 2, if values are alike in Code 2 column (matching 4 letters or more), But lines have different date then overwrite all values with the longest value of the oldest date.<o></o>
Hope it explains the problem, and if not, please, ask me questions.<o></o>
I do not even know how to start? Sorting data is okay, so I would first sort data by Code 1 column then, read each unique value in the Code 1 column into temporary array. And then what? <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 12pt; HEIGHT: 15.75pt; VISIBILITY: visible; mso-wrap-style: square" id=Picture_x0020_1 alt="0" type="#_x0000_t75" o:spid="_x0000_i1025"><v:imagedata o:title="0" src="file:///C:\Users\dpavlov\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape>
<o></o>
Please, give ANY suggestions. <o></o>
<o> </o>
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o> I am trying to create a script that will clean data in the table using a specific way.
<o
></o> Here is the file with the original data and the results:<o
></o>http://www.box.com/s/64907aed4d2371cc33e5
<o
></o> Basically script should go through the Code 1 column comparing codes, if the code matches then it looks into Code 2 column and do some changes, if required, to Code 2 column only. Changes are based on some rules.<o
></o>Rule 1, if values are alike in Code 2 column (matching 4 letters or more), and all lines have the same date then overwrite all values with the longest value.<o
></o>Rule 2, if values are alike in Code 2 column (matching 4 letters or more), But lines have different date then overwrite all values with the longest value of the oldest date.<o
></o>Hope it explains the problem, and if not, please, ask me questions.<o
></o>I do not even know how to start? Sorting data is okay, so I would first sort data by Code 1 column then, read each unique value in the Code 1 column into temporary array. And then what? <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o
referrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 12pt; HEIGHT: 15.75pt; VISIBILITY: visible; mso-wrap-style: square" id=Picture_x0020_1 alt="0" type="#_x0000_t75" o:spid="_x0000_i1025"><v:imagedata o:title="0" src="file:///C:\Users\dpavlov\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><o
></o> Please, give ANY suggestions. <o
></o><o
> </o>
