match records on same row

[JugglerJAF]

I have two columns of data (Columns A and B).

Some of the values are common to both lists.
Some values will appear on A but not on B.
Some values will appear on B but not on A.
Both lists should be of unique entries and will be sorted in ascending order.
The lists may be the same size, but will probably have different numbers of values.

I'm looking for a macro that will move the data so that where matches are found, they appear on the same line, and where there is no match, the list which does not contain a match will have a blank.

For example:

Excel Workbook
	A	B
1	List 1	List 2
2	Alpha	Bravo
3	Bravo	Echo
4	Charlie	Foxtrot
5	Delta	Golf
6	Echo	Hotel
7	Golf	India
8	India	Juliet
9	Mike	Kilo
10	Papa	*
11	Romeo	*
Sheet1

would become

Excel Workbook
	H	I
1	List 1	List 2
2	Alpha	*
3	Bravo	Bravo
4	Charlie	*
5	Delta	*
6	Echo	Echo
7	*	Foxtrot
8	Golf	Golf
9	*	Hotel
10	India	India
11	*	Juliet
12	*	Kilo
13	Mike	*
14	Papa	*
15	Romeo	*
Sheet1

NB: Asterisks in the tables above denote blank cells.


I used to have some code that did this, but lost it when I changed jobs. I must have had it saved at my old place of work, but not backed up anywhere!

 

[JackDanIce]

Assuming your data headers are in row 1 and your data starts in row 2, your original data in columns A and B and you wish your adjusted data to be in columns H and I, try:

Sub MySort ()
Range("A:B").copy
Range("H1").pastespecial paste:=xlpastevalues
Dim i as Long
i = 2
Do until IsEmpty(Range("H" & i)) AND IsEmpty(Range("I" & i))
If Range("H" & i) < Range("I" & i) Then
  Range("I" & i).insert Shift:=xlDown
Else
  If Range("H" & i) > Range("I" & i) Then _
    Range("H" & i).insert shift:=xldown
End If
i = i + 1
Loop
End Sub

This hasn't been tested and trying to do this purely from memory/sight so back up your work first before trying!

 

[JugglerJAF]

That works a treat. Thanks.

I've had to add in a step to ensure that each data set contains the same final value as the code as written kept looping if the final value in one list wasn't in the other, but I've just added some code to add the value "ZZZZZZZ" to the end of each set of values in the two lists, apply your code and then delete the "ZZZZZZZ" value.