Compare two columns by aligning same-values and spacing out differences

  • Thread starter Thread starter Jon Davis
  • Start date Start date
J

Jon Davis

How space out mismatches of two similar columns while keeping like cells
aligned? For example, how do I convert a worksheet containing (in columns A
and B):

A A
B B
C D
D E
F G
....

... to ..

A A
B B
C
D D
E
F
G
 
One play which gets you there ...

The play:
1. Gather source data into one col
2. Use advanced filter > uniques on the combined source to extract a uniques
list
3. Do a Data > Sort on the uniques list to get it into alpha order
4. Use formulas to compare and re-align the source cols in 2 other cols

Illustrated in this sample:
http://www.freefilehosting.net/download/NDkxNjg=
Compare n align 2 cols in sorted alpha order

Steps
1. Copy n paste source data in cols A and B into col C,
one paste below the other (order immaterial).
Enter a col label in C1, eg: All

2. Select col C, click Data > Filter > Advanced Filter
List range: $C:$C
Check "Copy to another location"
Copy to: $D$1
Check "Unique records only"
Click OK
This extracts a uniques list into col D

3. Select col D,
do a Data > Sort* > Sort by "All" > Ascending > OK
*continue with current selection

4. Then just place in
E2: =IF(ISNUMBER(MATCH(D2,A:A,0)),D2,"")
F2: =IF(ISNUMBER(MATCH(D2,B:B,0)),D2,"")
Select E2:F2, copy down to the last row of data in col D
Cols E & F will return the exact results that you seek
 
Back
Top