# Need to "align" two columns of similar entries in Excel

Have 2 similar columns in excel.

One column is a very long (master) list, and one column is a short one that has entries that are also in the first column....

Is there a way to adjust the 2nd column so that the numbers are dropped next to the matching entry in the first column?

Column A                              Column B
20090410120923.812.3           20090417211728.140.3
20090417211125.875.3           20090430091757.406.3
20090417211728.140.3           20090426114717.718.3
20090426114717.718.3           20090417211125.875.3
20090428173220.140.3           20090429111035.1909

becomes....

20090410120923.812.3
20090417211125.875.3           20090417211125.875.3
20090417211728.140.3           20090417211728.140.3
20090426114717.718.3           20090426114717.718.3
20090428173220.140.3           20090417211728.140.3

Note the missing number in column B had no match in Column B, so presumably it matches elsewhere in the list.

The list is over 17,000 for "A", and only 650 for "B" -- so I'm looking to do it programmatically.

Ideas?
LVL 5
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
I answered a question yesterday that, while different, could be applied to this very situation.

If you are able to move column B and simply use it as a reference, you should be able to use a formula like:
``````=IF(ISNA(VLOOKUP(A1,\$C\$1:\$C\$65535,1,FALSE)),A1,"")
``````

This assumes that:
(1) Your data starts in cell A1
(2) Your reference column for matches has been moved to column C

When pasted into B1 and copied down this will compare the value in the corresponding A cell, look for a match in column C.  If a match is found the value is repeated in column B, if not the cell is left blank.

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Older than dirtCommented:
If your data starts in Row 2 then this will need adjustment.

``````Private Sub CommandButton1_Click()
Dim LastRowColA As Long
Dim LastRowColB As Long
Dim strColB() As String
Dim lngIndex As Long
Dim lngIndex2 As Long
Dim lngCountB As Long

LastRowColA = Range("A65536").End(xlUp).Row
LastRowColB = Range("B65536").End(xlUp).Row

ReDim strColB(LastRowColB - 1)
For lngIndex = 0 To LastRowColB
If Range("B" & lngIndex + 1).Value <> "" Then
strColB(lngCountB) = Range("B" & lngIndex + 1).Value
Range("B" & lngIndex + 1).Value = ""
lngCountB = lngCountB + 1
End If
Next
ReDim Preserve strColB(lngCountB - 1)

For lngIndex = 0 To UBound(strColB)
For lngIndex2 = 1 To LastRowColA
If strColB(lngIndex) = Range("A" & lngIndex2).Value Then
Range("B" & lngIndex2).Value = strColB(lngIndex)
Exit For
End If
Next
Next

End Sub
``````
Older than dirtCommented:
If it starts in row 2 make the changes shown here.

``````Private
Sub CommandButton1_Click()
Dim LastRowColA As Long
Dim LastRowColB As Long
Dim strColB() As String
Dim lngIndex As Long
Dim lngIndex2 As Long
Dim lngCountB As Long

LastRowColA = Range("A65536").End(xlUp).Row
LastRowColB = Range("B65536").End(xlUp).Row

ReDim strColB(LastRowColB - 1)
'For lngIndex = 0 To LastRowColB
For lngIndex = 1 To LastRowColB

If Range("B" & lngIndex + 1).Value <> "" Then
strColB(lngCountB) = Range("B" & lngIndex + 1).Value
Range("B" & lngIndex + 1).Value = ""
lngCountB = lngCountB + 1
End If
Next
ReDim Preserve strColB(lngCountB - 1)

For lngIndex = 0 To UBound(strColB)
'    For lngIndex2 = 1 To LastRowColA
For lngIndex2 = 2 To LastRowColA

If strColB(lngIndex) = Range("A" & lngIndex2).Value Then
Range("B" & lngIndex2).Value = strColB(lngIndex)
Exit For
End If
Next
Next

End Sub
``````

Do you want to validate for the situation where something is in col B but not in col A?
Author Commented:
I was looking at vLOOKUP as an option, and in fact it does exactly what I wanted it to do.
Simply!
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.