copying all details of scanned tags

its_me_renan

Board Regular
Joined
Dec 18, 2011
Messages
70
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.

ABCDEFGHIJKLMNO
1Work orderWork DescriptionTagAreaRequire Bypass?Shutdown Input Tag HERE! Work orderWork DescriptionTagAreaRequire Bypass?Shutdown
2
360166Inspetion of MixerPE-Y-7601P1yesyes PE-Y-7609 60174Declogging of linesPE-Y-7609P3yesno
460170Inspetion of BeltPE-Y-7607P2yesno PE-Y-7730 60182Cleaning of filterPE-Y-7730P5nono
560174Declogging of linesPE-Y-7609P3yesno PP-Y-7772 60186Replacement of ValvesPP-Y-7001P4yesyes
660178Manual Barring of compressorPE-Y-7610AP4noyes PP-Y-7001
760182Cleaning of filterPE-Y-7730P5nono PP-Y-7202
860186Replacement of ValvesPP-Y-7001P4yesyes
960190Sampling of VesselsPP-Y-7002P5noyes
10

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,672
Office Version
365
Platform
Windows
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)),"")
 
Last edited:

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
837
Hello imr,

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


Code:
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.
 

Forum statistics

Threads
1,082,145
Messages
5,363,384
Members
400,734
Latest member
sanpr

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top