multiple find?

jswaim

Board Regular
Joined
Sep 27, 2002
Messages
141
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Ricardo

Board Regular
Joined
Jun 26, 2002
Messages
149
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

Board Regular
Joined
Sep 27, 2002
Messages
141
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

Board Regular
Joined
Jun 26, 2002
Messages
149
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

Board Regular
Joined
Jun 26, 2002
Messages
149

ADVERTISEMENT

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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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

Board Regular
Joined
Sep 27, 2002
Messages
141
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
 

Forum statistics

Threads
1,143,745
Messages
5,720,608
Members
422,292
Latest member
Bernd0501

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top