merging two data file with same rows in a new columns for new dictionary (macro)

O

osmanoca

Board Regular
Joined
Apr 16, 2016
Messages
87
Hello dear helpfull mrexcel friends..i need help please. very important .thanks

i have two excel file for dictionary. Both are for two languages . both are with columns. word and meaning. but one language of two dictionaries is same. for example i have English-kurdish and kurdis-persian files. i want make make persian english new dictionary with same rows in kurdish. i pasted their columns side to side as: Eng-Kur is column A and B , Kurdish-Persian column is C and D so i want merging or bringing same rows in kurdish (column B-C) in one row.


example
my Eng-Kurdish data as below:

[TABLE="width: 245"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]ENG[/TD]
[TD]KU[/TD]
[/TR]
[TR]
[TD]book[/TD]
[TD]pirtûk[/TD]
[/TR]
[TR]
[TD]table[/TD]
[TD]mase[/TD]
[/TR]
[TR]
[TD]tree[/TD]
[TD]dar[/TD]
[/TR]
</tbody>[/TABLE]

my kurdish-Persian data as below:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]KUR[/TD]
[TD="class: xl65, width: 64"]PERS[/TD]
[/TR]
[TR]
[TD]dar[/TD]
[TD]dirext[/TD]
[/TR]
[TR]
[TD]sar[/TD]
[TD]serd[/TD]
[/TR]
[TR]
[TD]pirtûk[/TD]
[TD]ketab[/TD]
[/TR]
[TR]
[TD]mase[/TD]
[TD]miz[/TD]
[/TR]
[TR]
[TD]zêr[/TD]
[TD]zer[/TD]
[/TR]
[TR]
[TD]mêr[/TD]
[TD]merd[/TD]
[/TR]
</tbody>[/TABLE]


i pasted two file side by side in one file as ABCD columns as below:

[TABLE="width: 373"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]ENG[/TD]
[TD]KU[/TD]
[TD]KU[/TD]
[TD]PERS[/TD]
[/TR]
[TR]
[TD]book[/TD]
[TD]pirtûk[/TD]
[TD]dar[/TD]
[TD]dirext[/TD]
[/TR]
[TR]
[TD]table[/TD]
[TD]mase[/TD]
[TD]sar[/TD]
[TD]serd[/TD]
[/TR]
[TR]
[TD]tree[/TD]
[TD]dar[/TD]
[TD]pirtûk[/TD]
[TD]ketab[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]mase[/TD]
[TD]miz[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]zêr[/TD]
[TD]zer[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]mêr[/TD]
[TD]merd[/TD]
[/TR]
</tbody>[/TABLE]

i want macro code to rearrange and bring only same rows with two columns near to eachother in new columns as below:

[TABLE="width: 373"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]ENG[/TD]
[TD]KU[/TD]
[TD]KU[/TD]
[TD]PERS[/TD]
[/TR]
[TR]
[TD]book[/TD]
[TD]pirtûk[/TD]
[TD]pirtûk[/TD]
[TD]ketab[/TD]
[/TR]
[TR]
[TD]table[/TD]
[TD]mase[/TD]
[TD]mase[/TD]
[TD]miz[/TD]
[/TR]
[TR]
[TD]tree[/TD]
[TD]dar[/TD]
[TD]dar[/TD]
[TD]dirext[/TD]
[/TR]
</tbody>[/TABLE]

then i will delete kurdish columns and make ENG-PERS dictionary

please give me a way to arrive this solution. macro please. very very thanks..
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Hi, osmanoca
Question:
Is it possible that value in col B has no match in col C? Say there is ‘pirtûk’ in col B but not in col C.
Are there any duplicates in any column? Say ‘tree’ & ‘lemon’ is ‘dar’ in Kurdish Sorry, I don’t know the real example to show my point. I modified your sample data, something like this:

Excel 2007 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: Darkslateblue"]
[TH][/TH]
[TH]
A​
[/TH]
[TH]
B​
[/TH]
[TH]
C​
[/TH]
[TH]
D​
[/TH]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
1​
[/TD]
[TD="bgcolor: lightgreen"]ENG[/TD]
[TD="bgcolor: lightgreen"]KU[/TD]
[TD="bgcolor: lightgreen"]KU[/TD]
[TD="bgcolor: lightgreen"]PERS[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
2​
[/TD]
[TD]book[/TD]
[TD]pirtûk[/TD]
[TD]dar[/TD]
[TD]dirext[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
3​
[/TD]
[TD]table[/TD]
[TD]mase[/TD]
[TD]sar[/TD]
[TD]serd[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
4​
[/TD]
[TD]tree[/TD]
[TD]dar[/TD]
[TD]mase[/TD]
[TD]miz[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
5​
[/TD]
[TD]lemon[/TD]
[TD]dar[/TD]
[TD]zêr[/TD]
[TD]zer[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]mêr[/TD]
[TD]merd[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet5
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Thanks for answer. They are two different dictionary database. So columns AB are data of a dict. and CD are another dict data. So of course many words will be different between B and C. But i want only to bring same rows of B and C to same row. Others are different and i dont want them. İf i can bring B and C with their words in same rows in one new row, then i will create new dict from their rows words.
İf B and C is same i want macro to take them with A and D into new row.

Akuini said:
Hi, osmanoca



Question:
Is it possible that value in col B has no match in col C? Say there is ‘pirtûk’ in col B but not in col C.
Are there any duplicates in any column? Say ‘tree’ & ‘lemon’ is ‘dar’ in Kurdish Sorry, I don’t know the real example to show my point. I modified your sample data, something like this:

Excel 2007 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: Darkslateblue"]
[TH][/TH]
[TH]
A​
[/TH]
[TH]
B​
[/TH]
[TH]
C​
[/TH]
[TH]
D​
[/TH]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
1​
[/TD]
[TD="bgcolor: lightgreen"]ENG[/TD]
[TD="bgcolor: lightgreen"]KU[/TD]
[TD="bgcolor: lightgreen"]KU[/TD]
[TD="bgcolor: lightgreen"]PERS[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
2​
[/TD]
[TD]book[/TD]
[TD]pirtûk[/TD]
[TD]dar[/TD]
[TD]dirext[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
3​
[/TD]
[TD]table[/TD]
[TD]mase[/TD]
[TD]sar[/TD]
[TD]serd[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
4​
[/TD]
[TD]tree[/TD]
[TD]dar[/TD]
[TD]mase[/TD]
[TD]miz[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
5​
[/TD]
[TD]lemon[/TD]
[TD]dar[/TD]
[TD]zêr[/TD]
[TD]zer[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]mêr[/TD]
[TD]merd[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet5[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Last edited:
Upvote 0
Ok, that answered my first question, which is ‘yes’.
But what about duplicate? Is there any duplicate?
 
Upvote 0
Only in B or C no dublicated. But may be. İf possible you can give two codes. İf there is dublicated it can merge also.
 
Upvote 0
Ok, try this.
But it doesn’t deal with the duplicates problem yet, but see how it works.
I’m using col E as helper column. The result that you want is the ones with ‘1’ in col E.


Please use Code Tags when posting a code. Like this: [CODE ]Your Code Here[/ CODE]
Code: 
[B][COLOR=Royalblue]Sub[/COLOR][/B] a1018125a[B]()[/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] va[B],[/B] vc[B],[/B] vd
[B][COLOR=Royalblue]Dim[/COLOR][/B] d [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Object[/COLOR][/B]
 
    Range[B]([/B][B][COLOR=brown]"A:B"[/COLOR][/B][B]).[/B]Sort Key1[B]:=[/B]Range[B]([/B][B][COLOR=brown]"B1"[/COLOR][/B][B]),[/B] Order1[B]:=[/B]xlAscending[B],[/B] Header[B]:=[/B]xlYes
    Range[B]([/B][B][COLOR=brown]"C:D"[/COLOR][/B][B]).[/B]Sort Key1[B]:=[/B]Range[B]([/B][B][COLOR=brown]"C1"[/COLOR][/B][B]),[/B] Order1[B]:=[/B]xlAscending[B],[/B] Header[B]:=[/B]xlYes
   
    [B][COLOR=Royalblue]Set[/COLOR][/B] d [B]=[/B] CreateObject[B]([/B][B][COLOR=brown]"scripting.dictionary"[/COLOR][/B][B])[/B]
        d.CompareMode [B]=[/B] vbTextCompare [FONT=trebuchet ms][I][COLOR=Lightseagreen]'vbBinaryCompare[/COLOR][/I][/FONT]
            va [B]=[/B] Range[B]([/B][B][COLOR=brown]"B2"[/COLOR][/B][B],[/B] Cells[B]([/B]Rows.count[B],[/B] [B][COLOR=brown]"B"[/COLOR][/B][B]).[/B][B][COLOR=Royalblue]End[/COLOR][/B][B]([/B]xlUp[B]))[/B]
                vc [B]=[/B] Range[B]([/B][B][COLOR=brown]"C2"[/COLOR][/B][B],[/B] Cells[B]([/B]Rows.count[B],[/B] [B][COLOR=brown]"C"[/COLOR][/B][B]).[/B][B][COLOR=Royalblue]End[/COLOR][/B][B]([/B]xlUp[B]))[/B]
                    [B][COLOR=Royalblue]ReDim[/COLOR][/B] vd[B]([/B][B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]vc[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B]),[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]vc[B],[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B]))[/B]
 
    [B][COLOR=Royalblue]For[/COLOR][/B] i [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]va[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
        d[B]([/B]va[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B]))[/B] [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B]
    [B][COLOR=Royalblue]Next[/COLOR][/B]
   
    [B][COLOR=Royalblue]For[/COLOR][/B] i [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]vc[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
        [B][COLOR=Royalblue]If[/COLOR][/B] d.exists[B]([/B]vc[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B]))[/B] [B][COLOR=Royalblue]Then[/COLOR][/B]
            vd[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B] [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B]
            d[B]([/B]vc[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B]))[/B] [B]=[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B]
        [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
    [B][COLOR=Royalblue]Next[/COLOR][/B]
   
    [B][COLOR=Royalblue]For[/COLOR][/B] i [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]va[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
       [B][COLOR=Royalblue]If[/COLOR][/B] d[B]([/B]va[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B]))[/B] [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]Then[/COLOR][/B] va[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B] [B]=[/B] [B][COLOR=brown]""[/COLOR][/B]
    [B][COLOR=Royalblue]Next[/COLOR][/B]
   
    Range[B]([/B][B][COLOR=brown]"B2"[/COLOR][/B][B]).[/B]Resize[B]([/B]UBound[B]([/B]va[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B]),[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B] [B]=[/B] va
    Range[B]([/B][B][COLOR=brown]"E2"[/COLOR][/B][B]).[/B]Resize[B]([/B]UBound[B]([/B]vd[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B]),[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B] [B]=[/B] vd
  
    Range[B]([/B][B][COLOR=brown]"A:E"[/COLOR][/B][B]).[/B]Sort Key1[B]:=[/B]Range[B]([/B][B][COLOR=brown]"E1"[/COLOR][/B][B]),[/B] Order1[B]:=[/B]xlAscending[B],[/B] Header[B]:=[/B]xlYes
    Range[B]([/B][B][COLOR=brown]"A:B"[/COLOR][/B][B]).[/B]Sort Key1[B]:=[/B]Range[B]([/B][B][COLOR=brown]"B1"[/COLOR][/B][B]),[/B] Order1[B]:=[/B]xlAscending[B],[/B] Header[B]:=[/B]xlYes
 
 
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
 
Upvote 0
Using your sample above, here's the result:


Excel 2007 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]
A​
[/TH]
[TH]
B​
[/TH]
[TH]
C​
[/TH]
[TH]
D​
[/TH]
[TH]
E​
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
1​
[/TD]
[TD="bgcolor: #92D050"]ENG[/TD]
[TD="bgcolor: #92D050"]KU[/TD]
[TD="bgcolor: #92D050"]KU[/TD]
[TD="bgcolor: #92D050"]PERS[/TD]
[TD="bgcolor: #92D050"][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
2​
[/TD]
[TD]tree[/TD]
[TD]dar[/TD]
[TD]dar[/TD]
[TD]dirext[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
3​
[/TD]
[TD]table[/TD]
[TD]mase[/TD]
[TD]mase[/TD]
[TD]miz[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
4​
[/TD]
[TD]book[/TD]
[TD]pirtûk[/TD]
[TD]pirtûk[/TD]
[TD]ketab[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]mêr[/TD]
[TD]merd[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]sar[/TD]
[TD]serd[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]zêr[/TD]
[TD]zer[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet9[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I wrote another macro, I think it is more reliable than the one I gave you above. Try it. It will insert the English words in col E, that’s the result you want.

Please use Code Tags when posting a code. Like this: [CODE ]Your Code Here[/ CODE]
Code: 
[B][COLOR=Royalblue]Sub[/COLOR][/B] a1018125b[B]()[/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] va[B],[/B] vc
[B][COLOR=Royalblue]Dim[/COLOR][/B] d [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Object[/COLOR][/B]
 
   
    [B][COLOR=Royalblue]Set[/COLOR][/B] d [B]=[/B] CreateObject[B]([/B][B][COLOR=brown]"scripting.dictionary"[/COLOR][/B][B])[/B]
        d.CompareMode [B]=[/B] vbTextCompare [FONT=trebuchet ms][I][COLOR=Lightseagreen]'vbBinaryCompare[/COLOR][/I][/FONT]
            va [B]=[/B] Range[B]([/B][B][COLOR=brown]"A2"[/COLOR][/B][B],[/B] Cells[B]([/B]Rows.count[B],[/B] [B][COLOR=brown]"B"[/COLOR][/B][B]).[/B][B][COLOR=Royalblue]End[/COLOR][/B][B]([/B]xlUp[B]))[/B]
                vc [B]=[/B] Range[B]([/B][B][COLOR=brown]"C2:E"[/COLOR][/B] [B]&[/B] Cells[B]([/B]Rows.count[B],[/B] [B][COLOR=brown]"D"[/COLOR][/B][B]).[/B][B][COLOR=Royalblue]End[/COLOR][/B][B]([/B]xlUp[B]).[/B]row[B])[/B]
 
    [B][COLOR=Royalblue]For[/COLOR][/B] i [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]va[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
        d[B]([/B]va[B]([/B]i[B],[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B]))[/B] [B]=[/B] va[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
    [B][COLOR=Royalblue]Next[/COLOR][/B]
   
    [B][COLOR=Royalblue]For[/COLOR][/B] i [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]vc[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
        [B][COLOR=Royalblue]If[/COLOR][/B] d.exists[B]([/B]vc[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B]))[/B] [B][COLOR=Royalblue]Then[/COLOR][/B]
            vc[B]([/B]i[B],[/B] [B][B][COLOR=crimson]3[/COLOR][/B][/B][B])[/B] [B]=[/B] d[B]([/B]vc[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B]))[/B]
        [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
    [B][COLOR=Royalblue]Next[/COLOR][/B]
   
    Range[B]([/B][B][COLOR=brown]"C2"[/COLOR][/B][B]).[/B]Resize[B]([/B]UBound[B]([/B]vc[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B]),[/B] [B][B][COLOR=crimson]3[/COLOR][/B][/B][B])[/B] [B]=[/B] vc
    Range[B]([/B][B][COLOR=brown]"C:E"[/COLOR][/B][B]).[/B]Sort Key1[B]:=[/B]Range[B]([/B][B][COLOR=brown]"E1"[/COLOR][/B][B]),[/B] Order1[B]:=[/B]xlAscending[B],[/B] Header[B]:=[/B]xlYes
    Range[B]([/B][B][COLOR=brown]"A:B"[/COLOR][/B][B]).[/B]Sort Key1[B]:=[/B]Range[B]([/B][B][COLOR=brown]"A1"[/COLOR][/B][B]),[/B] Order1[B]:=[/B]xlAscending[B],[/B] Header[B]:=[/B]xlYes
 
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]

Excel 2007 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: Darkslateblue"]
[TH][/TH]
[TH]
A​
[/TH]
[TH]
B​
[/TH]
[TH]
C​
[/TH]
[TH]
D​
[/TH]
[TH]
E​
[/TH]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
1​
[/TD]
[TD="bgcolor: #B8CCE4"]ENG[/TD]
[TD="bgcolor: #B8CCE4"]KU[/TD]
[TD="bgcolor: #B8CCE4"]KU[/TD]
[TD="bgcolor: #B8CCE4"]PERS[/TD]
[TD="bgcolor: #B8CCE4"][/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
2​
[/TD]
[TD]book[/TD]
[TD]pirtûk[/TD]
[TD]pirtûk[/TD]
[TD]ketab[/TD]
[TD]book[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
3​
[/TD]
[TD]table[/TD]
[TD]mase[/TD]
[TD]mase[/TD]
[TD]miz[/TD]
[TD]table[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
4​
[/TD]
[TD]tree[/TD]
[TD]dar[/TD]
[TD]dar[/TD]
[TD]dirext[/TD]
[TD]tree[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]sar[/TD]
[TD]serd[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]zêr[/TD]
[TD]zer[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]mêr[/TD]
[TD]merd[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet2[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
thnaks Akuini . code worked well. but the problem is in dublicated rows as you said. so before this code i have to merge dublicated in b and c and then run the code. it will be fine so. thanks.

Akuini said:
I wrote another macro, I think it is more reliable than the one I gave you above. Try it. It will insert the English words in col E, that’s the result you want.

Please use Code Tags when posting a code. Like this: [CODE ]Your Code Here[/ CODE]
Code: 
[B][COLOR=Royalblue]Sub[/COLOR][/B] a1018125b[B]()[/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] va[B],[/B] vc
[B][COLOR=Royalblue]Dim[/COLOR][/B] d [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Object[/COLOR][/B]
 
   
    [B][COLOR=Royalblue]Set[/COLOR][/B] d [B]=[/B] CreateObject[B]([/B][B][COLOR=brown]"scripting.dictionary"[/COLOR][/B][B])[/B]
        d.CompareMode [B]=[/B] vbTextCompare [FONT=trebuchet ms][I][COLOR=Lightseagreen]'vbBinaryCompare[/COLOR][/I][/FONT]
            va [B]=[/B] Range[B]([/B][B][COLOR=brown]"A2"[/COLOR][/B][B],[/B] Cells[B]([/B]Rows.count[B],[/B] [B][COLOR=brown]"B"[/COLOR][/B][B]).[/B][B][COLOR=Royalblue]End[/COLOR][/B][B]([/B]xlUp[B]))[/B]
                vc [B]=[/B] Range[B]([/B][B][COLOR=brown]"C2:E"[/COLOR][/B] [B]&[/B] Cells[B]([/B]Rows.count[B],[/B] [B][COLOR=brown]"D"[/COLOR][/B][B]).[/B][B][COLOR=Royalblue]End[/COLOR][/B][B]([/B]xlUp[B]).[/B]row[B])[/B]
 
    [B][COLOR=Royalblue]For[/COLOR][/B] i [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]va[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
        d[B]([/B]va[B]([/B]i[B],[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B]))[/B] [B]=[/B] va[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
    [B][COLOR=Royalblue]Next[/COLOR][/B]
   
    [B][COLOR=Royalblue]For[/COLOR][/B] i [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]vc[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
        [B][COLOR=Royalblue]If[/COLOR][/B] d.exists[B]([/B]vc[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B]))[/B] [B][COLOR=Royalblue]Then[/COLOR][/B]
            vc[B]([/B]i[B],[/B] [B][B][COLOR=crimson]3[/COLOR][/B][/B][B])[/B] [B]=[/B] d[B]([/B]vc[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B]))[/B]
        [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
    [B][COLOR=Royalblue]Next[/COLOR][/B]
   
    Range[B]([/B][B][COLOR=brown]"C2"[/COLOR][/B][B]).[/B]Resize[B]([/B]UBound[B]([/B]vc[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B]),[/B] [B][B][COLOR=crimson]3[/COLOR][/B][/B][B])[/B] [B]=[/B] vc
    Range[B]([/B][B][COLOR=brown]"C:E"[/COLOR][/B][B]).[/B]Sort Key1[B]:=[/B]Range[B]([/B][B][COLOR=brown]"E1"[/COLOR][/B][B]),[/B] Order1[B]:=[/B]xlAscending[B],[/B] Header[B]:=[/B]xlYes
    Range[B]([/B][B][COLOR=brown]"A:B"[/COLOR][/B][B]).[/B]Sort Key1[B]:=[/B]Range[B]([/B][B][COLOR=brown]"A1"[/COLOR][/B][B]),[/B] Order1[B]:=[/B]xlAscending[B],[/B] Header[B]:=[/B]xlYes
 
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]

Excel 2007 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: Darkslateblue"]
[TH][/TH]
[TH]
A​
[/TH]
[TH]
B​
[/TH]
[TH]
C​
[/TH]
[TH]
D​
[/TH]
[TH]
E​
[/TH]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
1​
[/TD]
[TD="bgcolor: #B8CCE4"]ENG[/TD]
[TD="bgcolor: #B8CCE4"]KU[/TD]
[TD="bgcolor: #B8CCE4"]KU[/TD]
[TD="bgcolor: #B8CCE4"]PERS[/TD]
[TD="bgcolor: #B8CCE4"][/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
2​
[/TD]
[TD]book[/TD]
[TD]pirtûk[/TD]
[TD]pirtûk[/TD]
[TD]ketab[/TD]
[TD]book[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
3​
[/TD]
[TD]table[/TD]
[TD]mase[/TD]
[TD]mase[/TD]
[TD]miz[/TD]
[TD]table[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
4​
[/TD]
[TD]tree[/TD]
[TD]dar[/TD]
[TD]dar[/TD]
[TD]dirext[/TD]
[TD]tree[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]sar[/TD]
[TD]serd[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]zêr[/TD]
[TD]zer[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]mêr[/TD]
[TD]merd[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet2[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
deleting same rows on two columns
Replies
7
Views
393
Fluff
Fluff
E
  • Question Question
Remove "," from macro output and concatenate values from two column with "/" only when both the cell have same uniqque key in the row
Replies
0
Views
488
exlearner
E
P
  • Question Question
Macro: Create several rows of data based on existing data in new worksheet
Replies
7
Views
481
MickG
M
L
  • Question Question
Macro or Formula search a merged column for a cell colored yellow and return data from row.
Replies
4
Views
350
Logan668
L
H
  • Question Question
Macro/Formula to Select Rows of data and output to new Worksheets(?)
Replies
1
Views
510
thatmichaelguy
T

Forum statistics

Threads
1,223,956
Messages
6,175,614
Members
452,661
Latest member
Nonhle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top