Compare Rows, group identical entries, and find lowest value

[Tim1900]

Hi
I have a sheet with over 1000 entries.
Part of the columns are build up as below.

A A 4
A A 6
A A 5
A B 7
A B 9
A B 8

I'm trying to make a formula that:
- first checks in which rows columns A and B are equal, (in this example, row 1, 2 and 3)
- then, from those reflected rows, determine which has the lowest value.
- display this with an X in the following column.

So this should give the following results:

A A 4 -> X
A A 6 ->
A A 5 ->

A B 7 ->
A B 9 ->
A B 8 -> X


The values above, like A and B, are really citynames in the real document.
Tried vlookup, in combination with IF and AND statements, but I keep locking up.

any help appreciated.

thanx,
Tim

 

[T. Valko]

OK.
I surely understand it has to go through to the entire column-range to find/detect group the data, but I'd expect it to then put 3 X-es in my sheet, when I make an exact copy of your example in an empty sheet. And that what it doesn't do for me.

It only puts 1 X in the entire sheet on the row where column C has the lowest value of all rows. So the formula thinks the entire sheet is 1 group (as I name it).
It should present 3 X-es in the above example, using this formula.

It's either me not copying the data correctly as I have to make some changes for the IF-statements. (changing the comma's to the ';'-character due to my regional settings), or the formula is incorrect for my purpose.

Nevertheless, thanks for the help, and maybe somebody could have an eye on it again.

If you happen to have an excel file where this does work normally as I , I'd really love to receive a copy. Any way we could share files through the forum?

Thanx a lot,
Tim

Here's a small sample file that demonstrates this (as I understand it!).

zzzTim1900.xls 16kb

http://cjoint.com/?AFsxwU09shB

If you want to swap files then it has to be done either through private email or by posting the file to some other site then downloading it from that site. In this case I've used a free file hosting site.

 

[Tim1900]

That is it!! Great!
I really don't get why the formula doesn't work when I manually add it to my test-sheet, but this behaves as it mentioned.

I now only have to include it in the real excel file.


Thanx a lot for this.

 

[T. Valko]

That is it!! Great!
I really don't get why the formula doesn't work when I manually add it to my test-sheet, but this behaves as it mentioned.

I now only have to include it in the real excel file.


Thanx a lot for this.

Good deal. Thanks for the feedback!