comparing two fields in an excel spreadsheet
 

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

comparing two fields in an excel spreadsheet
 

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" wrote in message
...
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

comparing two fields in an excel spreadsheet
 

Yes, that is correct.
Thank you
Ajay


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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

comparing two fields in an excel spreadsheet
 

Ajay,

Try the macro below. the assumptions a 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" wrote in message
...
Yes, that is correct.
Thank you
Ajay


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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

comparing two fields in an excel spreadsheet
 

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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Ajay,

Try the macro below. the assumptions a 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" wrote in message
...
Yes, that is correct.
Thank you
Ajay


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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

comparing two fields in an excel spreadsheet
 

Ajay,

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

Bernie

"ajmister" wrote in message
...
Thank sir. That worked. Could you recommend a good book for writing
macros.
Ajay