Trying to consolidate data from production schedule

Greetings

We have a production meeting every morning and new items are put on the schedule and completed items are taken off. We have 6 different packaging lines and 14 different production kettles I have attached an "Artist's Rendering" of the production schedule with just 3 lines, lines 4-6 would be exactly the same right below 1-3.

Column A has the kettle number and size merged and centered over 4 cells. What I am trying to accomplish is consolidating all of the information and adding the Kettle number and Line number in order to do some additional manipulation and do some material planning. I don't need the description since I have the item number. So what I need is Line No, Kettle No, 'New', Rank, Item, and Lot.

I have tried looping through the rows, but have not been able to have it work, and have just managed to get frustrated. I would appreciate any help or suggestions to point me in the right direction. I am pretty new to VBA, I know it can be done, I just don't have the wherewithal to make it happen (yet!).

Thanks!

Is this workbook what you ought to use or are you open to a new layout?
What surprise me is that you have no dates in your schedule just rank which I think means Priority.
I would rather see something more like a database. Let me know if you are interested.

Hello jacob@thepenpoint,

The attached workbook contains the macro below and a button on Sheet2 to run it. The ouput is across the page as it is on Sheet1. The macro can be changed to stack the Production Lines on top of each other if you would prefer.

Please Login or Register  to view this content.

Thanks Pierre, this isn't my layout, it is what I get from production every day and yes those are priorities. There are no dates because it is all dependent on how fast they can move the product through production and packaging. I am trying to get the data into a database so that I can determine my materials needs to make sure I have enough materials on hand to complete the job or if we need to bypass something until parts or supplies come in.

I'm always interested in new solutions. This site has amazed me with some of the ingenious and imaginative solutions I have seen posted.

Thanks Jarko28

I think this will probably do the job I am looking for. I will admit though that Array formulas are still a couple of steps outside my comfort zone, so I am not exactly sure what is going on with them although, I can't think of a better way to learn. Thanks!

Thanks to everyone who took their time to take a look at my problem! The people on this site are absolutely AWESOME!