Data Comparison and copy to other tab

[stangz71]

I have a script that was created for a previous request but I am looking to modify it for new data. I have the spreadsheet (attached) and the code below. I am looking to place "Grid ID" numbers starting at B6 and below on the "Customer" tab. Then do a search on the "output" tab and if the "Grid ID" matches, then copy the contents from the "A"column to "Q" column on the "output" tab for that Grid ID and copy it to the "C" to "Q" colums on the corresponding Grid ID line on the "Customer" tab. I know the code is close but not sure what I am missing.

<code>

Option Explicit


Sub nomatchnoty()

Dim X, Z, i As Long, j As Long, n As Long, Y(), dic As Object

With Worksheets("Customer")
X = .Range("a6:Q" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

Set dic = CreateObject("scripting.dictionary")
dic.comparemode = 1
With dic

For i = 1 To UBound(X, 1)
If Len(X(i, 3)) Then
.Item(X(i, 3)) = i
End If
Next
End With

With Worksheets("Output")
X = .Range("a23:Q" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

ReDim Y(1 To UBound(X), 1 To UBound(X, 2))

With dic
For i = 1 To UBound(X)
If Len(X(i, 3)) Then
If .exists(X(i, 3)) Then
For j = 4 To UBound(X, 2)
Y(.Item(X(i, 3)), j - 3) = X(i, j)
Next
End If
End If
Next
End With

With Worksheets("Customer")
.Range("C6:Q" & .Rows.Count).ClearContents
.Range("C6").Resize(i - 1, UBound(Y, 2)) = Y
.Columns.AutoFit
End With
End Sub
<code>

[AB33]

stangz71,
Please wrap you code with code tags. You should edit the above code and put it on tags.

[stangz71]

AB33

I will make sure to do this next time but would also like to know if there is anyway to remove the blank space on the D column so the remaining data gets added to the D column?

[AB33]

In which sheet?

[stangz71]

The customer sheet because it writes something to the C column and then adds a blank space to the D column and then writes to the E column. If it's not possible, no worries, just wondering if it could be done, thanks in advance.

[AB33]

It is possible, just need to understand.
The reason for writing blank in column D is because you have empty in column B in sheet output.
The code copies from column A to Q and the second column to be copied is column B and it is blank.

[stangz71]

So if you can copy A, skip "B" and do C:Q, that would be great.

[AB33]

You have also other empty columns in A-Q, but the attached skips column B only.

[stangz71]

That's the winner, thanks again for all your help.

[stangz71]

AB33

Wondering if you are still around and can help me change the spreadsheet that you assisted me with in 2015?

[AB33]

Yes, I am still around, but not as active as in the past. Please attach a sample with a desired output.

[stangz71]

I attached a sample of what I am looking for and added some "Possible Future Column" because I may need to pull in other columns in the future so if you can make it so I can adjust the amount of columns I pull into the "DD_Inventory" tab I would appreciate it, thanks in advance.

[AB33]

The last column is L. See if this works for you.

[stangz71]

That works great thanks for your help but for future reference what would I need to do to extend it beyond column "L"?

[AB33]

If others remain the same, just need to change column L to what ever you want. If you struggle, just drop me a PM

Please Login or Register  to view this content.

[stangz71]

Thanks again for taking the time to help me out.