Distribute/Copy by Matching Multiple Conditions

hi All
I posted a question on below link but nobody has provided the solution of it can anybody here is able to solve it it is become a challenge for me. kindly read all posts to understand all scenarios

http://www.mrexcel.com/forum/excel-q...mpossible.html

Thanks for providing cross post link.
However, me neither doesn't see a logic.

Can you explain step by step and give some more info:
like, why is in Sheet 1: A11 A1545 10
and in sheet 2: A11 - A1545 1

Also, why does A12 appear on sheet2 when it's missing on sheet1:

A12 2345 1

Originally Posted by zbor

Thanks for providing cross post link.
However, me neither doesn't see a logic.

Can you explain step by step and give some more info:
like, why is in Sheet 1: A11 A1545 10
and in sheet 2: A11 - A1545 1

Also, why does A12 appear on sheet2 when it's missing on sheet1:

A12 2345 1

A12 appears in sheets because it has already DI# number and my first condition is that macro search and distribute/copy by matching those ID where DI# is blank or "-" in col b in result sheet
Sheet1 is source data however result sheet2 is extracting EDI#

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

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


one more thing is 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

I will attach sheet if required

Sure, that would be much easier.

please find enclosed workbook for your kind reference

please find enclosed workbook for your kind reference

please find enclosed work book as a example

Here, try this formula:

Formula:

Please Login or Register  to view this content.

confirmed with ctrl+shift+enter (not just enter)

Thanks for your replay but the result is not correct in first starting row in result sheet please see below points

The EDI # A1245 through formula is not correct because in result sheet order request qty in first match condition is 8 however in sheet1 available qty is 2 against EDI # A1245 so you can not give EDI A1245 qty because it is not equal or greater

Result through formula
ID DI# EDI # Qty
A11 - A1245 8

Correct Result as given in my last attachment
ID DI# EDI # Qty
A11 - A1545 8
A11 - A1345 1
A11 - A1545 2
A12 2345 1
A13 3245 1
A16 - B4567 3
A16 - B6789 2

Further can it be done through macro as i have data in number of rows

Data in before and after is same.
Can you place proper values?

Sorry I don't understand what you meant

will you please explain it more ?

I am enclosing another example workbook for your further understanding, kindly look it.

I think I got.
If yes you have wrong result in your example:

Please Login or Register  to view this content.

Also, what happend with original data (Fruit Data Base) once that quantities are used?
Right now it doesn't do anything but can be changed to adopt with new values.

zbor thanks you very for reply and solution provided by you I would like to tell you that data (Fruit Data Base) once that any fruit quantity is used/ended, code show error that "All available quantity of Apple is used.

Below is error in yellow highlighted result after running macro.Total Available qty of apple is 13 but distribution is 20 shown macro result.

test.jpg

can you upload qorkbook with new inputs for above picture?

Also, maybe you didn't understand me...
You have data like this:

Please Login or Register  to view this content.

Now, when you run macro... You get some result on Fruit Order List.
What will happend with above data? Do they need to reduce based on Fruit Order List or remain the same?

I think I found an error.
If not please upload example workbook.

Note: After running macro you'll get ! where it has issuficient qty.

You are great zbor, brilliant and I am really thankful to you for this.

Glad I've helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Dear zbor

Hope that you are fine , i am facing an issue in your provided code , when i run this macro on 50,000 rows data, excel is hanged and going to not responding.

Any update please

Ah, yes.. I thought this might be problem because I wanted to use MATCH but couldn't get it work so it does few loops through all data and it might get slow.
I must check why it didn't work in a first place.

Hope that you have found some solution if it

Please check is this faster.

zbor thanks for your reply but result is same and excel is hanged and gone to not responding, code arent working on 2000 r ows or more rows

Can you upload your workbook?
Make sure to mask personal data but leave similliar formatting.