Solved

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

Posted on 2012-09-18
310 Views
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?
0
Question by:btetlow-expert

LVL 4

Accepted Solution

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.
0

LVL 44

Expert Comment

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
``````
0

LVL 44

Expert Comment

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?
0

LVL 5

Author Closing Comment

I was looking at vLOOKUP as an option, and in fact it does exactly what I wanted it to do.
Simply!
0

Featured Post

Suggested Solutions

Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.