Dispatching Materials details output as plain Text

[bobby786]

Dear All Excel Experts ,
My job requires me to send Text message to our vendors to dispatch the materials which contains the following information

Supplier Name : Longzhen
Model NO : S25
Material Name : Colorbox
Qty : 500 pcs
Address: Room 505 , Commercial Plaza , 104 Road , Unit 2 , Baoan District
Mr.John
Tel: 8765423
Notes: Please use lifter when the goods to this address

I have excel sheets :
Dispatch = Which will be used to enter the data
Address_list= which contains Supplier name and its address details with contact person name and tel no.
Material_Names = which contains the names of the material which will be needed to dispatch it.

Right now i have to manually copy paste everything into "Dispatch" and then i will copy all cells and paste them in notepad , then copy again and paste in our Text-message App which is similar to Whats-app.

I want to make this process automatic.

Here i will try to explain it , please forgive me for language and long post , as English is not my native language.

1. I want when i enter the Supplier name , Model NO , Qty after that "Material Name" column in "dispatch" sheet should give me option to select materials from "Material_Name" sheet , so i don't have to type it , in post as example i just used the simple name but in real life my material names contains words and numbers which is hard to remember always.So i like to have selection or drop-down or whatever is possible to easily select the available material names.

2.Address Column in "Dispatch" should get the corresponding address of that supplier automatically , so i dont' have to copy paste it , As soon as i enter the supplier name it get the address automatically.

3."Notes" Column in "Dispatch" sheet should get the notes from corresponding sheet , i have few different notes for different supplier , here i have no clue how to make it automatic please share your suggestion for it.

4. Last but not least , when i select all my data copy and paste it should give me Plain Text output like the Below:
Supplier Name : Longzhen
Model NO : S25
Material Name : Colorbox
Qty : 500 pcs
Address: Room 505 , Commercial Plaza , 104 Road , Unit 2 , Baoan District
Mr.John
Tel: 8765423
Notes: Please use lifter when the goods to this address

To download the spreadsheet click on this link Dispatch.Materials

Remarks: Somehow XL2BB capture range is not working when i paste the range it shows like this " ￿￿" , therefore i have upload my spreadsheet to my cloud web page in case if any helper want to take a look , i have attached the screenshot with the post as well.

I hope to have help from you guys Thanks in advance

Regards

 

[Paul Ked]

Would you consider a Userform for this? It may be simpler for both of us!

 

[Paul Ked]

More questions!

1. I need to split the address, name and telephone into different columns otherwise they may not alwys be picked up. That ok?
2. Is S25 the product and available in colourbox, blister etc? If so, I'd prefer to set it out like:

Book1
	A	B	C	D	E	F	G	H
1	Product	Options
2	S25	Colorbox	Blister	Cartons	Warranty Card	Labels
3	S26	Bottle	Magnum	Jeroboam	Methuselah	Salmanazar	Balthazar	Nabuchadnezzar
Material_Names

 

[Akuini]

Remarks: Somehow XL2BB capture range is not working when i paste the range it shows like this " ￿￿" ,

Please read this:

XL2BB 2 Squares ￿￿

If you paste you XL2BB in your post and get 2 squares like below, then you likely have 1 or more Windows File Explorer windows open. Close them and re-generate your XL2BB code and it should be correct this time. (Not all users with Windows File Explorer windows open experience this problem) ￿￿...

www.mrexcel.com

 

[bobby786]

Would you consider a Userform for this? It may be simpler for both of us!

Yes i am open for everything , i just want to make my manual process more faster and smarter with your precious help. But one thing i would like to add here there will be CHINESE characters in real data like in address , i am not aware if USERFORM get angry with Chinese language.

Regards

 

[Paul Ked]

Great!

i am not aware if USERFORM get angry with Chinese language

I wouldn't have thought so, but let us try!

 

[bobby786]

More questions!

1. I need to split the address, name and telephone into different columns otherwise they may not alwys be picked up. That ok?
2. Is S25 the product and available in colourbox, blister etc? If so, I'd prefer to set it out like:

Book1
	A	B	C	D	E	F	G	H
1	Product	Options
2	S25	Colorbox	Blister	Cartons	Warranty Card	Labels
3	S26	Bottle	Magnum	Jeroboam	Methuselah	Salmanazar	Balthazar	Nabuchadnezzar
Material_Names

1. Yes i can make address , names , telephone in different columns , i just have to redo my data but thats i know how to do it , I will make it as per your say above.
2. this is trickly as every product will have different materials to use , Let me say S25 is a main commodity for example its a Charger , but to make it complete product we need to order Colorbox , Blister , Warranty Card etc . How you set it up is no problem for me as long as it will make it easy for me the whole process and i have dont have to look for right address and names everytime we need to make the dispatch text message.


Regards

 

[bobby786]

Please read this:

XL2BB 2 Squares ￿￿

If you paste you XL2BB in your post and get 2 squares like below, then you likely have 1 or more Windows File Explorer windows open. Close them and re-generate your XL2BB code and it should be correct this time. (Not all users with Windows File Explorer windows open experience this problem) ￿￿...

www.mrexcel.com

thanks i will read it , it will help me alot if it can get it fixed.

 

[Paul Ked]

I couldn't sleep so I knocked up this.

It needs de-bugging and some error trapping adding, but let me know if the concept is ok. We can develop it more once you get a feel of it!

 

[bobby786]

@Paul Ked
Its really appreciate-able that you have done the effort. I just took a 3 hours sleep , first thing i check was your post , I have tried the spreadsheet shared by you. But i dont know what i am doing wrong , on Dispatch sheet when i enter the Supplier name nothing happens , similarly when i enter the material name also nothing happen.May be i am missing the key point on how it will work.
if you require i will capture a video on how i am doing it and upload it here , in case if you want to have a look. ?

Regards