Reformat Certain Data onto Another Sheet?

**stusic** · 11-02-2012, 10:52 AM

I'm wondering if there's a similar way I can take a thorn out of my side that's been bothering me for years.

I would like to find a way to go through a sheet and create a list of values based on values in that column. Column A has part numbers, Column E1, F1, G1, H1... are order numbers. Below the project numbers (starting from Row 5) are the quantities of parts used for that order.

I hate using the word, but a dynamic way where it's instantly updated would be ideal (i.e., no vba, no button to push).

I'm not really sure how to set this up in a formulaic fashion, but I've been thinking about how it would work. Here's what I've got so far:

The sheet "upload" would be where the list is created, always referencing the data on sheet "BOM".

Search Col A for "END". Mark it's row. This designates the end of the bill of materials (BOM) list.

E1 will always be the first order. Search F1 to O1 for the first blank cell. The cell immediately before this will be the last order entered in the bill.

Starting with E1, search down the column from E6 to the row where "END" was found.
For each value that is >0:
a. Copy the order number (E1) to sheet "upload", Col A
b. Copy the contents of the cell in Col A of the same row to sheet "upload", Col B
c. Copy the contents of the cell in Col E of that row (the >0 value) to sheet "upload", Col C
d. Copy the contents of the cell in Col D of that row to sheet "upload", Col D

When it has finished searching through Col E (from Step 2), repeat for the additional columns calculated in Step 3, substituting Col "E" in step 6a and 8c.

Easy-peesy, huh? Lol, I can see what I need to do, but how to do it is something else.

If it's helpful, I've uploaded a previous example to dropbox that may be helpful in seeing what I have to do: http://dl.dropbox.com/u/24293397/212185-BOM.xlsm

Thanks for any help all

**6StringJazzer** · 11-03-2012, 11:33 PM

I do not know if this can be done with formulas. Doing it with multiple orders seems very difficult if it can be done at all, although I have seen wizardry with formulas on this forum. However, I am sure it can be done with VBA, which you seem to be ruling out. Would you be opposed to VBA that would run automatically when there are changes made to the BOM without the user having to push a button? My concern is that it might be slow enough to disrupt the user's ability to enter data smoothly. If there were a way to know when a new order is completely entered it could be done once on completion, but it doesn't seem so, so it would have to be updated every time a new part quantity is entered.

**stusic** · 11-05-2012, 09:17 AM

Originally Posted by 6StringJazzer

I do not know if this can be done with formulas. Doing it with multiple orders seems very difficult if it can be done at all, although I have seen wizardry with formulas on this forum.

Yeah, I had originally thought it couldn't be done, but after seeing some of that wizardry here, I thought it might actually be possible. My thought was to take that formula and re-purpose it to gather all the parts for the orders in their own column, then combine all the orders into one long list. I've gotten formulas in there to find the end of the BOM, to find the number of orders on the BOM, and to find how many items are in each order. Just can't figure out how to retrieve the part number, qty, etc. from each order. Seems possible...

Originally Posted by 6StringJazzer

However, I am sure it can be done with VBA, which you seem to be ruling out. Would you be opposed to VBA that would run automatically when there are changes made to the BOM without the user having to push a button?

I'm not necessarily ruling it out, but it would need to be automatic and transparent. If I've got to have the user press a button, it won't fly. I've never seen any VBA that doesn't involve a button of some sort... if it can be embedded in the file, then I don't see why it wouldn't work.

Originally Posted by 6StringJazzer

My concern is that it might be slow enough to disrupt the user's ability to enter data smoothly. If there were a way to know when a new order is completely entered it could be done once on completion, but it doesn't seem so, so it would have to be updated every time a new part quantity is entered.

The BOM sheet changes all the time (adding or removing parts, changing the quantity of parts). Although the upload sheet is only used once they're done making their changes. If it doesn't intrude on their ability to enter data in the BOM, then a delay is not such a big issue.

Thanks for your help Stringer,

-stu

**6StringJazzer** · 11-06-2012, 01:48 PM

Originally Posted by stusic

I'm not necessarily ruling it out, but it would need to be automatic and transparent. If I've got to have the user press a button, it won't fly. I've never seen any VBA that doesn't involve a button of some sort... if it can be embedded in the file, then I don't see why it wouldn't work.

The technique for this is straightforward. You can write an event handler Worksheet_Change that will run any time the worksheet changes. However, the VBA I have written to generate your list takes much too long to run to do this every time another part is entered, something like a minute. So I don't have a solution for you. My intuition is that it shouldn't take that long to run; it's just scanning the cells and copying the ones with a quantity>0. If I figure out a faster algorithm I'll be back.

**6StringJazzer** · 11-06-2012, 03:50 PM

I have started another thread to seek help for the performance issue. When I run it by hitting F5 from the code window, it runs in under a second, but when it's run by the Worksheet_Change event it takes about 90 seconds.

**6StringJazzer** · 11-07-2012, 12:08 AM

In that other thread, one member said it ran ~1 second in either method, so I post it here for you to test. [stusic=macros to automatically update form=212185-BOM.xlsm]