Hi,
I have a access table which contains few rows.
I need to compare each row in the table with other rows in the same sheet.
Logic:
If column "Key" contains different data in 2 rows then check column "Unit", If column "Unit" contains same data then check "Assembly",
If column "Assembly" in one row is empty and column "Assembly" in another row has data then copy the value of column "Effective_Date" (column "Assembly" has value) to column "Effective_Date" of the row which is empty in column "Assembly".
required output:
Thanks in advance.
Vijay
I have a access table which contains few rows.
I need to compare each row in the table with other rows in the same sheet.
Code:
[TABLE]
[FONT=Arial][COLOR=#000000]<CAPTION>[B]test[/B]</CAPTION>[/COLOR][/FONT]<THEAD>[TR]
[TH="bgcolor: #c0c0c0"][FONT=Arial][COLOR=#000000]Key[/COLOR][/FONT][/TH]
[TH="bgcolor: #c0c0c0"][FONT=Arial][COLOR=#000000]Unit[/COLOR][/FONT][/TH]
[TH="bgcolor: #c0c0c0"][FONT=Arial][COLOR=#000000]Assembly[/COLOR][/FONT][/TH]
[TH="bgcolor: #c0c0c0"][FONT=Arial][COLOR=#000000]Effective_Date[/COLOR][/FONT][/TH]
[/TR]
</THEAD><TBODY>[TR]
[TD][FONT=Arial][COLOR=#000000]10[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]6666666H92[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05/02/2012[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]10[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]
[/COLOR][/FONT][/TD]
[TD][COLOR=#ff0000][FONT=Arial]10/02/2012[/FONT][/COLOR][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]20[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]6666666H93[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]15/01/2013[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]30[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]9999999H92[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]20/02/2012[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]30[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]
[/COLOR][/FONT][/TD]
[TD][COLOR=#ff0000][FONT=Arial]25/02/2012[/FONT][/COLOR][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]40[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]9999999H93[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]30/01/2013[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]50[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]7777777H92[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05/02/2012[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]50[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]
[/COLOR][/FONT][/TD]
[TD][COLOR=#ff0000][FONT=Arial]10/02/2012[/FONT][/COLOR][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]60[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]7777777H93[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]15/01/2013[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]70[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]5555555H92[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]20/02/2012[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]70[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]
[/COLOR][/FONT][/TD]
[TD][COLOR=#ff0000][FONT=Arial]25/02/2012[/FONT][/COLOR][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]80[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]5555555H93[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]30/01/2013[/COLOR][/FONT][/TD]
[/TR]
</TBODY><TFOOT></TFOOT>[/TABLE]
If column "Key" contains different data in 2 rows then check column "Unit", If column "Unit" contains same data then check "Assembly",
If column "Assembly" in one row is empty and column "Assembly" in another row has data then copy the value of column "Effective_Date" (column "Assembly" has value) to column "Effective_Date" of the row which is empty in column "Assembly".
required output:
Code:
[TABLE]
[FONT=Arial][COLOR=#000000]<CAPTION>[B]Test_Output[/B]</CAPTION>[/COLOR][/FONT]<THEAD>[TR]
[TH="bgcolor: #c0c0c0"][FONT=Arial][COLOR=#000000]Key[/COLOR][/FONT][/TH]
[TH="bgcolor: #c0c0c0"][FONT=Arial][COLOR=#000000]Unit[/COLOR][/FONT][/TH]
[TH="bgcolor: #c0c0c0"][FONT=Arial][COLOR=#000000]Assembly[/COLOR][/FONT][/TH]
[TH="bgcolor: #c0c0c0"][FONT=Arial][COLOR=#000000]Effective_Date[/COLOR][/FONT][/TH]
[/TR]
</THEAD><TBODY>[TR]
[TD][FONT=Arial][COLOR=#000000]10[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]6666666H92[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05/02/2012[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]10[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]
[/COLOR][/FONT][/TD]
[TD][COLOR=#008000][FONT=Arial]15/01/2013[/FONT][/COLOR][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]20[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]6666666H93[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]15/01/2013[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]30[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]9999999H92[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]20/02/2012[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]30[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]
[/COLOR][/FONT][/TD]
[TD][COLOR=#008000][FONT=Arial]30/01/2013[/FONT][/COLOR][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]40[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]9999999H93[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]30/01/2013[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]50[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]7777777H92[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05/02/2012[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]50[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]
[/COLOR][/FONT][/TD]
[TD][COLOR=#008000][FONT=Arial]15/01/2013[/FONT][/COLOR][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]60[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]7777777H93[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]15/01/2013[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]70[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]5555555H92[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]20/02/2012[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]70[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]
[/COLOR][/FONT][/TD]
[TD][COLOR=#008000][FONT=Arial]30/01/2013[/FONT][/COLOR][/TD]
[/TR]
[TR]
[TD][FONT=Arial][COLOR=#000000]80[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]05100070[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]5555555H93[/COLOR][/FONT][/TD]
[TD][FONT=Arial][COLOR=#000000]30/01/2013[/COLOR][/FONT][/TD]
[/TR]
</TBODY><TFOOT></TFOOT>[/TABLE]
Thanks in advance.
Vijay