Managing product info

[sjs]

I'm implementing a new business system and need to
inventory our existing stock and import it into the new
system. Unfortunately the SS we're using to capture the
inventory isn't formatted the same way the SS I use to
import the data into the new biz system.

In our warehouse we can have one product with multiple Lot
numbers, for example product id: 1234 can have 4 lot
numbers: abc, def, ghi, and jkl all containing different
quantities.

The Inventory SS uses one row to capture multiple Lots and
looks like this:

Column A Column B Column C Column D Column E etc...
Product ID Lot # Quantity Lot # Quantity etc...
123 abc 100 def 200
456 ghi 50 jkl 100

The Import SS I use to import into my biz system looks
like this:

Column A Column B Column C Column D Column E etc...
Product ID Lot # Quantity
123
456
789

So based on the Inventory SS for product 123 I need to
insert rows into the Import SS to look like this:

Column A Column B Column C Column D Column E etc...
Product ID Lot # Quantity
123 abc 100
123 def 200
456
789

If product 456 has 4 lots w/quantities of 50, 100, 150
and the Import SS would then look like this:

Column A Column B Column C Column D Column E etc...
Product ID Lot # Quantity
123 abc 100
123 def 200
456 ghi 50
456 jkl 100
456 mno 150
456 pqr 200
789

So, I need to take the Product ID from the Inventory SS
and do a lookup in the Import SS then insert a row for
each Lot in the perspective Product ID, copying the Lot
and quantity for each lot.

Anyone have any ideas? Anyone read this far down?!

tks,
steve

 

[agarwaldvk]

Steve

I am not so sure what is your level of competency in using VBA fo
Excel. I once had a similar problem which I got around using VBA.

From my understanding, looping is something which is directly no
available using the front end functionality.

If you are happy using VBA, I have attached a file which shows the cod
in the module sheet and the output on one of the worksheets. Both th
old and the new data is presumed to be on the some worksheet in th
same workbook. If this is not the case, you may want to appen
references to the relevant workbooks and worksheets. I hope this work
for you. Please feel free to check with me if you encounter an
problems. I have checked it though for the shown layout.


The code is shown here (only because I am not so sure how th
attachment will work) :-


Sub test()
writeRowStart = 2 'assuming the first row is used for descriptors
writeRowWrkg = writeRowStart
wrkg = 2 'assuming your data in your old system starts in row2
Change this to whatever row number your original data starts in
productNameCol = 8 'assuming the product name to be in column8 a
shown in the attached worksheet.
Do While Cells(wrkg, productNameCol) <> ""
thisProductName = Cells(wrkg, productNameCol).Value
startcol = productNameCol
wrkgcol = startcol
count1 = 0
Do While Cells(wrkg, wrkgcol) <> ""
thisCellValue = Cells(wrkg, wrkgcol).Value
pos1 = InStr(1, thisCellValue, "Lot", vbTextCompare)
If pos1 <> 0 Then
count1 = count1 + 1
Cells(writeRowWrkg, 1) = thisProductName
Cells(writeRowWrkg, 2) = Cells(wrkg, wrkgcol).Value
Cells(writeRowWrkg, 3) = Cells(wrkg, wrkgcol
1).Value
writeRowWrkg = writeRowWrkg + 1
End If
wrkgcol = wrkgcol + 1
Loop
wrkg = wrkg + 1
Loop
End Sub



But if you do not intend taking that path (the fact that you have pu
your query in the Worksheet Functions category and not in Programmin
section), then, my apologies but I then do not know enough to offer an
help!


Best regards


Deepak Agarwa