| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Comparing Multiple fields | Excel Discussion (Misc queries) | |||
| Comparing fields in an excel worksheet | Excel Worksheet Functions | |||
| Comparing text fields | Excel Worksheet Functions | |||
| Comparing and potentially adding two fields | Excel Worksheet Functions | |||
| Comparing Date Fields | Excel Worksheet Functions | |||
Linear Mode
