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

 

[Tim1900]

Of course these should be the correct values:

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

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

 

[T. Valko]

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

Maybe this...

Book1
	A	B	C	D
2	A	A	4	X
3	A	A	6	_
4	A	A	5	_
5	A	B	7	X
6	A	B	9	_
7	A	B	8	_
8	A	C	4	X
9	A	C	5	_
Sheet1

This array formula** entered in D2 and copied down:

=IF(C2=MIN(IF(A$2:A$9=A2,IF(B$2:B$9=B2,C$2:C$9))),"X","")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Tim1900]

It's a start for sure.
But it only displays the 'X' in column D, for the row with the lowest value in column C.

But it checks for the entire column A and B, not the supplied 'groups'.

 

[T. Valko]

It's a start for sure.
But it only displays the 'X' in column D, for the row with the lowest value in column C.

Isn't that what you wanted? That's what you displayed in your post.

But it checks for the entire column A and B, not the supplied 'groups'.

Not sure what that means. How are groups "supplied"?

 

[Tim1900]

THanx for your time btw..
really appreciate it!

Not sure what that means. How are groups "supplied"?

The X-es are displayed correctly in your example above (using the correct groups), but this doesn't seem to work OK when I copy, or even type it in my excel file.

The "groups" in your example would be row:
2,3,4 (<-- as 1 block of similar data)
5,6,7
8,9
In these rows column A and B are identical. not sure how to clarify this other.

When I use your formule, it searches the entire sheet, from row 2 to 9 in your example, and only adds the "x" in column D, where column C has the lowest value.

Thanx,
Tim

 

[T. Valko]

THanx for your time btw..
really appreciate it!




The X-es are displayed correctly in your example above (using the correct groups), but this doesn't seem to work OK when I copy, or even type it in my excel file.

The "groups" in your example would be row:
2,3,4 (<-- as 1 block of similar data)
5,6,7
8,9
In these rows column A and B are identical. not sure how to clarify this other.

When I use your formule, it searches the entire sheet, from row 2 to 9 in your example, and only adds the "x" in column D, where column C has the lowest value.

Thanx,
Tim

Well, I guess I don't understand what you want to do.

 

[Tim1900]

Might be writing the same, but to write it down a little different:

- from all rows in the sheet, first detect which rows have equal values in column A AND B
group 1: row 2,3,4
group 2: row 5,6,7
group 3: row 8,9

- Then inside these groups, find the lowest value in column C
- show this with an X in column D

 

[T. Valko]

Might be writing the same, but to write it down a little different:

- from all rows in the sheet, first detect which rows have equal values in column A AND B
group 1: row 2,3,4
group 2: row 5,6,7
group 3: row 8,9

- Then inside these groups, find the lowest value in column C
- show this with an X in column D

That's exactly what my suggestion does.

I think you're getting "hung up" on the fact that the formula tests each full column of data. You probably think that it should only test rows 2,3,4 for group1, only test rows 5,6,7 for group2 and only test rows 8,9 for group3.

Well, in a way, that's what the formula is doing but in order to do that it has to look at the entire data range to determine which groups are where.

If I'm completely off the mark then maybe a fresh set of eyes will see what I'm failing to see.

Good luck!

 

[Tim1900]

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