Compare 2 range and rearrange data

smanith · Apr 15, 2019

Hi all,
am new to excel macro VB, I have a requirement , where i have the below data:

Number

Value

Number

Value

Difference

Number	Value	Number	Value
113999	2475802	0	161717.6
115000	161717.6	113902	1611615
119140	21780.24	113999	2475284
119150	274086.8	119140	21780.24
119160	792537.1	119150	274086.8
119180	349912.7	119160	792537.9
119190	308752.5	119180	320793.7
119210	80364	119190	308752.5
119220	298546.5	119210	80364
119250	791109.7	119220	298546.5
119280	704297.7	119250	791109.7
119330	239020	119280	704297.7
180014	306944.2	119330	239020

Here 2 sets are there, i need to compare column1(Number) with column3(Number) and rearrange the whole table with matching Number values. For example i need the result as below:

Number	Value	Number	Value
113999	2475802	113999	2475284
115000	161717.6
119140	21780.24	119140	21780.24
119150	274086.8	119150	274086.8
119160	792537.1	119160	792537.9
119180	349912.7	119180	320793.7
119190	308752.5	119190	308752.5
119210	80364	119210	80364
119220	298546.5	119220	298546.5
119250	791109.7	119250	791109.7
119280	704297.7	119280	704297.7
119330	239020	119330	239020
180014	306944.2
		0	161717.6
		113902	1611615

Could you please help me out with this? Read in the forum that it can be achieved using dictionary. But since am new here, could not proceed with it.

MickG · Apr 15, 2019

Try this:-
NB:- This code will alter your data!!

Code:

[COLOR="Navy"]Sub[/COLOR] MG15Apr13
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Ray = Rng.Offset(, 2).Resize(, 2).Value
MsgBox Ray(1, 2)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray, 1)
        .Item(Ray(n, 1)) = Ray(n, 2)
    [COLOR="Navy"]Next[/COLOR] n
    Rng.Offset(, 2).Resize(, 2).ClearContents

    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, 2) = Dn.Value
            Dn.Offset(, 3).Value = .Item(Dn.Value)
            .Remove (Dn.Value)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]

Range("C" & UBound(Ray, 1) + 2).Resize(.Count, 2) = Application.Transpose(Array(.Keys, .items))
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

smanith · Apr 16, 2019

Hi Mick,
Thanks you for the quick response!
I tried running your code for the below data:

113999	3131202,25	113902	1545898,66
119140	4800	113999	3131202,13
119150	333861,68	119140	4800
119160	536733,24	119150	333861,68
119180	345958,89	119160	536733,24
119190	383234,08	119180	345958,89
119210	77880	119190	383234,08
119220	292658,4	119210	77880
119250	881794,83	119220	292658,4
119280	830957,84	119250	881794,83
119315	42519,1	119280	830957,84
119330	235600	119315	16000
180018	91840	119330	235600

Result i got:

113999	3131202,25	113999	3131202,131
119140	4800	119140	4800
119150	333861,68	119150	333861,68
119160	536733,24	119160	536733,24
119180	345958,89	119180	345958,89
119190	383234,08	119190	383234,08
119210	77880	119210	77880
119220	292658,4	119220	292658,4
119250	881794,83	119250	881794,83
119280	830957,84	113902	1545898,658
119315	42519,1	119315	16000
119330	235600	119330	235600
180018	91840

non matching numbers are in same row -highlighted in red
one of the record from second set is missing in result - highlighted in green

Could you please help?

MickG · Apr 16, 2019

I could not duplicate that , but I have changed the last row variable, see if that solves the problem!!!

Code:

[COLOR="Navy"]Sub[/COLOR] MG16Apr22
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Ray = Rng.Offset(, 2).Resize(, 2).Value
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray, 1)
        .Item(Ray(n, 1)) = Ray(n, 2)
    [COLOR="Navy"]Next[/COLOR] n
    Rng.Offset(, 2).Resize(, 2).ClearContents

    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, 2) = Dn.Value
            Dn.Offset(, 3).Value = .Item(Dn.Value)
            .Remove (Dn.Value)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]

Range("C" & Lst + 1).Resize(.Count, 2) = Application.Transpose(Array(.Keys, .items))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

smanith · Apr 21, 2019

Thanks Mick

It worked brilliantly.

Thanks
Swathi

MickG · Apr 23, 2019

You're welcome

seven1four · Apr 25, 2019

Hi Mick,
How would i change the code if i was comparing between two list that had 2 additional column each. A-C (A is the number) and D-F (D is the number) so that when you rearrange the list the set of column moves too.
Thanks

MickG · Apr 26, 2019

Try this:-

Code:

[COLOR="Navy"]Sub[/COLOR] MG26Apr57
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Ray = Rng.Offset(, 3).Resize(, 3).Value
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray, 1)
        .Item(Ray(n, 1)) = Array(Ray(n, 2), Ray(n, 3))
    [COLOR="Navy"]Next[/COLOR] n
    Rng.Offset(, 3).Resize(, 3).ClearContents

    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, 3) = Dn.Value
            Dn.Offset(, 4).Value = .Item(Dn.Value)(0)
            Dn.Offset(, 5).Value = .Item(Dn.Value)(1)
            .Remove (Dn.Value)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
c = Lst
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    c = c + 1
    Cells(c, "D") = K
    Cells(c, "E") = .Item(K)(0)
    Cells(c, "F") = .Item(K)(1)
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Compare 2 range and rearrange data

smanith

New Member

Excel Facts

MickG

MrExcel MVP

smanith

New Member

MickG

MrExcel MVP

smanith

New Member

MickG

MrExcel MVP

seven1four

New Member

MickG

MrExcel MVP

Similar threads

Forum statistics

Share this page

Compare 2 range and rearrange data

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock