comparing two fields in an excel spreadsheet

[ajmister]

Hi

I have a spreadsheet which has two fields

Col A Col B
ABC ABC
EFG KLM
KLM MNO
MNO PQR
STU
PQR XYZ


Is there a way in excel to compare the two columns and create an output
like

Col A Col B
ABC ABC
EFG
KLM KLM
MNO MNO
PQR PQR
STU
XYZ XYZ


Thank
Aj

 

[Bernie Deitrick]

Aj,

Just to be clear, you want to create an alphabetized list in column A that
includes all uniques entries from columns A and B, and then move items in
column B to match the values in column A.....

HTH,
Bernie
MS Excel MVP

 

[ajmister]

Yes, that is correct.
Thank you
Ajay

Aj,

Just to be clear, you want to create an alphabetized list in column A that
includes all uniques entries from columns A and B, and then move items in
column B to match the values in column A.....

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Ajay,

Try the macro below. the assumptions are: there aren't any headers, only
your data of interest is in columns A and B, and all your data values are
string constants. If any of those are wrong, the code will need to be
changed slightly.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim myCell As Range
Dim row As Long

For Each myCell In Range("B:B").SpecialCells(xlCellTypeConstants, 2)
If Application.CountIf(Range("A:A"), myCell.Value) = 0 Then
Range("A65536").End(xlUp)(2).Value = myCell.Value
End If
Next myCell

Range("A:A").Sort key1:=Range("A1"), _
order1:=xlAscending, _
header:=xlNo
Range("B:B").Sort key1:=Range("B1"), _
order1:=xlAscending, _
header:=xlNo

For row = 1 To Application.CountA(Range("A:A"))
If Cells(row, 2).Value <> Cells(row, 1).Value Then
Cells(row, 2).Insert Shift:=xlDown
End If
Next row
End Sub

 

[ajmister]

Thank sir. That worked. Could you recommend a good book for writing macros.
Ajay

 

[Bernie Deitrick]

Ajay,

You're welcome. Try any of John Walkenbach's books in his Power
Programming with VBA series.

Bernie