Dividing Customer Invoice into Individual Vender Order Forms

[Bigslim]

I want to create a system to take a Customer Order and Divide it up amongst the Vender in which they order their product from.

The only issue I am having is having to input my data twice. How can I have a Formula READ the Product Number and DISPLAY the Product Name in the appropriate order form for the Venders.

Customer Order Form
ABC-123 Product 1
DEF-456 Product 2
GHI-789 Product 3


Venders Order Forms

Vender ABC
Product 1
----------------
Vender DEF
Product 2
----------------
Vender GHI
Product 3



There are more chances than not that each vender will have more than one product being ordered through them.


My Objective is to Input the Info once and Display in the proper format that I need it. But if I have to input the info per vender I would like to than condense into a separate order form to the customer.

Any help would be greatly appreciated

 

[Akashwani]

Hi Bigslim,

Welcome to MrExcel.

I'm not sure if this is the solution you require, but it may give you some ideas.
If nothing else it has bumped you up.

Excel Workbook
	A	B
1	Customer	Product
2	Tim	Car
3	Stan	Bus
4	tom	Bike
5	Dom	Coach
6	Harry	tram
7	Sally	Train
8	Mary	Sledge
9	Jim	Tut Tut
10	Homer	Jet Ski
11	Tim	Bus
12	Stan	Plane
13	tom	Boat
14	Tim	Horse
15	Mary	Trike
16	Dan	Bike
Customer Order form

Excel Workbook
	A	B	C	D	E	F	G
1	Count	Vender	Product1	Product2	Product3	Product4	Product5
2	3	Tim	Car	Bus	Horse	*	*
3	2	Stan	Bus	Plane	*	*	*
4	2	tom	Bike	Boat	*	*	*
5	1	Dom	Coach	*	*	*	*
6	1	Harry	tram	*	*	*	*
7	1	Sally	Train	*	*	*	*
8	2	Mary	Sledge	Trike	*	*	*
9	1	Jim	Tut Tut	*	*	*	*
10	1	Homer	Jet Ski	*	*	*	*
11	1	Dan	Bike	*	*	*	*
Vendors Order Form

The formula in A2 needs to be copied down.
The formula in C2 needs to be entered using CTRL SHIFT ENTER not enter.
You can then copy it across and down as far as you like.

I hope this helps.

Good luck.

Ak

 

[Bigslim]

I don't think u have the idea I need to separate items on the customer order to individual vender orders but I need to Vlookup it seems but I have similar item codes

 

[Akashwani]

Hi Bigslim,

You are correct, I have no idea what you are trying to do here.

Can you post some more sample data and a clearer explanation of what you want to do?
Can you upload a sample of your file and post a link to it? Somewhere like here....4shared

Ak

 

[Bigslim]

unfortunately I am having issues loading things on here so I will just do the best i can with typing out a sample. I will add more detail to emphasize what I want.


Step 1 - Customer calls with an order from multiple venders, and as the person taking the order, I found it would be easiest and best if all they had to do was enter all the Customers order on 1 Form. This will end up being a customer receipt at delivery. So standard invoice is all i need for them.

But First and foremost I need to have a database of coded product. (I have already created this code system)

Database format

Item# Item Price


Code Definition
VenderCode ItemCode
ABC 123


Customer Order

Item # Item Price
ABC123 Prod-1 1
ABC234 Prod-2 2
ABC345 Prod-3 3
DEF123 Prod-4 4
DEF234 Prod-5 5

**This is just a simple Vlookup or Index & Match Formula

Step 2 - Now that I have the order I need to Setup the order to retrieve from the Venders. There is no need to have 1 Vender see what the customer is buying from another Vender. Plus, my code for the product is irrelevant to them so there is no need for that to be on the Vender Copy as well.

Vender Order

VenderABC

Item Price
Prod-1 1
Prod-2 2
Prod 3 3


VenderDEF

Item Price
Prod-4 4
Prod-5 5

**I have the price on the Vender Order Form in case of any price discrepancies.

So the idea I am looking excel to do is Look at the Item # and Match the Vender Code with the Vender and then List each individual product and price per line.

I am going to be playing Middle Man in a Sales Process and so I need to have consolidated order for the customer and individual orders for the venders.


I sure hope this makes a lot more sense. the code you gave me before I saw on youtube and was very interested in until I saw the outcome of it and it is not what I want for my code. Eventually for my database I would like to have a separate excel file, with 1 vender per worksheet, but right now I just want 1 long list in my database and figure out the code for this problem I am having.

I would like to say that your thoughts have been going down the right path but maybe you can unveil a fork in the road that I missed.

 

[Bigslim]

Thank you very much for the assistance with this problem. Great work, you definitely know what your doing in Excel.

 

[Akashwani]

Hi,

Thanks for the positive feed back.
I don't really know what I'm doing though
What little I do know, is down to the wonderful people on here that have been helping me for the last 2 years.

Good luck with your project.

Thanks

Ak

 

[Bigslim]

Ok similar problem. I have to create a program to randomize poker table seat.

I have a form which I have to fill out to list the players and it is set to tell u to tell the players what table they are at which is fine, but now I want to create a separate list for each table telling me who is at which table and be able to assign a specific seat to a player at the start of the game.


So my form has 2 list/columns of table numbers, because the form is in Landscape format, and some table numbers are in both list/columns.

Here is what I have


1 Joe 4 Jane
2 rich 2 nancy
3 harry 1 colleen
4 Mo 3 Kathy

Here is what i need to create from the lists above

Table 1 Table 2 Table 3 Table 4
Joe Rich Harry Mo
Colleen Nancy Kathy Jane

 

[Akashwani]

Hi,

It is best if you start a new thread explaining your new problem.
Make sure that you provide suitable sample data and an example of your expected result.

Good luck.

Ak