Template
Database (FYI Column C is just a sum of the five channels - alpha, beta, theta, delta, gamma)
Hello everyone,
I hope you all had a great holidays and are off to a great start on 2015! I would appreciate any help or direction to lead me to my goal. I have tried to do some googling but I haven't found quite what I need. This might require a macro, something I have very little knowledge on. This website has been a great resource over the years for me, and I appreciate anyone who tries to help me out here.
So my brother has started his own business and has been using the "TEMPLATE" to create orders for his company. Every single order is a separate Excel file based on the "TEMPLATE" file. Let's say there are 35 orders so there are 35 Excel files just like "TEMPLATE". I want to combine the data of these 35 files into a single table ("DATABASE").
I have highlighted the three areas of interest in the sheet in yellow. I pretty much want to collect the Quantity/Product/Size out of every Excel file (Rows 32:46). A product is only ordered if the quantity column is filled with a number. I only need to collect the rows of data that have a value in the quantity field. Each Excel file may contain the same or different products listed in these rows (32:46). If it is the same product (PRODUCT/SIZE), then I want them added together if it is in the same channel (Ie. Two seperate West Coast forms that both have three LARGE Rubber Ducky's in the Alpha Channel would show 6 in the DATABASE under the West Coast/Rubber Ducky/Large/Alpha.
Possible Values for the three areas of interest:
Coast Name: (Written at top/middle of TEMPLATE)
West Coast
East Coast
Channel: (Designated by "X" in corresponding box on TEMPLATE)
Alpha
Beta
Theta
Delta
Gamma
I hope I have explained this clearly. My database only shows the tabulation of one form for the West Coast. The East Coast would have to be a separate table or something similar to distinguish the two regions. The database is a place that adds together the combined quantities of all ordered products (Product/Size), separated by the Channel and Region. The way the database looks doesn't matter. It could be a long database looking list, that has columns (Coast, Product, Size, Channel & The most Important Value: Quantity Ordered). I just need the data out of all the forms without doing it manually!
In order to simplify things if it makes it easier for anyone trying to help, I could create a folder called West Coast and a Folder Called East Coast that separates these two region's Excel order forms. Our Macro would only then have to collect the Qty/Product/Size per Channel. I could run this process twice for each Coast. Another simplification is if we combined PRODUCT/SIZE into one field. I could add the size descriptor the product name so product could be: RUBBER DUCKY LARGE and RUBBER DUCKY SMALL. If that makes the creation of a macro easier, I am all for it.
Please let me know if I can clarify or assist with anything. Any guidance or assistance is greatly appreciate by my brother & I.
Thank you so much for reading,
Database (FYI Column C is just a sum of the five channels - alpha, beta, theta, delta, gamma)
Hello everyone,
I hope you all had a great holidays and are off to a great start on 2015! I would appreciate any help or direction to lead me to my goal. I have tried to do some googling but I haven't found quite what I need. This might require a macro, something I have very little knowledge on. This website has been a great resource over the years for me, and I appreciate anyone who tries to help me out here.
So my brother has started his own business and has been using the "TEMPLATE" to create orders for his company. Every single order is a separate Excel file based on the "TEMPLATE" file. Let's say there are 35 orders so there are 35 Excel files just like "TEMPLATE". I want to combine the data of these 35 files into a single table ("DATABASE").
I have highlighted the three areas of interest in the sheet in yellow. I pretty much want to collect the Quantity/Product/Size out of every Excel file (Rows 32:46). A product is only ordered if the quantity column is filled with a number. I only need to collect the rows of data that have a value in the quantity field. Each Excel file may contain the same or different products listed in these rows (32:46). If it is the same product (PRODUCT/SIZE), then I want them added together if it is in the same channel (Ie. Two seperate West Coast forms that both have three LARGE Rubber Ducky's in the Alpha Channel would show 6 in the DATABASE under the West Coast/Rubber Ducky/Large/Alpha.
Possible Values for the three areas of interest:
Coast Name: (Written at top/middle of TEMPLATE)
West Coast
East Coast
Channel: (Designated by "X" in corresponding box on TEMPLATE)
Alpha
Beta
Theta
Delta
Gamma
I hope I have explained this clearly. My database only shows the tabulation of one form for the West Coast. The East Coast would have to be a separate table or something similar to distinguish the two regions. The database is a place that adds together the combined quantities of all ordered products (Product/Size), separated by the Channel and Region. The way the database looks doesn't matter. It could be a long database looking list, that has columns (Coast, Product, Size, Channel & The most Important Value: Quantity Ordered). I just need the data out of all the forms without doing it manually!
In order to simplify things if it makes it easier for anyone trying to help, I could create a folder called West Coast and a Folder Called East Coast that separates these two region's Excel order forms. Our Macro would only then have to collect the Qty/Product/Size per Channel. I could run this process twice for each Coast. Another simplification is if we combined PRODUCT/SIZE into one field. I could add the size descriptor the product name so product could be: RUBBER DUCKY LARGE and RUBBER DUCKY SMALL. If that makes the creation of a macro easier, I am all for it.
Please let me know if I can clarify or assist with anything. Any guidance or assistance is greatly appreciate by my brother & I.
Thank you so much for reading,
Last edited:
