Previous Topic Next Topic

Excel 2010compare multiple columns of data

Closed
DavidR - Oct 27, 2011 at 07:26 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 27, 2011 at 10:17 AM
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!


1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Oct 27, 2011 at 10:17 AM
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
0

Similar discussions

Unlock safe folder without losing any data
Mintu -
HelpiOS -

1 response
How To Fix Mint Mobile Data Not Working?
sachin327 -
joshitha -

2 responses
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses