copying all details of scanned tags

[its_me_renan]

Please help me to make a program using excel which I can use in my current work. As you can see I have a series of inputs from column A to column F. What I want to happen is when I input a tag or series of tags on column H starting at H3, it will scan all tags at column C and all similar tags will copied together with their corresponding details to column J to column O. Thank you. See below sample.

[TABLE="width: 1357"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Work order[/TD]
[TD]Work Description[/TD]
[TD]Tag[/TD]
[TD]Area[/TD]
[TD]Require Bypass?[/TD]
[TD]Shutdown[/TD]
[TD] [/TD]
[TD]Input Tag HERE! [/TD]
[TD] [/TD]
[TD]Work order[/TD]
[TD]Work Description[/TD]
[TD]Tag[/TD]
[TD]Area[/TD]
[TD]Require Bypass?[/TD]
[TD]Shutdown[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]60166[/TD]
[TD]Inspetion of Mixer[/TD]
[TD]PE-Y-7601[/TD]
[TD]P1[/TD]
[TD]yes[/TD]
[TD]yes[/TD]
[TD] [/TD]
[TD]PE-Y-7609[/TD]
[TD] [/TD]
[TD]60174[/TD]
[TD]Declogging of lines[/TD]
[TD]PE-Y-7609[/TD]
[TD]P3[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]60170[/TD]
[TD]Inspetion of Belt[/TD]
[TD]PE-Y-7607[/TD]
[TD]P2[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD] [/TD]
[TD]PE-Y-7730[/TD]
[TD] [/TD]
[TD]60182[/TD]
[TD]Cleaning of filter[/TD]
[TD]PE-Y-7730[/TD]
[TD]P5[/TD]
[TD]no[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]60174[/TD]
[TD]Declogging of lines[/TD]
[TD]PE-Y-7609[/TD]
[TD]P3[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD] [/TD]
[TD]PP-Y-7772[/TD]
[TD] [/TD]
[TD]60186[/TD]
[TD]Replacement of Valves[/TD]
[TD]PP-Y-7001[/TD]
[TD]P4[/TD]
[TD]yes[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]60178[/TD]
[TD]Manual Barring of compressor[/TD]
[TD]PE-Y-7610A[/TD]
[TD]P4[/TD]
[TD]no[/TD]
[TD]yes[/TD]
[TD] [/TD]
[TD]PP-Y-7001[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]60182[/TD]
[TD]Cleaning of filter[/TD]
[TD]PE-Y-7730[/TD]
[TD]P5[/TD]
[TD]no[/TD]
[TD]no[/TD]
[TD] [/TD]
[TD]PP-Y-7202[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]60186[/TD]
[TD]Replacement of Valves[/TD]
[TD]PP-Y-7001[/TD]
[TD]P4[/TD]
[TD]yes[/TD]
[TD]yes[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]60190[/TD]
[TD]Sampling of Vessels[/TD]
[TD]PP-Y-7002[/TD]
[TD]P5[/TD]
[TD]no[/TD]
[TD]yes[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Yongle]

If there is more than one match then this will NOT work
(finds first match only)

In J3 copied down

=INDEX($A:$F,MATCH($H3,$C:$C,0),COLUMN(A3))

To suppress error if match not found

=IFERROR(INDEX($A:$F,MATCH($H3,$C:$C,0),COLUMN(A3)),"")

 

[vcoolio]

Hello imr,

Here is a VBA solution that may work for you:-

Sub Test()
        
        Dim ar As Variant, lr As Long
        
        lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
        ar = Sheet1.Range("H3", Sheet1.Range("H" & Sheet1.Rows.Count).End(xlUp))

Application.ScreenUpdating = False

        Sheet3.Cells.Clear
        Sheet1.Range("J3:O" & lr).Clear

If IsArray(ar) Then
For i = 1 To UBound(ar)
        Sheet1.Range("C1", Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, ar(i, 1)
         Sheet1.Range("A2", Sheet1.Range("F" &  Sheet1.Rows.Count).End(xlUp)).Copy Sheet3.Range("J" &  Rows.Count).End(3)(2)
        Sheet3.UsedRange.Copy Sheet1.[J3]
        Sheet1.[C1].AutoFilter
Next i
Else
        Sheet1.Range("C1", Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, ar
         Sheet1.Range("A2", Sheet1.Range("F" &  Sheet1.Rows.Count).End(xlUp)).Copy Sheet3.Range("J" &  Rows.Count).End(3)(2)
        Sheet3.UsedRange.Copy Sheet1.[J3]
        Sheet1.[C1].AutoFilter
End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code places the tag numbers that you enter in Column H into an array then filters Column C for the array values and copies the relevant rows of data (from Columns A:F) over to Columns J:O.
The code also covers the fact that there may only be one element in the array every now and then.
The code also uses a helper sheet (Sheet3 in this case).

You may also note that I have used the sheet codes (Sheet1, Sheet3 etc.) in the above code.
Is there any reason for not using Row2?

I've uploaded a little sample file at the following link for you to play with:-

http://ge.tt/3q***1r2

Click on the "RUN" button to see it work. Alter the amount of tag numbers in Column H to see how the code adjusts.

I hope that this helps.

Cheerio,
vcoolio.

 

[its_me_renan]

Thank you so much..

 

[vcoolio]

You're welcome imr.

Cheerio,
vcoolio.