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,292
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,078,467
Messages
5,340,495
Members
399,378
Latest member
voodoo1

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top