VB Macro for counting data & output result to spreadsheet

[Red fuji]

Hello Excel gurus-
thanks for reading-i hope it is clear-i did my best; please let me know if it is not.
I have a matrix with a mix of various data that I would like to count and output to a spreadsheet. it is a bit of a challenge-way beyond me.
The data consists of Shelves, Boxes and Books. The shelves hold boxes which hold books. The books and boxes need to be counted and populated to a spreadsheet.
I have an Excel file with two tabs; first tab has the quantities of BOOKS that go in each BOX, the quantity of BOXES on each SHELF, and condition of the BOOKS . Second tab has the tabulated count.
I am looking for a Microsoft VB that would give me the count in a specific format shown in tab two? I can pre-populate the headings manually; but need VB code to start at second row and on.
I know it is a ton of work and a challenge –Thanks Much in advance for volunteering your time and effort. Thanks!

please dont recommend that i do this different. i know some of you probably have better ways of displaying the resut and so forth and i know some of the data is repeated over to the result tab-that is ok . i just have to do it this way for certain reasons that i wont get into to keep this short thanks and sorry.

one note to add if it helps:
In the matrix data-you will notice that some of the books don’t have a quantity and doesnt specify which box they go into. These books do not get added to the count even though they have a BOOK STATUS i.e, Bad, Good etc. Only books having quantity are consider. sorry for this ugly and complicated mess.

[Red fuji]

help anyone

[thameem127]

Its very confusing. Please upload file with clear expectations.

Thanks

Thameem

[watersev]

hi Red fuji, I did not understand what data will be present on the result already sheet but if counts are the only missing data then it could be done this way, press Run button. I've noticed that some results of yours were wrong to my understanding. Please check attachment.

[Red fuji]

watersev,
Thanks but this is some of the needed data. I actually need everything you see in the second tab (exept the heading; i can populate those manualy and save the file then n run the macro)

Thameem- Here is a simplified version of the data. I split the raw data into two sections for you see things clearly. One table for the Books-to-Boxes and other table is for Boxes-to-Shelves. (Although originally the data is combined. I hope this makes it a little easier to see. Thanks

[watersev]

please check attachment, press Run

[Red fuji]

please check attachment, press Run

works great ! thank you !!!

now i am trying to learn each and every line of the code in order to translate it to different work. being a VBA novice, I am having a hard time comprehending the code.
MIght be too much to ask- but do you think it can be written in a more simple way for idiot like me to grasp- it doesnt matter if it gets longer just want it to be as intuitive as possible for easy revising. dynamics such as changing data, columns and rows and additinal data and such. the core VBA will stay intact-just need to match it up to changes. thanks again.

[watersev]

I would not say that the whole thing is very complicated to catch. I've added comments to the code to make it easier to understand, hope that helps.

Please Login or Register  to view this content.

[Red fuji]

what if the PART NUMBER is a random set of characters - It does not necessarly start with the word 'BOX' (Code line 15)
and if i add another part number to the list- how and which line of code would i revise to reflect that change?
Thanks!

Please Login or Register  to view this content.

[watersev]

if Description column has data for every PartNumber this could be used instead:

Please Login or Register  to view this content.

[watersev]

please check attachment, the code have been updated

It is assumed that:
1. Description field have data for each PartNumber
2. PartNumber stays 2 columns to the right of Description field

Try to add/remove items in Description, PartNumber horizontal and vertical.