Difficult but not impossible

[decent_boy]

Hello, hope that you are well

I need a macro for my result sheet , let me explain you that i have sheet1 and there are 3 fields from col a to c and i need a macro for result sheet which matches ID number from result sheet col a (where DI# is blank in col b) with sheet1 col a and copy/distribute b col value from sheet1 to result sheet in col c which must be equal d col value , ok please see below example in red highlighted font cells as a macro result.

Sheet1
ID	EDI #	Qty
A11	A1245	2
A11	A1345	1
A11	A1545	10
A16	B4567	3
A16	B6789	30

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

Result Sheet
ID	DI#	EDI #	Qty
A11	-	A1245	2
A11	-	A1345	1
A11	-	A1545	1
A12	2345		1
A13	3245		1
A16	-	B4567	3
A16	-	B6789	2
A16	-	B6789	1

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[MARK858]

Ignore what I did have written here as that wouldn't make any sense with the next ID's

 

[decent_boy]

Rick what you need to know more please ask I will explain but I give short example here suppose in sheet 1 in col a there are different and same name fruits and in column b their unique serial number and in column c fruits quantity
You are told to distribute these fruits in result sheet as per conditions.

1- Match fruit name with sheet

2- Where is DI # number is blank in result sheet

3-Give fruit (means copy EID #) from sheet 1 to result sheet as per given quantity of result sheet
once matching fruit quantity is ended in sheet 1 then go to next matching fruit name and copy data from their

 

[decent_boy]

Would you please help i know someone can do this

 

[Rick Rothstein]

Rick what you need to know more please ask I will explain but I give short example here suppose in sheet 1 in col a there are different and same name fruits and in column b their unique serial number and in column c fruits quantity
You are told to distribute these fruits in result sheet as per conditions.

1- Match fruit name with sheet

2- Where is DI # number is blank in result sheet

3-Give fruit (means copy EID #) from sheet 1 to result sheet as per given quantity of result sheet
once matching fruit quantity is ended in sheet 1 then go to next matching fruit name and copy data from their

I still do not understand how the 10 and the 30 became 1 and either 2 or 1 depending on what the mistake in the repeat of B6789 actually was.

 

[decent_boy]

I still do not understand how the 10 and the 30 became 1 and either 2 or 1 depending on what the mistake in the repeat of B6789 actually was.

First I would like to tell you B6789 is a typo mistake which i have removed in below data and second thing is we are not copying/distributing Qty from sheet1 to result sheet but we have to only copy EDI # from sheet1 to result sheet by matching their ID and remaining conditions as mentioned above post.

Sheet1
ID	EDI #	Qty
A11	A1245	2
A11	A1345	1
A11	A1545	10
A16	B4567	3
A16	B6789	30

<tbody>
</tbody>

Result Sheet
ID	DI#	EDI #	Qty
A11	-	A1245	2
A11	-	A1345	1
A11	-	A1545	1
A12	2345		1
A13	3245		1
A16	-	B4567	3
A16	-	B6789	2

<tbody>
</tbody>

 

[decent_boy]

more explanation there are total 13 qty against ID A11 having different EDI # in sheet1 but result is demanding 4 qty against ID A11 so we distribute data like below

ID	DI#	EDI #	Qty
A11	-	A1245	2
A11	-	A1345	1
A11	-	A1545	1

<tbody>
</tbody>

 

[Rick Rothstein]

more explanation there are total 13 qty against ID A11 having different EDI # in sheet1 but result is demanding 4 qty against ID A11 so we distribute data like below

ID	DI#	EDI #	Qty
A11	-	A1245	2
A11	-	A1345	1
A11	-	A1545	1

<tbody>
</tbody>

I think I see what you want now. Question... would it ever be possible for the quantity to be more than a single EDI# has available as shown in Sheet1? By that, I mean could the table look like this and, if so, how would the EDI#'s fill in?

ID	DI#	EDI #	Qty
A11	-		4
A11	-		3
A11	-		2

<tbody>
</tbody>

 

[decent_boy]

Rick sheet1 has always more quantity than result sheet and qty in result is always equal or less than sheet1 qty

 

[decent_boy]

Rick sheet1 has always more quantity than result sheet and qty in result is always equal or less than sheet1 qty

My one more quest is that is that possible that macro check qty count of result sheet and if demanding qty is greater in starting count and it goes down and check another matching qty if it is equal or greater like in above you case i solve it like that

Result Sheet
ID	DI#	EDI #	Qty
A11	-	A1545	4
A11	-	A1545	3
A11	-	A1545	2
A11		A1245	2

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

 

[decent_boy]

My one more quest is that is that possible that macro check qty count of result sheet and if demanding qty is greater in starting count and it goes down and check another matching qty if it is equal or greater like in above you case i solve it like that

Result Sheet
ID	DI#	EDI #	Qty
A11	-	A1545	4
A11	-	A1545	3
A11	-	A1545	2
A11		A1245	2

<tbody>
</tbody>

Can anybody please solve it