Match and copy

[clownstyle]

Hi everyone,

Got a problem spreadsheet and it's been a while since I've done anything with Excel. If you pros can cast an eye over the following I would be very grateful :D

I've got a large report that has exported data about printers. I have 6 sheets that are just data, with serial numbers for each printer. The last sheet contains serial numbers for each of the machines. Basically I need somehow to search the first 1-6 worksheets for text that contains "For printer [ID:4]" . [ID:5] [ID:6] etc.. and add the machine serial number, copied from the other sheet.

I can probably explain better with pictures

https://imgur.com/1MLkean

https://imgur.com/gXVpz3Z

 

[mumps]

It would be easier to test possible solutions if you could post a copy of your file instead of a picture.

 

[clownstyle]

It would be easier to test possible solutions if you could post a copy of your file instead of a picture.

Good call.. It was 50+Mb with all 6 tabs of data, so I've removed a few to post, also removed data from column C because it may contain sensitive info about what was printed

http://www.filedropper.com/mrexcelupload

 

[mumps]

I have a few questions for clarification. On the Machine ID sheet, I see Model numbers but no serial numbers. On Sheet1, I see one occurrence of "For printer [ID:4]" but no occurrences of this type are found on Sheet2. Sheet also has no headers in row 1. Could you please explain in detail what you want to do using a few examples from your data referring to specific cells and worksheets?

 

[clownstyle]

I have a few questions for clarification. On the Machine ID sheet, I see Model numbers but no serial numbers. On Sheet1, I see one occurrence of "For printer [ID:4]" but no occurrences of this type are found on Sheet2. Sheet also has no headers in row 1. Could you please explain in detail what you want to do using a few examples from your data referring to specific cells and worksheets?

Thanks mumps,

On the Machine ID sheet, I see Model numbers but no serial numbers.

The model numbers on the machine ID sheet are the serial numbers for the devices.

On Sheet1, I see one occurrence of "For printer [ID:4]" but no occurrences of this type are found on Sheet2.

There is only one occurrence of each ID number, then a lot of data underneath, until it gets to the next device so you will see "For printer [ID:4]" "For printer [ID:5]" "For printer [ID:6]" etc each one relates to a specific device, but the serial for that device is on the Machine ID sheet.

Sheet also has no headers in row 1.

The export has around 600,000 rows on each page, when it hits the max row length it spills over onto a new sheet, it's how the report comes unfortunately.

Could you please explain in detail what you want to do using a few examples from your data referring to specific cells and worksheets?

On the first sheet you'll notice in column B there are a number of device ID's (not the numbers e.g. 887987) they are spread throughout column B (you'll have to look hard to find them, or filter for them!)

They look like this:

For printer [ID:4] GHTA5501i - TASKalfa 5501i
For printer [ID:5] 6TA4551ci - TASKalfa 4551ci
For printer [ID:6] GHTA4551ci - TASKalfa 4551ci
For printer [ID:7] GHTA8001i1 - TASKalfa 8001i
For printer [ID:8] GHTA8001i2 - TASKalfa 8001i
For printer [ID:9] GHTA8001i3 - TASKalfa 8001i
For printer [ID:10] GHTA8001i4 - TASKalfa 8001i
For printer [ID:11] 14TA4551ci - TASKalfa 4551ci
For printer [ID:12] GTA3501iLINK - TASKalfa 3501i
For printer [ID:13] 1TA4501i3PK2 - TASKalfa 4501i
For printer [ID:14] GTA3010iLINK - TASKalfa 3010i
For printer [ID:15] 9TA4551ci2 - TASKalfa 4551ci
For printer [ID:16] GTA3010iREC - TASKalfa 3010i
For printer [ID:17] GTA3501iPOST - TASKalfa 3501i
For printer [ID:21] 1TA4501i1PK1 - TASKalfa 4501i

That's the first page alone.

What I want to do is lookup each one, and where it exists place the Model / Serial number from the "Machine ID" worksheet into column A next to the device ID on Sheet 1 and sheet 2. For example:

Serial Number	ID
L8F3X02317	For printer [ID:5] 6TA4551ci - TASKalfa 4551ci
L8F4103561 	For printer [ID:6] GHTA4551ci - TASKalfa 4551ci
L8V4100305	For printer [ID:7] GHTA8001i1 - TASKalfa 8001i
	        For printer [ID:8] GHTA8001i2 - TASKalfa 8001i
	        For printer [ID:9] GHTA8001i3 - TASKalfa 8001i
	        For printer [ID:10] GHTA8001i4 - TASKalfa 8001i
	        For printer [ID:11] 14TA4551ci - TASKalfa 4551ci
	        For printer [ID:12] GTA3501iLINK - TASKalfa 3501i
	        For printer [ID:13] 1TA4501i3PK2 - TASKalfa 4501i
	        For printer [ID:14] GTA3010iLINK - TASKalfa 3010i

So I need it to copy the Model/Serial number from column B on the machine ID sheet to the cell in column A marked Serial.

It may help for me to remove some rows and show that example instead, like I say there is a lot of data.

 

[clownstyle]

Here is a more simple version of the sheet, without the useless data..

http://www.filedropper.com/mrexceluploadsimple

In that example > copy model number from the worksheet Machine ID > insert in corresponding cell next to the ID number in Sheet 1 so it will populate the serial number field next to the device.

Bearing in mind the original document have 6 worksheets of data, with around 600,000 rows each

 

[clownstyle]

Any help with this one guys?

 

[mumps]

Try this macro:

Sub CopySerialNum()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim ws As Worksheet
    Dim SerNum As Range
    Dim foundNum As Range
    For Each ws In Sheets
        If ws.Name <> "Machine ID" Then
            If ws.Name <> "Sheet1" Then
                Sheets("Sheet1").Rows(1).Copy
                ws.Rows(1).Insert
            End If
            LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            ws.Range("A1:B" & LastRow).AutoFilter Field:=2, Criteria1:="For printer" & "*"
            For Each SerNum In ws.Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible)
                Set foundNum = Sheets("Machine ID").Range("A:A").Find(Split(SerNum, "]")(0), LookIn:=xlValues, lookat:=xlPart)
                If Not foundNum Is Nothing Then
                    SerNum.Offset(0, -1) = foundNum.Offset(0, 1)
                End If
            Next SerNum
            If ws.FilterMode Then ws.ShowAllData
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

You will notice that the macro copies and inserts the header row from Sheet1 to those sheets with no headers. This assumes that the sheets have at least one empty row at the bottom. Hopefully, this will always be the case.

 

[clownstyle]

Try this macro:

Sub CopySerialNum()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim ws As Worksheet
    Dim SerNum As Range
    Dim foundNum As Range
    For Each ws In Sheets
        If ws.Name <> "Machine ID" Then
            If ws.Name <> "Sheet1" Then
                Sheets("Sheet1").Rows(1).Copy
                ws.Rows(1).Insert
            End If
            LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            ws.Range("A1:B" & LastRow).AutoFilter Field:=2, Criteria1:="For printer" & "*"
            For Each SerNum In ws.Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible)
                Set foundNum = Sheets("Machine ID").Range("A:A").Find(Split(SerNum, "]")(0), LookIn:=xlValues, lookat:=xlPart)
                If Not foundNum Is Nothing Then
                    SerNum.Offset(0, -1) = foundNum.Offset(0, 1)
                End If
            Next SerNum
            If ws.FilterMode Then ws.ShowAllData
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

You will notice that the macro copies and inserts the header row from Sheet1 to those sheets with no headers. This assumes that the sheets have at least one empty row at the bottom. Hopefully, this will always be the case.

Wow, that's great mumps.. Unfortunately the sheets don't have a spare row, they are all used with data and excel doesn't allow me to add another row. I'll test the macro now

Edit - Macro is coming back with error 9 - Subscript out of range

 

[mumps]

When I run the macro on the file located at this link, it works properly.
https://app.box.com/s/qco16r47c3qfv3bmra8snicpbez608ub