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,634
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,081,563
Messages
5,359,618
Members
400,540
Latest member
JimUSMC

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top