update a table based on another table

[modiria50989]

[FONT=&quot]Hello,

[/FONT][FONT=&quot]<o></o>[/FONT]
[FONT=&quot]I need a VBA for the following process please. Thanks.[/FONT]
[FONT=&quot]If we have 2 tables in excel. each table has [/FONT][FONT=&quot]diffrent number ofrows, they have 2 columns including car nembers and car names. the macrofirst look at the car number at table 1 then goes through table 2, if finds asame car number, then replace the car name at table 1 with the car name attable 2 corresponding to that car number.[/FONT][FONT=&quot]<o></o>[/FONT]
[FONT=&quot] [/FONT]

 

[modiria50989]

Thank you for the instruction, now it works, but it has two big problem, first of all, the replaced celles are not in their correct position, for example as you see on the picture, infront of 110 i shoud see u67, but i see g6. second problem is empty cells, i want t keep original values if there is no match.


[TABLE="width: 1072"]
<colgroup><col><col><col span="4"><col><col span="3"><col><col span="5"></colgroup><tbody>[TR]
[TD]q number[/TD]
[TD]q real name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]q number[/TD]
[TD]q real name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]q number[/TD]
[TD]ata[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]a1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]48[/TD]
[TD]u65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]48[/TD]
[TD]u65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]b2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]79[/TD]
[TD]a3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]79[/TD]
[TD]u66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]g4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]110[/TD]
[TD]g6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]110[/TD]
[TD]u67[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]y9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]37[/TD]
[TD]y9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]37[/TD]
[TD]y9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]u65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[TD]a1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[TD]a1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]54.2[/TD]
[TD]a2[/TD]
[TD="colspan: 2"]Table1 on sheet 1[/TD]
[TD][/TD]
[TD]89[/TD]
[TD]u67[/TD]
[TD="colspan: 3"]Table2 after macro on sheet 2[/TD]
[TD]89[/TD]
[TD]sara[/TD]
[TD="colspan: 4"]Table2 before macro on sheet 2[/TD]
[/TR]
[TR]
[TD]60.4[/TD]
[TD]b3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3456[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3456[/TD]
[TD]doon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]66.6[/TD]
[TD]g5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]567890[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]567890[/TD]
[TD]mana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]72.8[/TD]
[TD]y10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]678[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]678[/TD]
[TD]ghali[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]79[/TD]
[TD]u66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]990[/TD]
[TD]b2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]990[/TD]
[TD]ghooz[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]85.2[/TD]
[TD]a3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25[/TD]
[TD]b2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]91.4[/TD]
[TD]b4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]345[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]345[/TD]
[TD]manas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]97.6[/TD]
[TD]g6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45[/TD]
[TD]g4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]103.8[/TD]
[TD]y11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7899[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7899[/TD]
[TD]ukryn[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]110[/TD]
[TD]u67[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]54.2[/TD]
[TD]g4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]54.2[/TD]
[TD]a2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45653[/TD]
[TD]samsam[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45653[/TD]
[TD]samsam[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45600[/TD]
[TD]ram[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45600[/TD]
[TD]ram[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]60.4[/TD]
[TD]halim[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]60.4[/TD]
[TD]b3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]65005[/TD]
[TD]ghoor[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]65005[/TD]
[TD]ghoor[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]66.6[/TD]
[TD]baghali[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]66.6[/TD]
[TD]g5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45600[/TD]
[TD]chasb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45600[/TD]
[TD]chasb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]72.8[/TD]
[TD]nasb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]72.8[/TD]
[TD]y10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]415360[/TD]
[TD]damp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]415360[/TD]
[TD]damp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4650005[/TD]
[TD]ata[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4650005[/TD]
[TD]ata[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]51230006[/TD]
[TD]afshin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]51230006[/TD]
[TD]afshin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1320006[/TD]
[TD]soosan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1320006[/TD]
[TD]soosan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]85.2[/TD]
[TD]ali[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]85.2[/TD]
[TD]a3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10274928[/TD]
[TD]baghali[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10274927.9[/TD]
[TD]baghali[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9858873[/TD]
[TD]shahab[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9858873[/TD]
[TD]shahab[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9442818[/TD]
[TD]sara[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9442818.14[/TD]
[TD]sara[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]91.4[/TD]
[TD]doon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]91.4[/TD]
[TD]b4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3424854[/TD]
[TD]mana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3424854.09[/TD]
[TD]mana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8354245[/TD]
[TD]ghali[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8354244.89[/TD]
[TD]ghali[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13283636[/TD]
[TD]ghooz[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13283635.7[/TD]
[TD]ghooz[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]97.6[/TD]
[TD]foorn[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]97.6[/TD]
[TD]g6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4.56E+08[/TD]
[TD]manas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]456350006[/TD]
[TD]manas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4.64E+08[/TD]
[TD]jalas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]463520008[/TD]
[TD]jalas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5.65E+09[/TD]
[TD]ukryn[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5653230006[/TD]
[TD]ukryn[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]103.8[/TD]
[TD]ukryn[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]103.8[/TD]
[TD]y11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[igold]

The way the code is written, the problems you describe do not make sense. The code starts with the first Car Number in Table1, and then looks at the Car Numbers (starting at the top) in Table2. If it finds a match, the code takes the name of the car from column 2 of Table1 and writes it to Column 2 of the matching Car Number in Table2. It then proceeds to look at the second Car Number in Table1 and repeats the process.

In your example if the Car Number in Table1 is 110 then any Car Number 110 in Table 2 will get the name u67. If that did not happen then the only explanation I can think of is that one of the Car Numbers has a space or other non-printing character in the number so they are not matching. If there is no match nothing will happen to any of the Cells in Table2.

It is too bad that your graphic did not come out better.

As a test, you may want to manually input the Car Numbers that did not change (in both tables) and run the code again and see if the correct changes take place.

 

[Macropod]

Cross-posted at: VBA to update a table from another table
Please read Mr Excel's policy on Cross-Posting in rule 13: Forum Rules