PDA

View Full Version : IF formula for identifying unique rows (quite confusing problem)



j.smith1981
06-01-2010, 01:39 AM
I have a good question here, can someone help?

I have 2 columns I want to uniquely identify here.

Take columns B and C for this example.

Here's an example of this dilemma:

Printer model | Cartridge code

Dell 725 | 592-10177
Dell 810 | 592-10177

I want to uniquely identify these 2 rows, so I can put an ID number in each one.

Where the layout would be like so:

Category ID | Printer Model | Cartridge Code

If there isnt a number above the current ID, like for the 1st row, then use 1 and if there is one above, add 1 to the existing value.

How would this be possible, can someone help?

Thanks for any replies in advance,
Jeremy.

Bob Phillips
06-01-2010, 02:57 AM
Type 1 in row 1, 2 in row 2, select both of them, then just autofill down.

j.smith1981
06-01-2010, 05:30 AM
Thanks ever so much for that reply.

I forgot to mention however, my data is stripped over 2 sheets in an excel workbook.

Is there anyway of making a formula look at the last value of the ID column say on sheet 2, like so:

Sheet1 (contains the ink's x reference):

cat ID | cat name
1 | cat1
2 | cat2
3 | cat3
4 | cat4

Then Sheet 2 will follow on from that:
cat ID | cat 5
5 | cat5
6 | cat6
and so on....

Is there anyway of doing this in an excel formula?

Thanks again in advance,
Jeremy.

Bob Phillips
06-01-2010, 05:41 AM
Sheet2:

A2: =MAX(Sheet1!A:A)+1
A3: =A2+1

Copy A3 down

j.smith1981
06-01-2010, 06:41 AM
Actually scratch both of those ideas I had!

They wouldnt simply work for the concept I am looking at, at all to be fair!

I must apologise, as I only saw this when I was trying to get the macro to pick these ID's up, then I can see the errors in my ways as it where.

Take this example just as a concept yea?

Category name | Cartridge
Category 1 | Cartridge1
Category 1 | Cartridge2
Category 1 | Cartridge3

Category 2 | Cartridge1

So it would look like:
Cat ID | Cat Name | Cartridge Name
1 | Category 1 | Cartridge1
1 | Category 1 | Cartridge2
1 | Category 1 | Cartridge3

2 | Category 2 | Cartridge 1

Is there anyway of making excel from a formula, work out when a user types in a value into Cat Name field yea?

That it checks the value the user has just inputted, if that value is exactly the same in the above row, then display the same ID number.

If it doesnt then add 1 up to that value, like the above example as it where.

I hope this makes sense.

Thanks for any replies in advance,
Jeremy

mdmackillop
06-01-2010, 09:27 AM
Hi Jeremy,
Post a sample workbook showing what you are after, with explanatory notes. This doesn't sound difficult, but a clearer understanding would help.
Regards
MD