Identifying largest number in a column

[doublej41]

Hi All

I have 2 columns of data, column A is an incremental ID number and column B includes a product name. When the product name is selected from the drop down box in Column B, I would like column A to automatically show the next ID number in the sequence.

The image illustrates the columns to try and make sense of my query. What I would like to happen is when I select BC-125 in cell B6, for a formula to work out that the next ID number for BC125 is 004 and so on for BC-100 and BC-150.




Uploaded with ImageShack.us


Any suggestions will be greatly appreciated.

Thanks

 

[Richard Schollar]

Assuming you are using Autofilter to select the producs in col B you can use SUBTOTAL to return the highest number of the remaining values:

=SUBTOTAL(4,A2:A10000)

So to get the next one you can increment by one:

=SUBTOTAL(4,A2:A10000)+1

 

[doublej41]

Thanks for your reply.

Is there a way of doing it without using the Autofilter? I would like all product names and ID's to be visible all the time as an ongoing list that people add to.

 

[Richard Schollar]

Yep - but where will you select the relevant Product? Say it is in a cell (eg C1) then you can use an array formula:

=MAX(IF(B2:B10000=C1,A2:A10000))

which must be confirmed with Ctrl+Shift+Enter. Excel will surround with curly braces {} following successful entry (don't try and enter these manually yourself).

 

[doublej41]

OK - That works great, thank you.


However as I have more than one product, is there a way to reference a list as opposed to just one cell. I have tried referencing C1:C3 (instead of just C1 as I have multiple products) and I have also tried giving this a dynamic range name, both of which returned #N/A.

As there are already multiple products and more will be added in the future I need a way of referencing all products. If there is a way to incorporate a dynamic range name that would be useful, as I already have one set up for the drop down list in column B.

 

[Richard Schollar]

How does your spreadsheet work? Is it a Validation list that you have in the B column cells so eg a user will pick a product for example in B6 then you want A6 to be filled with the next incremental ID number automatically? If this is the case then you are looking at some relatively simple VBA (this is probably best rather than trying to shoehorn it with formulas/another dropdown). I can show you how if you want to go this route?

 

[doublej41]

I would like to avoid using VBA if possible.

My spreadsheet has 2 tabs, on the first are the two columns that are shown in the image above, column A is the product ID and column B is the product name. The product name is selected from a validation drop down list. The second tab contains a list of products in column A, this forms the dynamic range name that is used as the source for the drop down list.

When the user selects a product from the drop down list in say B6, I want A6 to display the next incremental ID. The issue I am having is that there are multiple products and I want each product's ID to start from 1, hence the original request for finding the maximum.

So the Max If formula you suggested does the job when only talking about one product, but I need it for multiple. So instead of asking if B1:10000=C1, is it possible to reference more than one cell or a named range i.e. B1:B10000=C1:C3?


Sorry for the confusion. I hope this is more clear. As you can probably guess I am a novice when it comes to Excel.

 

[Richard Schollar]

But why would you want to reference more than one product? You will only be returning one value (ie the next incremental ID) so why do you need to reference multiple Products to achieve this - surely you are only interested in the specific product?

 

[doublej41]

I think I can see the confusion. Am I correct in thinking that for your formula to work, there must be a drop validation list in cell C1? As it stands there isn't (which is why I was thinking the formula was restricted to one product).

At the moment I only have one validation list in column B, where the user selects the product. Can this work without the addition of a second validation list?

 

[Gerald Higgins]

No, I don't think Richard's solution depends on having a drop validation list in C1.

You began this thread by saying you wanted to use a single product name.
But in post #5 you began talking about referring to more than one product at a time.
It's not clear to me (and I think maybe Richard is also confused) as to how exactly this would work, or even why you would want to do it.