Loop, match and return values

Hi all,

Assume you hold your stock in several external warehouses. They obviously hold stocks for other companies too. Every day you receive stocklists from all of them. The aim is to run a report that would only include your stock. So, basically if you know what stock you hold in which warehouse, it would be looping through all stocklists, matching your product and delivering qty, warehouse ID and best before date to a separate sheet. Sounds simple, but I struggle to get this laid out on VB editor...

Can someone please look at the attached dummy, where I have also replicated the desired result for you to understand what am I trying to achieve.

Thanks for your priceless help.

Forgot to mention there are 4 warehouses we receive stocklists from. On a dummy it only shows 2. My bad...

How about

Please Login or Register  to view this content.

Hi Fluff13 and thanks for response.

I should have mentioned this at the beginning, that all stocklists are in completely different layout. I thought the solution will be very simple For loop, which I will be able to adapt (changing sheet names, ranges etc.). Apparently your code is beyond my comprehension, but works with dummy spreadsheet. I won't be able to adapt this code for original spreadsheet.

Apologies for all.

Originally Posted by ABSTRAKTUS

I should have mentioned this at the beginning, that all stocklists are in completely different layout.

In that case you will need to supply a workbook showing your actual data layout, not a simplified version.

I shall describe the layout as it truly is. There are 4 sheets for stocklists named:

1. ASHINGTON (product in col G, quantity in col M, best before in col P)
2. OPTIMA (product in col A, quantity in col E, best before in col C)
3. OAKLANDS (product in col P, quantity in col C, best before in col D)
4. GILLINGHAM (product in col C, quantity in col H, best before in col L)

I hope this will clarify a lot... Sorry again.

I have attached the workbook that code really needs to go on...

As I do not have the time to "Mock-up" a sample workbook, you will need to do that.

Can't adapt either code...

How about

Please Login or Register  to view this content.

Speechless... Solved!

Glad we could help & thanks for the feedback

Hello again, just a quick advise I'm after. What if I want to add additional fields to 4 I already have, what part of code do I change?

Thanks again!

You would need to change the select case statements

Please Login or Register  to view this content.

This would add the description in Col B (ie column 2)
And then Change the value in red here

Please Login or Register  to view this content.

To reflect the number of columns in the output.
Finally add new rows here as shown in red

Please Login or Register  to view this content.