Strategy Needed

[kleivakat]

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

 

[bpeltzer]

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_sheet!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]

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