Format Test Data

[exceltrip]

Hi All,

We currently use a scanner in our production area that is designed to scan in the data matrix codes of a product at the first build stage to;

a. verify that the data matrix codes can be read by a scanner, and
b. record the data matrix code into a table for future reference.

Each product has the same two data matrix codes for verification purposes; one data matrix code is on the back of the full assembly and another one is on the bottom to make it easier for our operators when checking the data matrix codes.

The data from the first scan is saved into an excel file, which we would like to use in the final build phase to recheck via another scanner that the data matrix codes are correct and valid.

The issue we have is that the scanner at the first scan stage records the data matrix sequentially down a spreadsheet, which creates duplicate data, whereas the scanner at the second and third stations, check the data via a string.

What we would like to do is take this data and remove the duplicate data matrix code and the move the test pass/fail results to be side by side in separate cells as below;

Data Currently Looks Like

Data Needs to Look Like
View attachment 98226

Would anyone know a formula or a simply way we can format the data quickly as there are thousands of rows of data.

Thanks in advance

 

[exceltrip]

Not sure what happened...but the table dropped off my initial post.

Data Currently Looks Like

Data Needs to Look Like

 

[mohadin]

Hi
Try

Sub test()
    Dim a, w
    Dim i&
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    a = Sheets("sheet1").Cells(1).CurrentRegion
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a)
            If Not .exists(a(i, 1)) Then
                .Add a(i, 1), Array(a(i, 1), a(i, 2), "", a(i, 3), a(i, 4))
            Else
                w = .Item(a(i, 1))
                w(2) = a(i, 2)
                .Item(a(i, 1)) = w
            End If
        Next
        ws.Cells(2, 10).Resize(.Count, 5) = Application.Index(.items, 0, 0)
        ws.Cells(1, 10).Resize(, 5) = Array("Data Matrix Codes", "Test#1", "Test#2", "CoC", "String")
        ws.Cells(1, 10).CurrentRegion.EntireColumn.AutoFit
    End With
End Sub