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.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Work order

Work Description

Tag

Area

Require Bypass?

Shutdown

Input Tag HERE!

Work order

Work Description

Tag

Area

Require Bypass?

Shutdown

2

3

60166

Inspetion of Mixer

PE-Y-7601

P1

yes

yes

PE-Y-7609

60174

Declogging of lines

PE-Y-7609

P3

yes

no

4

60170

Inspetion of Belt

PE-Y-7607

P2

yes

no

PE-Y-7730

60182

Cleaning of filter

PE-Y-7730

P5

no

no

5

60174

Declogging of lines

PE-Y-7609

P3

yes

no

PP-Y-7772

60186

Replacement of Valves

PP-Y-7001

P4

yes

yes

6

60178

Manual Barring of compressor

PE-Y-7610A

P4

no

yes

PP-Y-7001

7

60182

Cleaning of filter

PE-Y-7730

P5

no

no

PP-Y-7202

8

60186

Replacement of Valves

PP-Y-7001

P4

yes

yes

9

60190

Sampling of Vessels

PP-Y-7002

P5

no

yes

10

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[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.