Compare a range of cells

[FrodeOlsen]

How can i compare a range of cells an have them "reorganized" so that
the one that correspond is listed on the same Row?

AAA-AAA
BBB-CCC
CCC-DDD
EEE-EEE

become:
AAA-AAA
BBB-
CCC-CCC
-DDD
EEE-EEE

 

[Dave Peterson]

Two columns?

Add a header to row 1 and try this macro that I've saved this from a few
previous posts:

Option Explicit
Sub testme()

Application.ScreenUpdating = False

Dim wks As Worksheet
Dim ColA As Range
Dim ColB As Range
Dim iRow As Long
Dim myCols As Long

Set wks = Worksheets("sheet1")
wks.DisplayPageBreaks = False
With wks
'row 1 has headers!
Set ColA = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set ColB = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))

With ColA
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

'change the mycols to the number of columns that
'are associated with column B

myCols = 1 ' columns B only
With ColB.Resize(, myCols)
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

iRow = 2
Do
If Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 0 Then
Exit Do
End If

If .Cells(iRow, "A").Value = .Cells(iRow, "B").Value _
Or Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 1 Then
'do nothing
Else
If .Cells(iRow, "A").Value > .Cells(iRow, "B").Value Then
.Cells(iRow, "A").Insert shift:=xlDown
Else
.Cells(iRow, "B").Resize(1, myCols).Insert shift:=xlDown
End If
End If
iRow = iRow + 1
Loop
End With

Application.ScreenUpdating = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Herbert Seidenberg]

Here is a way to sort and compare two lists without VBA or formulas.
Assume your 2 lists look like this:

List1 List2
AL3 AM2
AM1 AK1
AK1 AM3
AK2 AK4
AL1 AL1
AM3 AK2
AL2 AK3

Copy List2 and place it under List1
Copy List1 and place it under List2
Give the copied lists a red background.
A * stands for red in this post.

List1 List2
AL3 AM2
AM1 AK1
AK1 AM3
AK2 AK4
AL1 AL1
AM3 AK2
AL2 AK3
AM2* AL3*
AK1* AM1*
AM3* AK1*
AK4* AK2*
AL1* AL1*
AK2* AM3*
AK3* AL2*

After sorting each column independenly, you get:
List1 List2
AK1 AK1
AK1* AK1*
AK2 AK2
AK2* AK2*
AK3* AK3
AK4* AK4
AL1 AL1
AL1* AL1*
AL2 AL2*
AL3 AL3*
AM1 AM1*
AM2* AM2
AM3 AM3
AM3* AM3*

After Advanced Filter > Copy to another location > Unique records:
List1 List2
AK1 AK1
AK2 AK2
AK3* AK3
AK4* AK4
AL1 AL1
AL2 AL2*
AL3 AL3*
AM1 AM1*
AM2* AM2
AM3 AM3

Find/Replace colored cells with a number
Go to > Special > Constants > Numbers
Edit > Clear All

List1 List2
AK1 AK1
AK2 AK2
AK3
AK4
AL1 AL1
AL2
AL3
AM1
AM2
AM3 AM3

 

[Lori]

As a further alternative: with the data as in Herbert's example choose

Data > PivotTable > Multiple Consolidation Ranges

For the data range, select the table and include an extra column to the
left which could just be blank then click Finish, the other options are
not important.

Having created the PivotTable, drag the Value field from the field list
to the column position to get this layout:

Value List1 List2
AK1 1 1
AK2 1 1
AK3 1
AK4 1
AL1 1 1
AL2 1
AL3 1
AM1 1
AM2 1
AM3 1 1

This is easily updated: if the data changes you can choose Refresh
Data.

If you want you can copy this as values to the first cell of a new
sheet and replace the 1's with text by choosing Edit > Replace Find:
'*' Replace with: '=a:a' and then copy and paste values.

 

[Herbert Seidenberg]

Great use of Pivot Table, Lori.
Advantages: Refreshable, less steps.
Here is a further step to make the text refreshable:
Select the portion of the PT shown by Lori.
Insert > Name > Create > Top Row
Adjacent to the PT enter these two formulas:
=IF(List1,Value,"")
=IF(List2,Value,"")
It then will look like this:
Value List1 List2
AK1 1 1 AK1 AK1
AK2 1 1 AK2 AK2
AK3 1 AK3
AK4 1 AK4
AL1 1 1 AL1 AL1
AL2 1 AL2
AL3 1 AL3
AM1 1 AM1
AM2 1 AM2
AM3 1 1 AM3 AM3

 

[Lori]

Thanks Herbert, Your further steps addressed one of the slight
drawbacks with pivots - that they cannot display text in the data
field. As a final step - to illustrate the many possible ways to
complete a task - here's a query which returns the list below and is
refreshable but on the minus side is a little less flexible than the
pivottable approach.

List 1 2
AK1 AK1 AK1
AK2 AK2 AK2
AK3 AK3
AK4 AK4
AL1 AL1 AL1
AL2 AL2
AL3 AL3
AM1 AM1
AM2 AM2
AM3 AM3 AM3

Steps:

1. Name the list by typing "A" in the name box next to the formula bar.
2. Choose Data > Import External Data > Import Data
3. Select files of type: Excel and locate the workbook from the
directory list.
4. Click OK then Edit query, Command Type: SQL, Command Text:

TRANSFORM First(List) SELECT List FROM (SELECT [List1] AS List,1 AS ID
FROM [A] UNION SELECT [List2],2 FROM [A]) GROUP BY List PIVOT ID;

(Note: The query can be on the same sheet as the data)