VBA to align data based on two columns

AshaG · Jan 4th 2019

I'm a VBA newbie trying to teach myself as I go, but I think what I'm trying to do is out of my league at the moment.

I have two lists in the same worksheet which both contain an ID number column. I need to align the data in each of the lists based on the ID number columns. The two ID columns can be of unequal length and ID numbers can be missing from either column.

Below are examples of randomly generated data in the format of my lists.

Any help would be hugely appreciated!!

Current:
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 657"]

[tr]

[TD="width: 97, bgcolor: transparent"]List 1[/TD]
[TD="width: 97, bgcolor: transparent"] [/TD]
[TD="width: 97, bgcolor: transparent"] [/TD]
[TD="width: 97, bgcolor: transparent"]List 2[/TD]
[TD="width: 97, bgcolor: transparent"] [/TD]
[TD="width: 97, bgcolor: transparent"] [/TD]
[TD="width: 97, bgcolor: transparent"] [/TD]
[TD="width: 97, bgcolor: transparent"] [/TD]
[TD="width: 97, bgcolor: transparent"] [/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]ID[/TD]
[TD="bgcolor: transparent"]ID-2[/TD]
[TD="bgcolor: transparent"]Entered[/TD]
[TD="bgcolor: transparent"]ID[/TD]
[TD="bgcolor: transparent"]Last Name[/TD]
[TD="bgcolor: transparent"]First Name[/TD]
[TD="bgcolor: transparent"]Number[/TD]
[TD="bgcolor: transparent"]DOB[/TD]
[TD="bgcolor: transparent"]Status[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000016/123[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]1/01/2019[/TD]
[TD="bgcolor: transparent"]000016/123[/TD]
[TD="bgcolor: transparent"]Loken[/TD]
[TD="bgcolor: transparent"]Chantel[/TD]
[TD="bgcolor: transparent, align: right"]866435[/TD]
[TD="bgcolor: transparent, align: right"]26/09/1951[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000017/123[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2/01/2019[/TD]
[TD="bgcolor: transparent"]000017/123[/TD]
[TD="bgcolor: transparent"]Canales[/TD]
[TD="bgcolor: transparent"]Joseph[/TD]
[TD="bgcolor: transparent, align: right"]517745[/TD]
[TD="bgcolor: transparent, align: right"]26/11/1981[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000018/123[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]3/01/2019[/TD]
[TD="bgcolor: transparent"]000018/123[/TD]
[TD="bgcolor: transparent"]Kirsh[/TD]
[TD="bgcolor: transparent"]Harry[/TD]
[TD="bgcolor: transparent, align: right"]648204[/TD]
[TD="bgcolor: transparent, align: right"]18/11/1981[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000020/123[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]4/01/2019[/TD]
[TD="bgcolor: transparent"]000020/123[/TD]
[TD="bgcolor: transparent"]Mager[/TD]
[TD="bgcolor: transparent"]Jenelle[/TD]
[TD="bgcolor: transparent, align: right"]868095[/TD]
[TD="bgcolor: transparent, align: right"]11/11/1952[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000021/123[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]5/01/2019[/TD]
[TD="bgcolor: transparent"]000021/123[/TD]
[TD="bgcolor: transparent"]Brant[/TD]
[TD="bgcolor: transparent"]Trevor[/TD]
[TD="bgcolor: transparent, align: right"]571714[/TD]
[TD="bgcolor: transparent, align: right"]23/06/1969[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000022/123[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]6/01/2019[/TD]
[TD="bgcolor: transparent"]000022/123[/TD]
[TD="bgcolor: transparent"]Yokoyama[/TD]
[TD="bgcolor: transparent"]Magali[/TD]
[TD="bgcolor: transparent, align: right"]618851[/TD]
[TD="bgcolor: transparent, align: right"]29/05/1951[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000023/123[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]7/01/2019[/TD]
[TD="bgcolor: transparent"]000023/123[/TD]
[TD="bgcolor: transparent"]Ziolkowski[/TD]
[TD="bgcolor: transparent"]Drew[/TD]
[TD="bgcolor: transparent, align: right"]721814[/TD]
[TD="bgcolor: transparent, align: right"]27/07/1955[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000026/123[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]9/01/2019[/TD]
[TD="bgcolor: transparent"]000025/123[/TD]
[TD="bgcolor: transparent"]Santistevan[/TD]
[TD="bgcolor: transparent"]Chas[/TD]
[TD="bgcolor: transparent, align: right"]521488[/TD]
[TD="bgcolor: transparent, align: right"]10/06/1978[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000027/123[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]10/01/2019[/TD]
[TD="bgcolor: transparent"]000026/123[/TD]
[TD="bgcolor: transparent"]Stocks[/TD]
[TD="bgcolor: transparent"]Tiffani[/TD]
[TD="bgcolor: transparent, align: right"]556527[/TD]
[TD="bgcolor: transparent, align: right"]29/08/1986[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000031/123[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]11/01/2019[/TD]
[TD="bgcolor: transparent"]000027/123[/TD]
[TD="bgcolor: transparent"]Mike[/TD]
[TD="bgcolor: transparent"]Elisabeth[/TD]
[TD="bgcolor: transparent, align: right"]572863[/TD]
[TD="bgcolor: transparent, align: right"]12/05/1970[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000032/123[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]12/01/2019[/TD]
[TD="bgcolor: transparent"]000031/123[/TD]
[TD="bgcolor: transparent"]Ballas[/TD]
[TD="bgcolor: transparent"]Derick[/TD]
[TD="bgcolor: transparent, align: right"]646430[/TD]
[TD="bgcolor: transparent, align: right"]20/03/1976[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000033/123[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]13/01/2019[/TD]
[TD="bgcolor: transparent"]000032/123[/TD]
[TD="bgcolor: transparent"]Roda[/TD]
[TD="bgcolor: transparent"]Corinna[/TD]
[TD="bgcolor: transparent, align: right"]776913[/TD]
[TD="bgcolor: transparent, align: right"]15/05/1973[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000036/123[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]14/01/2019[/TD]
[TD="bgcolor: transparent"]000033/123[/TD]
[TD="bgcolor: transparent"]Tankersley[/TD]
[TD="bgcolor: transparent"]Barry[/TD]
[TD="bgcolor: transparent, align: right"]876990[/TD]
[TD="bgcolor: transparent, align: right"]19/03/1965[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000038/123[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]15/01/2019[/TD]
[TD="bgcolor: transparent"]000036/123[/TD]
[TD="bgcolor: transparent"]Krieg[/TD]
[TD="bgcolor: transparent"]Rosio[/TD]
[TD="bgcolor: transparent, align: right"]777615[/TD]
[TD="bgcolor: transparent, align: right"]28/03/1981[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000042/123[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]16/01/2019[/TD]
[TD="bgcolor: transparent"]000044/123[/TD]
[TD="bgcolor: transparent"]Tinnin[/TD]
[TD="bgcolor: transparent"]Alison[/TD]
[TD="bgcolor: transparent, align: right"]658177[/TD]
[TD="bgcolor: transparent, align: right"]10/08/1951[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000044/123[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]17/01/2019[/TD]
[TD="bgcolor: transparent"]000046/123[/TD]
[TD="bgcolor: transparent"]Kettner[/TD]
[TD="bgcolor: transparent"]Estelle[/TD]
[TD="bgcolor: transparent, align: right"]740734[/TD]
[TD="bgcolor: transparent, align: right"]22/11/1952[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000046/123[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]18/01/2019[/TD]
[TD="bgcolor: transparent"]000051/123[/TD]
[TD="bgcolor: transparent"]Rachal[/TD]
[TD="bgcolor: transparent"]Jerri[/TD]
[TD="bgcolor: transparent, align: right"]753559[/TD]
[TD="bgcolor: transparent, align: right"]11/01/1954[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000051/123[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]19/01/2019[/TD]
[TD="bgcolor: transparent"]000061/123[/TD]
[TD="bgcolor: transparent"]Sack[/TD]
[TD="bgcolor: transparent"]Sergio[/TD]
[TD="bgcolor: transparent, align: right"]542856[/TD]
[TD="bgcolor: transparent, align: right"]29/07/1950[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000061/123[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]20/01/2019[/TD]
[TD="bgcolor: transparent"]000071/123[/TD]
[TD="bgcolor: transparent"]Peres[/TD]
[TD="bgcolor: transparent"]Cherish[/TD]
[TD="bgcolor: transparent, align: right"]883077[/TD]
[TD="bgcolor: transparent, align: right"]19/02/1988[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000071/123[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]21/01/2019[/TD]
[TD="bgcolor: transparent"]000072/123[/TD]
[TD="bgcolor: transparent"]Noriega[/TD]
[TD="bgcolor: transparent"]Tobi[/TD]
[TD="bgcolor: transparent, align: right"]831440[/TD]
[TD="bgcolor: transparent, align: right"]5/01/1980[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000072/123[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]22/01/2019[/TD]
[TD="bgcolor: transparent"]000074/123[/TD]
[TD="bgcolor: transparent"]Coachman[/TD]
[TD="bgcolor: transparent"]Mirna[/TD]
[TD="bgcolor: transparent, align: right"]656239[/TD]
[TD="bgcolor: transparent, align: right"]9/04/1951[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000074/123[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]23/01/2019[/TD]
[TD="bgcolor: transparent"]000075/123[/TD]
[TD="bgcolor: transparent"]Brune[/TD]
[TD="bgcolor: transparent"]Thaddeus[/TD]
[TD="bgcolor: transparent, align: right"]791366[/TD]
[TD="bgcolor: transparent, align: right"]19/06/1996[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000075/123[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]24/01/2019[/TD]
[TD="bgcolor: transparent"]000076/123[/TD]
[TD="bgcolor: transparent"]Casterline[/TD]
[TD="bgcolor: transparent"]Lissette[/TD]
[TD="bgcolor: transparent, align: right"]716882[/TD]
[TD="bgcolor: transparent, align: right"]27/12/1987[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000076/123[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]25/01/2019[/TD]
[TD="bgcolor: transparent"]000078/123[/TD]
[TD="bgcolor: transparent"]Pew[/TD]
[TD="bgcolor: transparent"]Vernie[/TD]
[TD="bgcolor: transparent, align: right"]610133[/TD]
[TD="bgcolor: transparent, align: right"]3/07/1969[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000077/123[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]26/01/2019[/TD]
[TD="bgcolor: transparent"]000079/123[/TD]
[TD="bgcolor: transparent"]Clauss[/TD]
[TD="bgcolor: transparent"]Hwa[/TD]
[TD="bgcolor: transparent, align: right"]612457[/TD]
[TD="bgcolor: transparent, align: right"]5/10/1968[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000078/123[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]27/01/2019[/TD]
[TD="bgcolor: transparent"]000080/123[/TD]
[TD="bgcolor: transparent"]Kinney[/TD]
[TD="bgcolor: transparent"]Ardis[/TD]
[TD="bgcolor: transparent, align: right"]831361[/TD]
[TD="bgcolor: transparent, align: right"]20/01/1997[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]000081/123[/TD]
[TD="bgcolor: transparent"]Weingartner[/TD]
[TD="bgcolor: transparent"]Karolyn[/TD]
[TD="bgcolor: transparent, align: right"]884128[/TD]
[TD="bgcolor: transparent, align: right"]3/04/1983[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]000086/123[/TD]
[TD="bgcolor: transparent"]Aikin[/TD]
[TD="bgcolor: transparent"]Vida[/TD]
[TD="bgcolor: transparent, align: right"]533550[/TD]
[TD="bgcolor: transparent, align: right"]13/05/1982[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]000088/123[/TD]
[TD="bgcolor: transparent"]Borror[/TD]
[TD="bgcolor: transparent"]Christa[/TD]
[TD="bgcolor: transparent, align: right"]693423[/TD]
[TD="bgcolor: transparent, align: right"]29/04/1963[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]000089/123[/TD]
[TD="bgcolor: transparent"]Gondek[/TD]
[TD="bgcolor: transparent"]Aundrea[/TD]
[TD="bgcolor: transparent, align: right"]525383[/TD]
[TD="bgcolor: transparent, align: right"]17/01/1983[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[/TABLE]

Desired:
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 657"]

[tr]

[TD="width: 97, bgcolor: transparent"]List 1[/TD]
[TD="width: 97, bgcolor: transparent"] [/TD]
[TD="width: 97, bgcolor: transparent"] [/TD]
[TD="width: 97, bgcolor: transparent"]List 2[/TD]
[TD="width: 97, bgcolor: transparent"] [/TD]
[TD="width: 97, bgcolor: transparent"] [/TD]
[TD="width: 97, bgcolor: transparent"] [/TD]
[TD="width: 97, bgcolor: transparent"] [/TD]
[TD="width: 97, bgcolor: transparent"] [/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]ID[/TD]
[TD="bgcolor: transparent"]ID-2[/TD]
[TD="bgcolor: transparent"]Entered[/TD]
[TD="bgcolor: transparent"]ID[/TD]
[TD="bgcolor: transparent"]Last Name[/TD]
[TD="bgcolor: transparent"]First Name[/TD]
[TD="bgcolor: transparent"]Number[/TD]
[TD="bgcolor: transparent"]DOB[/TD]
[TD="bgcolor: transparent"]Status[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000016/123[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]1/01/2019[/TD]
[TD="bgcolor: transparent"]000016/123[/TD]
[TD="bgcolor: transparent"]Loken[/TD]
[TD="bgcolor: transparent"]Chantel[/TD]
[TD="bgcolor: transparent, align: right"]568457[/TD]
[TD="bgcolor: transparent, align: right"]27/05/1990[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000017/123[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]2/01/2019[/TD]
[TD="bgcolor: transparent"]000017/123[/TD]
[TD="bgcolor: transparent"]Canales[/TD]
[TD="bgcolor: transparent"]Joseph[/TD]
[TD="bgcolor: transparent, align: right"]580347[/TD]
[TD="bgcolor: transparent, align: right"]9/06/1972[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000018/123[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]3/01/2019[/TD]
[TD="bgcolor: transparent"]000018/123[/TD]
[TD="bgcolor: transparent"]Kirsh[/TD]
[TD="bgcolor: transparent"]Harry[/TD]
[TD="bgcolor: transparent, align: right"]684099[/TD]
[TD="bgcolor: transparent, align: right"]26/07/1984[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000020/123[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]4/01/2019[/TD]
[TD="bgcolor: transparent"]000020/123[/TD]
[TD="bgcolor: transparent"]Mager[/TD]
[TD="bgcolor: transparent"]Jenelle[/TD]
[TD="bgcolor: transparent, align: right"]652838[/TD]
[TD="bgcolor: transparent, align: right"]27/02/1987[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000021/123[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5/01/2019[/TD]
[TD="bgcolor: transparent"]000021/123[/TD]
[TD="bgcolor: transparent"]Brant[/TD]
[TD="bgcolor: transparent"]Trevor[/TD]
[TD="bgcolor: transparent, align: right"]744940[/TD]
[TD="bgcolor: transparent, align: right"]11/05/1965[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000022/123[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]6/01/2019[/TD]
[TD="bgcolor: transparent"]000022/123[/TD]
[TD="bgcolor: transparent"]Yokoyama[/TD]
[TD="bgcolor: transparent"]Magali[/TD]
[TD="bgcolor: transparent, align: right"]620725[/TD]
[TD="bgcolor: transparent, align: right"]6/07/1995[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000023/123[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]7/01/2019[/TD]
[TD="bgcolor: transparent"]000023/123[/TD]
[TD="bgcolor: transparent"]Ziolkowski[/TD]
[TD="bgcolor: transparent"]Drew[/TD]
[TD="bgcolor: transparent, align: right"]625145[/TD]
[TD="bgcolor: transparent, align: right"]28/07/1962[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]000025/123[/TD]
[TD="bgcolor: transparent"]Santistevan[/TD]
[TD="bgcolor: transparent"]Chas[/TD]
[TD="bgcolor: transparent, align: right"]644502[/TD]
[TD="bgcolor: transparent, align: right"]6/09/1967[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000026/123[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]9/01/2019[/TD]
[TD="bgcolor: transparent"]000026/123[/TD]
[TD="bgcolor: transparent"]Stocks[/TD]
[TD="bgcolor: transparent"]Tiffani[/TD]
[TD="bgcolor: transparent, align: right"]511634[/TD]
[TD="bgcolor: transparent, align: right"]9/02/1958[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000027/123[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]10/01/2019[/TD]
[TD="bgcolor: transparent"]000027/123[/TD]
[TD="bgcolor: transparent"]Mike[/TD]
[TD="bgcolor: transparent"]Elisabeth[/TD]
[TD="bgcolor: transparent, align: right"]637366[/TD]
[TD="bgcolor: transparent, align: right"]26/10/1995[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000031/123[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]11/01/2019[/TD]
[TD="bgcolor: transparent"]000031/123[/TD]
[TD="bgcolor: transparent"]Ballas[/TD]
[TD="bgcolor: transparent"]Derick[/TD]
[TD="bgcolor: transparent, align: right"]554837[/TD]
[TD="bgcolor: transparent, align: right"]29/11/1997[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000032/123[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]12/01/2019[/TD]
[TD="bgcolor: transparent"]000032/123[/TD]
[TD="bgcolor: transparent"]Roda[/TD]
[TD="bgcolor: transparent"]Corinna[/TD]
[TD="bgcolor: transparent, align: right"]608320[/TD]
[TD="bgcolor: transparent, align: right"]17/11/1951[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000033/123[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]13/01/2019[/TD]
[TD="bgcolor: transparent"]000033/123[/TD]
[TD="bgcolor: transparent"]Tankersley[/TD]
[TD="bgcolor: transparent"]Barry[/TD]
[TD="bgcolor: transparent, align: right"]656345[/TD]
[TD="bgcolor: transparent, align: right"]18/10/1992[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000036/123[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]14/01/2019[/TD]
[TD="bgcolor: transparent"]000036/123[/TD]
[TD="bgcolor: transparent"]Krieg[/TD]
[TD="bgcolor: transparent"]Rosio[/TD]
[TD="bgcolor: transparent, align: right"]717440[/TD]
[TD="bgcolor: transparent, align: right"]14/06/1950[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000038/123[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]15/01/2019[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000042/123[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]16/01/2019[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000044/123[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]17/01/2019[/TD]
[TD="bgcolor: transparent"]000044/123[/TD]
[TD="bgcolor: transparent"]Tinnin[/TD]
[TD="bgcolor: transparent"]Alison[/TD]
[TD="bgcolor: transparent, align: right"]715387[/TD]
[TD="bgcolor: transparent, align: right"]12/09/1973[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000046/123[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]18/01/2019[/TD]
[TD="bgcolor: transparent"]000046/123[/TD]
[TD="bgcolor: transparent"]Kettner[/TD]
[TD="bgcolor: transparent"]Estelle[/TD]
[TD="bgcolor: transparent, align: right"]615133[/TD]
[TD="bgcolor: transparent, align: right"]5/09/1956[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000051/123[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]19/01/2019[/TD]
[TD="bgcolor: transparent"]000051/123[/TD]
[TD="bgcolor: transparent"]Rachal[/TD]
[TD="bgcolor: transparent"]Jerri[/TD]
[TD="bgcolor: transparent, align: right"]705142[/TD]
[TD="bgcolor: transparent, align: right"]28/07/1952[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000061/123[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]20/01/2019[/TD]
[TD="bgcolor: transparent"]000061/123[/TD]
[TD="bgcolor: transparent"]Sack[/TD]
[TD="bgcolor: transparent"]Sergio[/TD]
[TD="bgcolor: transparent, align: right"]751003[/TD]
[TD="bgcolor: transparent, align: right"]1/05/1958[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000071/123[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]21/01/2019[/TD]
[TD="bgcolor: transparent"]000071/123[/TD]
[TD="bgcolor: transparent"]Peres[/TD]
[TD="bgcolor: transparent"]Cherish[/TD]
[TD="bgcolor: transparent, align: right"]555632[/TD]
[TD="bgcolor: transparent, align: right"]14/11/1993[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000072/123[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]22/01/2019[/TD]
[TD="bgcolor: transparent"]000072/123[/TD]
[TD="bgcolor: transparent"]Noriega[/TD]
[TD="bgcolor: transparent"]Tobi[/TD]
[TD="bgcolor: transparent, align: right"]538908[/TD]
[TD="bgcolor: transparent, align: right"]5/07/1998[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000074/123[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]23/01/2019[/TD]
[TD="bgcolor: transparent"]000074/123[/TD]
[TD="bgcolor: transparent"]Coachman[/TD]
[TD="bgcolor: transparent"]Mirna[/TD]
[TD="bgcolor: transparent, align: right"]832721[/TD]
[TD="bgcolor: transparent, align: right"]13/06/1964[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000075/123[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]24/01/2019[/TD]
[TD="bgcolor: transparent"]000075/123[/TD]
[TD="bgcolor: transparent"]Brune[/TD]
[TD="bgcolor: transparent"]Thaddeus[/TD]
[TD="bgcolor: transparent, align: right"]838362[/TD]
[TD="bgcolor: transparent, align: right"]15/06/1968[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000076/123[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]25/01/2019[/TD]
[TD="bgcolor: transparent"]000076/123[/TD]
[TD="bgcolor: transparent"]Casterline[/TD]
[TD="bgcolor: transparent"]Lissette[/TD]
[TD="bgcolor: transparent, align: right"]810291[/TD]
[TD="bgcolor: transparent, align: right"]6/03/1970[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000077/123[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]26/01/2019[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]000078/123[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]27/01/2019[/TD]
[TD="bgcolor: transparent"]000078/123[/TD]
[TD="bgcolor: transparent"]Pew[/TD]
[TD="bgcolor: transparent"]Vernie[/TD]
[TD="bgcolor: transparent, align: right"]747954[/TD]
[TD="bgcolor: transparent, align: right"]23/02/1950[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]000079/123[/TD]
[TD="bgcolor: transparent"]Clauss[/TD]
[TD="bgcolor: transparent"]Hwa[/TD]
[TD="bgcolor: transparent, align: right"]898783[/TD]
[TD="bgcolor: transparent, align: right"]30/06/1985[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]000080/123[/TD]
[TD="bgcolor: transparent"]Kinney[/TD]
[TD="bgcolor: transparent"]Ardis[/TD]
[TD="bgcolor: transparent, align: right"]635071[/TD]
[TD="bgcolor: transparent, align: right"]11/09/1995[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]000081/123[/TD]
[TD="bgcolor: transparent"]Weingartner[/TD]
[TD="bgcolor: transparent"]Karolyn[/TD]
[TD="bgcolor: transparent, align: right"]655366[/TD]
[TD="bgcolor: transparent, align: right"]10/04/1988[/TD]
[TD="bgcolor: transparent"]Outstanding[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]000086/123[/TD]
[TD="bgcolor: transparent"]Aikin[/TD]
[TD="bgcolor: transparent"]Vida[/TD]
[TD="bgcolor: transparent, align: right"]768820[/TD]
[TD="bgcolor: transparent, align: right"]20/10/1993[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]000088/123[/TD]
[TD="bgcolor: transparent"]Borror[/TD]
[TD="bgcolor: transparent"]Christa[/TD]
[TD="bgcolor: transparent, align: right"]542919[/TD]
[TD="bgcolor: transparent, align: right"]12/09/1961[/TD]
[TD="bgcolor: transparent"]Over Due[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]000089/123[/TD]
[TD="bgcolor: transparent"]Gondek[/TD]
[TD="bgcolor: transparent"]Aundrea[/TD]
[TD="bgcolor: transparent, align: right"]836219[/TD]
[TD="bgcolor: transparent, align: right"]1/04/1990[/TD]
[TD="bgcolor: transparent"]Complete[/TD]

[/tr]

[/TABLE]

Marius44 · Jan 5th 2019

Hello AshaG
Excuse me, first of all, my English (I'm Italian)
If I understood the question correctly, this is the macro that, I suppose, solves the problem.
One thing I did not understand is how it does (with which criteria) the value of column ID-2 changes.

Code

Sub prova()
ur1 = Cells(Rows.Count, 1).End(xlUp).Row
ur2 = Cells(Rows.Count, 4).End(xlUp).Row
ReDim listcod(3 To ur1 + ur2)
For i = 3 To ur1
  listcod(i) = Cells(i, 1)
Next i
For i = 3 To ur2
  listcod(i + ur1 - 2) = Cells(i, 4)
Next i
For i = 3 To UBound(listcod)
  Cells(i, 11) = listcod(i)
Next i
ActiveSheet.Range("$K$3:$K$" & UBound(listcod)).RemoveDuplicates Columns:=1, Header:=xlNo
ActiveSheet.Range("$K$3:$K$" & UBound(listcod)).Sort Key1:=Range("$K$3"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ur3 = Cells(Rows.Count, 11).End(xlUp).Row
ReDim listcod(3 To ur3)
a = 2
For i = 3 To UBound(listcod)
  If Cells(i, 11) <> "" Then
    listcod(i) = Cells(i, 11).Value
  End If
Next i
ActiveSheet.Range("$K$3:$K$" & UBound(listcod)).ClearContents
ReDim List1(3 To ur1, 1 To 3)
ReDim List2(3 To ur2, 1 To 6)
For i = 3 To ur1
  For j = 1 To 3
    List1(i, j) = Cells(i, j).Value
  Next j
Next i
For i = 3 To ur2
  For j = 1 To 6
    List2(i, j) = Cells(i, j + 3).Value
  Next j
Next i
For i = 3 To UBound(listcod)
  For j = 3 To ur1
    If listcod(i) = List1(j, 1) Then
      For k = 1 To 3
        Cells(i, k + 10) = List1(j, k)
      Next k
      Exit For
    Else
    End If
  Next j
  For j = 3 To ur2
    If listcod(i) = List2(j, 1) Then
      For k = 1 To 6
        Cells(i, k + 13) = List2(j, k)
      Next k
      Exit For
    Else
    End If
  Next j
Next i
End Sub

Display More

Let me know.
Hi, Mario

AshaG · Jan 10th 2019

That code worked perfectly!! Thank you so much.

Just to answer your question, the data in ID-2 come the ID in the first list. My data starts as two separate lists which is manipulated by my other macros. The ID data which populates the first list starts in a different format to the ID in the second list. My other macro splits and recombined it to the xxxxxx/yyy format to be able to compare the lists, but the removed information ID-2 still needs to be recorded.

Thank you again so much for your help!

Marius44 · Jan 10th 2019

Thanks for the welcome feedback.
I'm counting for you.
Hi,
Mario

**KjBox** · Jan 11th 2019

It would help greatly if you attached your workbook.

AshaG · Jan 11th 2019

Thanks, KjBox.

I'm very new to all this and was unsure how to attach the workbook at the time. I have since worked it out and will do that next time.

VBA to align data based on two columns

Participate now!

Share