Taking a list, building a total.

[Cyberpawz]

Ok, the title really can't be helped, this is not an easy one.

I have this spreadsheet that goes on for over 500,000 rows just like how you see in the sheet attached. It is designed via an export through a program called Fishbowl.

Now here is the problem, whoever designed this export made it so that only fishbowl could read the data and make real sense of it. I want to design something that will allow excel to read specific aspects of it namely the ability to compile data based on the sales spreadsheet.

There are two tabs, the first is original data, the second is what I'd like to see.

Basically I want to make it so that it searches for the SO and gathers the data on it's lines if possible, as shown on what I'd like to see, and then add up any row of items value together and put it one individual row.

The problem I am running into is I can't figure out how to make it so that when the calculation sees the SO it knows to start a new row, and add all the item prices together, and to stop when the next SO line shows up, or am I barking up the wrong tree?

[JP Romano]

If the column names that you want to keep are consistent, you could delete all but the desired columns, then do a text filter on rows which do NOT contain "SO" - or maybe I'm oversimplifying?

[Cyberpawz]

The vertical rows are consistent, the problem is the horizonal rows aren't.

[carsto]

with consistent column headings use this code:

Please Login or Register  to view this content.

[JP Romano]

Another approach - this one loops through the columns to remove the ones you don't want based on their headers. Just another option for you!

Please Login or Register  to view this content.

[Cyberpawz]

I hate to ask this, but how do I enter this in? Also, the problem I am seeing is that it says to delete all non SO rows, but I don't want to delete all non SO rows. Only because between the SO rows are the Items, and those prices need to be combined to create one SO line. As shown on the second tab on the sheet I provided.

[Cyberpawz]

with consistent column headings use this code:

Please Login or Register  to view this content.

I hate to ask this, but how do I enter this in? Also, the problem I am seeing is that it says to delete all non SO rows, but I don't want to delete all non SO rows. Only because between the SO rows are the Items, and those prices need to be combined to create one SO line. As shown on the second tab on the sheet I provided.

[carsto]

I see you’re using 2007 or 2010 but these instructions are for 2003, hopefully you can figure it out. (Sorry)

To open the visual basic editor press alt+F11, or under Tools > Macro > Visual Basic Editor from the menu.

Project Explorer window should be on the left side (looks kind of like Windows Explorer) and lists all of the open workbooks (and probably one called Personal). If not, press Ctrl-R or select Project Explorer from the View menu.

Select the Workbook you are in and Insert > Module
Assuming you have no other code in this workbook Module1 will appear under your workbook tree. (It will be selected, but click on it to be certain.) Copy & Paste the code into the frame on the right side, which will be blank with two drop down menus above it displaying (General) and (Declarations).

The reason it deletes all rows without “SO” is because it has already added the item lines up and inserted the total into the SO row. See the comment 'add the preceding values and plug them into Col D.

edit: this macro doesn't leave you with 2 sheets it alters the original sheet to look like what you want. perhaps I misunderstood and you truly want 2 sheets a details and a summary?

[JP Romano]

I can see that you're in good hands with Castro - I'll keep an eye on the thread in case I can help though....

[Cyberpawz]

I see you’re using 2007 or 2010 but these instructions are for 2003, hopefully you can figure it out. (Sorry)

To open the visual basic editor press alt+F11, or under Tools > Macro > Visual Basic Editor from the menu.

Project Explorer window should be on the left side (looks kind of like Windows Explorer) and lists all of the open workbooks (and probably one called Personal). If not, press Ctrl-R or select Project Explorer from the View menu.

Select the Workbook you are in and Insert > Module
Assuming you have no other code in this workbook Module1 will appear under your workbook tree. (It will be selected, but click on it to be certain.) Copy & Paste the code into the frame on the right side, which will be blank with two drop down menus above it displaying (General) and (Declarations).

The reason it deletes all rows without “SO” is because it has already added the item lines up and inserted the total into the SO row. See the comment 'add the preceding values and plug them into Col D.

edit: this macro doesn't leave you with 2 sheets it alters the original sheet to look like what you want. perhaps I misunderstood and you truly want 2 sheets a details and a summary?

Yes, regretfully I need two tabs, not one tab... because this is probably going to be handed off to another use, who will not have VB on their machine, nor will the company allow it. Long story.

[carsto]

Without using VB I couldn't tell you to do this easily!

Excel has Visual Basic for Applications built into it, so if you have Excel you have VBA, you need only to set your macro security to allow you to use them and then "show the developer tab in the ribbon". It's a little harder to get to in XL2007-2010, it's in that round window button top left I think under Excel Options. (sorry, I don't use 2007 at work!)

If you can run the macro on your computer then pass the saved file off, I will happily modify it to make two sheets per you wishes. Please advise

Have you tried to paste my macro into your VB editor and run it?

[Cyberpawz]

No, only because I don't want to have it where if I leave this job the ability dies with me, I'm not the network admin, but I'm the most technologically advanced user here. I know sounds counter intuitive for keeping a job but I tend to make it so I have to come back to fix things even after I left a long time ago.

I'm using 2010 myself, and 2011 at home (mac version) Or either LibreOffice, or Open Office on my Ubuntu laptop