Convert single PO line item to multiple rows based on quantity ordered

[MattsDad]

Hi. I'm trying to build a process to aid our warehouse in printing barcode labels for inventory received. Currently someone has to take the PO/receiving document and manually enter the part number into a text file on a separate row for each individual piece that is received so our barcode printer can print the correct label to attach to our product.

For example, on a basic receiving document we receive 3 units of Item A and 1 unit of Item B that shows like this:
Item Qty
A 3
B 1

The warehouse worker then enters the data like this in the text file that will be printed:
A
A
A
B

The question is, can i do this in Excel 2003 on an XP box? We can easily import this data from the PO/receiving report to Excel, which I have done. I'm trying to figure out how to get Excel to create an output file either directly to a text file or to another tab in the workbook but I don't know how to instruct Excel to use one line of input as a source for more than one line of output.

I've spent several hours trying formulas to no avail. I don't know enough about VBA code to be able to write this myself. Any help you can give will be greatly appreciated.

 

[Peter_SSs]

.. im sorry for disturbing you many times

No need to apologise. You are not disturbing me - I come to the forum because I want to answer questions.

can i do this functions with macro codes ?

That is what we have already done.

with any formulas ?

To do it with formulas, I would use a helper column.
F1 has a stand-alone formula
F2 copied down
The other row 2 formulas are all copied down as far as you might need.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	ORDER_NUMBER	SKU	DESCRIPTION	ORDERED_QUANTITY	DELIVERY_METHOD	10	Order	SKU	Item Description	QTY	Serial Numbers	18 Digit	SR Number	Released By	Delivery Method	Order Status
2	5004811	112170313	iPhone 7 32GB Matte Black Local	2	MovEx	1	5004811	112170313	iPhone 7 32GB Matte Black Local	1					MovEx
3	5004828	112170379	IPhone 6 32GB Grey LTE/ Local	2	ELT Driver	3	5004811	112170313	iPhone 7 32GB Matte Black Local	1					MovEx
4	5004845	112170387	iPhone 8 Plus 64GB Space Grey /Local	2	Sales	5	5004828	112170379	IPhone 6 32GB Grey LTE/ Local	1					ELT Driver
5	5004714	112170402	Iphone X 256 GB Silver /Local	2	NokSmart	7	5004828	112170379	IPhone 6 32GB Grey LTE/ Local	1					ELT Driver
6	5004710	112170404	IPhone X 256 GB Space Gray/Local	2	Union Courier	9	5004845	112170387	iPhone 8 Plus 64GB Space Grey /Local	1					Sales
7							5004845	112170387	iPhone 8 Plus 64GB Space Grey /Local	1					Sales
8							5004714	112170402	Iphone X 256 GB Silver /Local	1					NokSmart
9							5004714	112170402	Iphone X 256 GB Silver /Local	1					NokSmart
10							5004710	112170404	IPhone X 256 GB Space Gray/Local	1					Union Courier
11							5004710	112170404	IPhone X 256 GB Space Gray/Local	1					Union Courier
12
Sheet3

 

[amora1012]

it was awesome you're a genius its working with these formulas very well with my modification so can i put the order number in the result just once per each order from expected data ?

 

[Peter_SSs]

Try these altered formulas for column G onwards.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	O
1	ORDER_NUMBER	SKU	DESCRIPTION	ORDERED_QUANTITY	DELIVERY_METHOD	10	Order	SKU	Item Description	QTY	Delivery Method
2	5004811	112170313	iPhone 7 32GB Matte Black Local	2	MovEx	1	5004811	112170313	iPhone 7 32GB Matte Black Local	1	MovEx
3	5004828	112170379	IPhone 6 32GB Grey LTE/ Local	2	ELT Driver	3		112170313	iPhone 7 32GB Matte Black Local	1	MovEx
4	5004845	112170387	iPhone 8 Plus 64GB Space Grey /Local	2	Sales	5	5004828	112170379	IPhone 6 32GB Grey LTE/ Local	1	ELT Driver
5	5004714	112170402	Iphone X 256 GB Silver /Local	2	NokSmart	7		112170379	IPhone 6 32GB Grey LTE/ Local	1	ELT Driver
6	5004710	112170404	IPhone X 256 GB Space Gray/Local	2	Union Courier	9	5004845	112170387	iPhone 8 Plus 64GB Space Grey /Local	1	Sales
7								112170387	iPhone 8 Plus 64GB Space Grey /Local	1	Sales
8							5004714	112170402	Iphone X 256 GB Silver /Local	1	NokSmart
9								112170402	Iphone X 256 GB Silver /Local	1	NokSmart
10							5004710	112170404	IPhone X 256 GB Space Gray/Local	1	Union Courier
11								112170404	IPhone X 256 GB Space Gray/Local	1	Union Courier
12
Sheet3

 

[amora1012]

dear peter im so glad to meet you here really u make everything going easier for me i appreciate that so im really sorry for my many requests im asking you to make this lastest updated for J2 to be not repeated as well just only the product qty per each order many thanks again

 

[Peter_SSs]

.. updated for J2 to be not repeated as well just only the product qty per each order

For the layout in post 23, try this in J2, copied down.
If it is not what you meant, please provide the expected results for each cell in J2:J11 for that sample data.

=IF(G2="","",VLOOKUP(G2,A$2:D$6,4,0))

 

[amora1012]

For the layout in post 23, try this in J2, copied down.
If it is not what you meant, please provide the expected results for each cell in J2:J11 for that sample data.

=IF(G2="","",VLOOKUP(G2,A$2:D$6,4,0))

its worked very well peter you're a brilliant man i really appreciate that support

 

[amora1012]

i have some problems after all formulas inside i would it like to share it with you peter to be easier to enplane with this file shared on onedrive here https://1drv.ms/x/s!Au5udmT72D-iwFLBvPoZ0iZ_uA-i

 

[Peter_SSs]

i have some problems after all formulas inside ..

Please specify the problems you are having and where they are located. I'm afraid I don't intend to examine 28,000+ cells (which are in a different layout to what I had been dealing with) to try and guess what your problems are.

 

[amora1012]

what i saw till now if you check the last order number 85644 at results and compare it with data in the next tab you'll find the first it item has been repeated 4 times (Order number,SKU,Description) and the delivery method maybe because of VLOOKUP function so can you find the way out to fix that ? thanks alot peter

 

[Peter_SSs]

Neither your previous sample data nor any description you gave in the last week mentioned that the same order number could be repeated on more than one line of the original data.