Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

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?
0
btetlow-expert
Asked:
btetlow-expert
  • 2
1 Solution
 
DustinKikuchiCommented:
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,"")

Open in new window


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
 
Martin LissRetired ProgrammerCommented:
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

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
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

Open in new window


Do you want to validate for the situation where something is in col B but not in col A?
0
 
btetlow-expertAuthor Commented:
I was looking at vLOOKUP as an option, and in fact it does exactly what I wanted it to do.
Simply!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now