Align lists to match values

[JugglerJAF]

I've had a search on the forum and cant find anything that does quite what I need.

I have two lists of data (columns A and B). Values can appear in both lists, on list1 but not on list 2, or on list 2 but not on list 1.

I need to move the data so that the values on each row match. Before and after screenshots of soem sample data are as follows:

BEFORE:

Excel Workbook
	A	B
1	List 1	List 2
2	alpha	DELTA
3	bravo	ECHO
4	charlie	FOXTROT
5	golf	GOLF
6	hotel	INDIA
7	*	JULIET
Sheet

AFTER:

Excel Workbook
	A	B
1	List 1	List 2
2	alpha	*
3	bravo	*
4	charlie	*
5	*	DELTA
6	*	ECHO
7	*	FOXTROT
8	golf	GOLF
9	hotel	*
10	*	INDIA
11	*	JULIET
Sheet

I've been trying all day to get my head round a VBA solution to this (the real list has about 20,000 records), but my brain has given up and gone home for the day!

Any suggestions, help or pointers greatly appreciated.

 

[Gary McMaster]

Several possible solutions were offered here:

http://www.mrexcel.com/forum/showthread.php?t=543645

Gary

 

[MickG]

Try this:- Results start "E1".

[COLOR="Navy"]Sub[/COLOR] MG25Aug53
[COLOR="Navy"]Dim[/COLOR] RngA [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oval
[COLOR="Navy"]Dim[/COLOR] RngB [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nCol [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Set[/COLOR] RngA = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]Set[/COLOR] RngB = Range(Range("b2"), Range("b" & Rows.Count).End(xlUp))
        oMax = Application.Max(RngA.Count, RngB.Count)
            [COLOR="Navy"]Set[/COLOR] RngA = Range("A2").Resize(oMax)
                ReDim Ray(1 To RngA.Count * 2, 1 To 2)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] RngA
            [COLOR="Navy"]For[/COLOR] col = 1 To 2
                oval = IIf(col = 1, Dn, Dn.Offset(, 1))
                [COLOR="Navy"]If[/COLOR] oval <> "" [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] Not .Exists(oval) [COLOR="Navy"]Then[/COLOR]
                   c = c + 1
                   .Add oval, Array(c, col)
                    Ray(c, col) = Dn(, col)
                [COLOR="Navy"]Else[/COLOR]
                   Q = .Item(oval)
                   nCol = IIf(Q(1) = 1, 2, 1)
                   Ray(Q(0), nCol) = Dn(, nCol)
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] col
        [COLOR="Navy"]Next[/COLOR] Dn
Range("E1").Resize(.Count, 2) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick