excel 2010compare multiple columns of dataClosed

Question

Hello,  

I have been given 6 seperate excel docs with serial numbers in, i have created 6 columns containing these different serial numbers but now need to sort them to show which columns have exactly the same serial number in them with the hope of eventually having a single remaining column containing all serial numbers that are in each column. tried sorting and using a few different filters but have not worked out how to do this yet. 

Help, please! 


Configuration: Windows 7 / Firefox 7.0.1

TrowaD · Answer

Hi DavidR,

Put the 6 columns of data in one column, now sort your data.

I assumed that your single column of data is in column A.

Using the follwing code will put the result in column B:
Sub test()
Dim lRow1 As Integer
Dim lRow2 As Integer

lRow1 = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A1:A" & lRow1)
    If cell.Value = cell.Offset(1, 0).Value And _
    cell.Value = cell.Offset(2, 0).Value And _
    cell.Value = cell.Offset(3, 0).Value And _
    cell.Value = cell.Offset(4, 0).Value And _
    cell.Value = cell.Offset(5, 0).Value Then

lRow2 = Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row

    cell.Copy Destination:=Range("B" & lRow2)

        End If
    Next
End Sub
Best regards,
Trowa

Excel 2010compare multiple columns of data

1 response

Similar discussions

Subscribe To Our Newsletter!