S
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
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