Extracting Equipment List from Quoting Tool

[Hollow94]

Hello all!

First post and pretty inexperienced Excel user (however I've really enjoyed tackling this problem, but now im just stuck and need help).

I want to create a tool that will extract data from various supplier part sheets (on different sheets in the same workbook) that I use to cost and quote project. Then present the items I specify for a job in one clean equipment list on a new sheet. The data I would like on this sheet is Part Code, Item Description, Quantity, Price (me), Price (customer). I then know from there how to omit or add columns depending on if Im giving it to a client or my accountant.
I would like the table to be automated so it'll remove lines when i've removed the quantity in the manufacturer sheet, not just leave it on a zero amount in the equipment list.

I've tried using IF statements and Match/Index functions but it isn't generating the results I need (I'm missing a key part of it).

Any advice would be greatly appreciated as I have exhausted my MS knowledge and youtube isn't helping me much. I have attached part of the workbook below, please ignore the accounts details and ridiculous figures being thrown around.

Thank you!

[dflak]

This will take some VB but isn't a major project.

First of all we need to confirm some things:
- You want the "answer" to be just 4 column: Part Code, Item Description, Price (me), Price (customer) - could you explain what columns on the source sheets contain the two prices? Would there be any value to adding the company name (sheet name) as another column?
- If the quantity is zero, don't record it.
- It looks like the information on the source sheets is in different columns but always has the same column header name.
- There seems to be a number of sheets in the workbook that do not have the kind of data you are looking for. Is there a way to determine what the source sheets are, or will you need to enumerate them somewhere? For example, can I depend on opening a sheet and looking at cell A1 to see if it contains the word "Item?"

[Hollow94]

Hey dflak,

I want the answer to list, Part Code, Item Description, Price (me), Price (customer) for any item that has a quantity equal to or greater than 1. I want all that data to be on one sheet 'equipment list'.


Would there be any value to adding the company name (sheet name) as another column?
For example where sheet name 'ruckus' is, should also have it's own column within the sheetPerhaps called 'Manufacturer'.


There seems to be a number of sheets in the workbook that do not have the kind of data you are looking for. Is there a way to determine what the source sheets are, or will you need to enumerate them somewhere? For example, can I depend on opening a sheet and looking at cell A1 to see if it contains the word "Item?"
I think i understand what you mean here. I'll want to read data from any manufacturer product price sheet, so in the attached example that would be sheets 'sonance' 'sonos' 'ruckus' 'projector screens'. Once the full workbook would contain near 20-25 sheets that i would need data from.

I've attached a new and perhaps clearer example of the original workbook.

Hope this is helpful, thanks again.

[dflak]

Trailing blanks on sheet names, trailing blanks on field names, merged rows, etc. Who is producing this?

Before anything can be done on the workbook. The data has to be cleaned up. So I will add that to the code.

[dflak]

I'm giving up on this thread. The data is too dirty. Even when I copy and paste the column headers into the code, the code still can't find them.

Maybe someone else will have better luck than I did.

[Hollow94]

yeah you're right it is a mess. I ditched that one over the weekend and started fresh with a limited data set after you mentioned VB (something i've never used).

I've figured out how to extract a row of data from a sheet and send it over to an 'equipment list'

However the problem i am now facing is that if i press the Inserted Macro button multiple times, I'll get to data duplicated. Is there a way to safeguard against this?

Thanks for your help, and patience.

[dflak]

Yes, there are ways to doing this. It would involve marking the records you have already added and excluding them from the copy.

Which brings up a question. On the Sonos sheet, do you intend on adding new information and keeping the old? In other words, if I click on the button, you want all through row 20 added. If I then add a row 21 and then click on the button, you want just row 21 added. Please confirm.

I will also comment on the code. It makes sense and it works, however, it could be made more efficient.

Generally speaking, things go faster it you copy whole ranges rather than loop trough and copy line by line. However, I might stick with the looping since you want two things: don't copy lines already processed and don't copy lines with no quantity. If you were dealing with huge quantities of data, I would consider filtering the data and then copying the visible range wholesale, but since we are not dealing with tens or hundreds of thousands of rows of data, this might actually take more time.

The second comment I have on the code is that there is usually no good reason to have to activate and select things. There are times when it makes sense, but generally not. The most efficient way to "switch" back between sheets is to set a "pointer" to them and references things on the sheets using the pointer. I will have an example in the code below.

The third comment I have is that you have the code on a sheet module. This means you will have to duplicate (and as you have it written) edit this code for each sheet. I suggest moving the code to a regular module and making it more "generic."

There is nothing wrong with the code you have written, however I will provide alternate code to demonstrate what I pointed out above.

Please Login or Register  to view this content.

My preference is to use the form controls rather than the active X controls. Put a button on each of the source sheets and assign this macro to it.