Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strategy Needed
I've been asked to do some fairly detailed reporting quarterly for a
customer, and told that I need to do it in Excel. I understand many of the functions for sorting, filtering, macros, etc., but am not sure how to set this up. Here's what I need to do. 1. Download all transactions from an AS400 database into Excel (Data/Import External Data... it works, I've done it.) This give me a list of every line item purchased for a certain time period and all related cost information from all purchase orders. 2. Separate that data into different product groups. Sometimes this is fairly straightforward, somes it's not. The product line is not identified in the AS400 database, only the model #. For example, if the model # begins with a "T", it's "Terrace" product. This is the part that's causing me the most trouble. There are many model numbers that are not easily recognizable as to which product group they belong to. (I'll come back to this below.) 3. Provide total dollar volume for each product group, and sometimes quantity of items purchased. (This isn't a problem once I figure out what model number goes where.) Step 2 is where I'm getting hung up. I'd like to figure out if there's a way to begin a parts list that identifies what product line a certain part belongs to. I can start this with the first report, and build on it each time a new part shows up on the report that I don't know. I probably have about 80% figured out at this point, but with over 6000 lines each quarter, this still takes a lot of time to figure out the last 20%. Should I create a worksheet that has columns for each product line, and just a list of model numbers that fit into each product line? Then use the lookup function when I need to find a certain model number? I'd like to then create a macros to automatically find the product line for each model #, but I don't know where that goes or how to create it. I've created some basic macros... at this point I'm looking for a strategy as to how to set this up. I can then try and work through the details, and may have more questions, but I'm not sure that what I described above will work. My knowledge level is somewhat above average, but far from expert! Thanks for any help or suggestions anyone mighit have as to how to do this in Excel. KK |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strategy Needed
I definitely wouldn't want separate tables for each product line (PL).
Instead, I'd create a single table, with the p/n in first column and the PL in the second. Then translating from pn to PL is a vlookup (something like =vlookup(pn,tablesheet!A:B,2,false). How prevelant is the arrangement where the first letter indicates the PL? If it's common, I'd introduce a second table that has the first letter in column A and the associated PL in column B. Then your equation becomes: =if(isna(vlookup(left(pn,1),first_letter_table_she et!A:B,2,false)),vlookup(pn,tablesheet!A:B,2,false ),vlookup(left(pn,1),first_letter_table_sheet!A:B, 2,false)) This would keep you from having to identify each new pn that comes in where the first letter is adequate. But you would want to keep an eye on the new p/ns to ensure that the 'first letter rule' still applies. --Bruce "kleivakat" wrote: I've been asked to do some fairly detailed reporting quarterly for a customer, and told that I need to do it in Excel. I understand many of the functions for sorting, filtering, macros, etc., but am not sure how to set this up. Here's what I need to do. 1. Download all transactions from an AS400 database into Excel (Data/Import External Data... it works, I've done it.) This give me a list of every line item purchased for a certain time period and all related cost information from all purchase orders. 2. Separate that data into different product groups. Sometimes this is fairly straightforward, somes it's not. The product line is not identified in the AS400 database, only the model #. For example, if the model # begins with a "T", it's "Terrace" product. This is the part that's causing me the most trouble. There are many model numbers that are not easily recognizable as to which product group they belong to. (I'll come back to this below.) 3. Provide total dollar volume for each product group, and sometimes quantity of items purchased. (This isn't a problem once I figure out what model number goes where.) Step 2 is where I'm getting hung up. I'd like to figure out if there's a way to begin a parts list that identifies what product line a certain part belongs to. I can start this with the first report, and build on it each time a new part shows up on the report that I don't know. I probably have about 80% figured out at this point, but with over 6000 lines each quarter, this still takes a lot of time to figure out the last 20%. Should I create a worksheet that has columns for each product line, and just a list of model numbers that fit into each product line? Then use the lookup function when I need to find a certain model number? I'd like to then create a macros to automatically find the product line for each model #, but I don't know where that goes or how to create it. I've created some basic macros... at this point I'm looking for a strategy as to how to set this up. I can then try and work through the details, and may have more questions, but I'm not sure that what I described above will work. My knowledge level is somewhat above average, but far from expert! Thanks for any help or suggestions anyone mighit have as to how to do this in Excel. KK |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strategy Needed
Bruce--
It took me a couple of days to get to this, but I just finished building a separate worksheet showing on Product Numbers and Product Lines. I will add to it as new Product numbers show up. I used vlookup to find Product Line (based on having Product Model #) on the other worksheet with all product listed, and it worked. Thanks for the tip! Kathy "bpeltzer" wrote: I definitely wouldn't want separate tables for each product line (PL). Instead, I'd create a single table, with the p/n in first column and the PL in the second. Then translating from pn to PL is a vlookup (something like =vlookup(pn,tablesheet!A:B,2,false). How prevelant is the arrangement where the first letter indicates the PL? If it's common, I'd introduce a second table that has the first letter in column A and the associated PL in column B. Then your equation becomes: =if(isna(vlookup(left(pn,1),first_letter_table_she et!A:B,2,false)),vlookup(pn,tablesheet!A:B,2,false ),vlookup(left(pn,1),first_letter_table_sheet!A:B, 2,false)) This would keep you from having to identify each new pn that comes in where the first letter is adequate. But you would want to keep an eye on the new p/ns to ensure that the 'first letter rule' still applies. --Bruce "kleivakat" wrote: I've been asked to do some fairly detailed reporting quarterly for a customer, and told that I need to do it in Excel. I understand many of the functions for sorting, filtering, macros, etc., but am not sure how to set this up. Here's what I need to do. 1. Download all transactions from an AS400 database into Excel (Data/Import External Data... it works, I've done it.) This give me a list of every line item purchased for a certain time period and all related cost information from all purchase orders. 2. Separate that data into different product groups. Sometimes this is fairly straightforward, somes it's not. The product line is not identified in the AS400 database, only the model #. For example, if the model # begins with a "T", it's "Terrace" product. This is the part that's causing me the most trouble. There are many model numbers that are not easily recognizable as to which product group they belong to. (I'll come back to this below.) 3. Provide total dollar volume for each product group, and sometimes quantity of items purchased. (This isn't a problem once I figure out what model number goes where.) Step 2 is where I'm getting hung up. I'd like to figure out if there's a way to begin a parts list that identifies what product line a certain part belongs to. I can start this with the first report, and build on it each time a new part shows up on the report that I don't know. I probably have about 80% figured out at this point, but with over 6000 lines each quarter, this still takes a lot of time to figure out the last 20%. Should I create a worksheet that has columns for each product line, and just a list of model numbers that fit into each product line? Then use the lookup function when I need to find a certain model number? I'd like to then create a macros to automatically find the product line for each model #, but I don't know where that goes or how to create it. I've created some basic macros... at this point I'm looking for a strategy as to how to set this up. I can then try and work through the details, and may have more questions, but I'm not sure that what I described above will work. My knowledge level is somewhat above average, but far from expert! Thanks for any help or suggestions anyone mighit have as to how to do this in Excel. KK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"In Today's Dollars" help needed... | Excel Worksheet Functions | |||
solver related some financial knowledge may be needed | Excel Discussion (Misc queries) | |||
formula results take up to 2 lines if needed, but keep border | Excel Worksheet Functions | |||
Sumproduct help needed! | Excel Worksheet Functions | |||
building invoice - strategy advice | Excel Discussion (Misc queries) |