Need some help creating sku's from multiple worksheets

[jasonr704]

I could sure use some help creating sku's from multiple worksheets, then be able to cross reference a master list to tell which ones are missing and need to be added into my inventory.

Step #1
1) Combine the Design sku's (found on Designs worksheet) with device sku's (on Devices worksheet)
--> Create sku's for all combinations of designs + devices
--> Need to also add a prefix for all combinations: "SKN"
resulting in this example: SKNHELKAT01AKNDL2
2) All combinations should then show up on combinations worksheet.

Step #2
Then I need to cross reference the sku's just created with sku's previously created which are found on the masterlist tab. I need for all NEW sku's to then appear on the "ADD" worksheet.


Thanks VERY much for the help!

[Tinbendr]

Here's my contribution.

David

[jasonr704]

thanks very much!
would you mind posting your macro or steps you took creating that? I'd like to be able to use it on an ongoing basis

[Tinbendr]

After going back through and adding comments, I discovered part of the Find routine wasn't needed. Shorter code, but not much faster. Still takes about three minutes on my machine.

In Module 1.
[vba]Sub ICantBelieveImDoingThis()

'Define some lastrow variables
Dim Src1Rows As Long
Dim Src2Rows As Long

'Define some loop counters
Dim lCountA As Long
Dim lCountB As Long
Dim lCountC As Long

'Define the result variables
Dim WrkStr As String
Dim FndStr As Range

'Set the C counter to 1
lCountC = 1

'Turn off screen updating. (Makes it run faster.)
Application.ScreenUpdating = False

'Clear the contents from Combined Col A
Worksheets("Combined").Columns(1).ClearContents
'Dynamically determine last row on Designs and Devices.
Src1Rows = Worksheets("Designs").UsedRange.Rows.Count
Src2Rows = Worksheets("Devices").UsedRange.Rows.Count

'Outside loop for Col A of Designs
For lCountA = 2 To Src1Rows
'Inside loop for Col A of Devices
For lCountB = 2 To Src2Rows
'WrkStr will hold the concanted new SKU
WrkStr = "SKN" & Worksheets("Designs").Range("B" & lCountA).Value & _
Worksheets("DEVICES").Range("A" & lCountB).Value
'This loop counter keeps track of the next row in the Combined sheet.
lCountC = lCountC + 1
'Insert it to the sheet
Worksheets("Combined").Range("A" & lCountC) = WrkStr
Next
Next

'Dynamically determine last row on Combined and Masterlist.
Src1Rows = Worksheets("combined").UsedRange.Rows.Count
Src2Rows = Worksheets("masterlist").UsedRange.Rows.Count

'Reset Loop counters
lCountA = 0
lCountB = 0

'Clear contents of Adds, Col A
Worksheets("adds").Columns(1).ClearContents

'Define Range to work with.
With Worksheets("Masterlist").Range("a1:a" & Src2Rows)
'Loop to cycle through newly combined SKU's
For lCountA = 2 To Src1Rows
'Set variable to used in search
WrkStr = Worksheets("Combined").Range("A" & lCountA)

'Start the Search
Set FndStr = .Find(WrkStr, LookIn:=xlValues)
If FndStr Is Nothing Then
'A match was NOT found, so copy the search string to Adds.
'This loop counter keeps track of the next row in the Adds sheet.
lCountB = lCountB + 1
'Debug.Print lCountB 'for testing purposes.
'Write it to the adds sheet.
Worksheets("Adds").Range("A" & lCountB) = WrkStr
End If
Next
End With

'Turn screenupdating back on
Application.ScreenUpdating = True

End Sub
[/vba]

[jasonr704]

ICantBelieveYouDidThatEither!

But thank you very much for doing so! Really appreciate your Excel expertise!
Email me and I'll send you a thank you gift from my company.