multiple find?

[jswaim]

Hi,
I have a problem, and would greatly appreciate any help. I work for an auto dealership with lots of cars and I am in charge of inventory. When I order vehicles I must look at current inventory and what is incoming in order to determine what needs to be ordered. In other words I must compare the models and options that we have and determine what must be ordered. Okay, here is the problem, I import data into a spreadsheet. The data looks like this (I am going to include the dollar sign ($) to represent each column.
Model $ Options $
cc1550 $ ag1,ae7,1sm $
cc1560 $ ag1,an3,1sb $
ck1563 $ ad3,an3,1sm $
cc1550 $ ad3,ae7,1sl $
I have hundreds of combinations like this and need to determine for example how many cc1550's we have with the ag1 and 1sm option and so on. This above is the most important. I currently use find and replace, but it will only let me find all ag1's, not multiple data criteria. What can I do? If possible I would like to be able to automatically put the findings in table, but if you could help me find the exact info. I need accurately it would help me greatly with my job which I really need to keep. Thank You very much.
This message was edited by jswaim on 2002-09-28 08:56

 

[Ricardo]

I do not understand the whole picture:
Do you have only 2 col , one with car and the other with a lot of options?
I think the best way is to build a Pivot Table.
IF you have all the options in one col you could separate that in n col (N: number of options)
OK?

 

[jswaim]

Well, I do have more than two columns but I did not include them here because I was going to work on that later because they have to do with age of inventory and VIN #. The column for the Model # and Options is what I am trying to concentrate on right now. I have various combinations of options sometimes totaling 30 or 40 different option codes. I will have cc15506's, some with a ae7, 1sb package and some with an ae7, 1sm package and so on. I am trying to determine which cc15506's are which and then count them and catagorize them. I am not sure on how to use the pivot table, but I will look into it. Thank You. If you have any more info. I sure could use it. Oh yeah, please note that the options are separated with comma's, I do not know if this is a problem?
This message was edited by jswaim on 2002-09-28 09:08

 

[Ricardo]

Sorry but with PT I could not make it work.

How do you use find & replace?

I have made an array with the autos like rows and with every options is a column

Then with COUNTIF(RAnge, Option) you could have a matrix detailling how many options you have for every car.
Do you agree?

 

[Ricardo]

Sorry
but I think now I understand:
EACH row is ONE car with the options and you want to count similar cars with similar options
Is that right?
That`s the reason the cc1550 is in 2 rows.
So let´s begin again
similar options are always in the same order? eg. ag1,ae7,1sm

We will crack it.....

 

[plettieri]

It is hard to see excatly what you are looking to do...you might want to use colo's html and send a sample of what you want ...in the meanehile several other have wanted to multiple lookups (using indirect, dget and others)..try the search engine on this board...its great..and a good one to start with is

http://www.mrexcel.com/board/viewtopic.php?topic=16933&forum=2


pll

 

[jswaim]

I really appreciate all the responses, sorry I did not get to get back so soon. I have been pulling my hair out using the pivot reports. I can't get it quite right. For some reason it always counts more options than are there, and it also only counts the options on the first row and leaves others out. There answer to one of your questions was are the options always in the same order and the answer is no. They are always in alphabetical order but, some vehicles have more options than others. Such as
Model$ Options$
cc15506 $ ae7,an3,1sb$
cc15506 $ ab1,ae7,1sb$
cc15506 $ ae7,an3,1sm$
Thanks again for all your responses.
Oh, yeah Ricardo, yes the models are all on different rows, and the options are following to the right. Each row is a different vehicle, some with the same options, others with similar and different options.
This message was edited by jswaim on 2002-09-28 16:08