comparing two fields in an excel spreadsheet

A

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
 
B

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
 
A

ajmister

Yes, that is correct.
Thank you
Ajay


Bernie Deitrick said:
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
 
B

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
 
B

Bernie Deitrick

Ajay,

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

Bernie
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top