To find different values in Col B corresp. to repeated vaues in c
 

Is there any formula to find the different(1st,2nd,3rd...) values in column
B(B1:B10) corresponding to repeated values in column A(A1:A10),without
creating another column?

Hi!

Try this array formula by entering with the key combo of
CTRL,SHIFT,ENTER:

=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=1,ROW
($A$1:$A$10)),ROW(1:1)))

Replace the "1" in the IF function with whatever the
repeated value from col A is or use a cell reference.

You can find every Nth value by copying down. If you only
want to find the specific Nth value just change the 1:1 in
the second ROW function to the Nth value you want to find.
Example: 3:3 for the 3rd value.

An alternative would be to use a reference cell to hold
the Nth value you want to return. For example, in C1 you
enter 3 to return the 3rd value:

=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=1,ROW
($A$1:$A$10)),ROW(INDIRECT("A"&C1))))

Biff

-----Original Message-----
Is there any formula to find the different
(1st,2nd,3rd...) values in column
B(B1:B10) corresponding to repeated values in column A
(A1:A10),without
creating another column?
.

Do you mean simply highlight them?

If so then select A1:B10, do Format / Conditional formatting, change cell
vaue is to formula is and put in =$A1<$B1

Now hit the format button, choose a nice pastel colour and hit OK until you
are out.

Regards
Ken....................

"K.S.Warrier" wrote:

Is there any formula to find the different(1st,2nd,3rd...) values in column
B(B1:B10) corresponding to repeated values in column A(A1:A10),without
creating another column?

Thanks a lot Mr.Biff
K.S.Warrier


"Biff" wrote:

Hi!

Try this array formula by entering with the key combo of
CTRL,SHIFT,ENTER:

=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=1,ROW
($A$1:$A$10)),ROW(1:1)))

Replace the "1" in the IF function with whatever the
repeated value from col A is or use a cell reference.

You can find every Nth value by copying down. If you only
want to find the specific Nth value just change the 1:1 in
the second ROW function to the Nth value you want to find.
Example: 3:3 for the 3rd value.

An alternative would be to use a reference cell to hold
the Nth value you want to return. For example, in C1 you
enter 3 to return the 3rd value:

=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=1,ROW
($A$1:$A$10)),ROW(INDIRECT("A"&C1))))

Biff

-----Original Message-----
Is there any formula to find the different
(1st,2nd,3rd...) values in column
B(B1:B10) corresponding to repeated values in column A
(A1:A10),without
creating another column?
.

Thanks a lot Mr.Ken Wright.
I meant not simply highlighting.
K.S.Warrier

"Ken Wright" wrote:

Do you mean simply highlight them?

If so then select A1:B10, do Format / Conditional formatting, change cell
vaue is to formula is and put in =$A1<$B1

Now hit the format button, choose a nice pastel colour and hit OK until you
are out.

Regards
Ken....................

"K.S.Warrier" wrote:

Is there any formula to find the different(1st,2nd,3rd...) values in column
B(B1:B10) corresponding to repeated values in column A(A1:A10),without
creating another column?

I'm a tad confused - If you don't want to highlight them and don't want
another column, where exactly do you want the data to be returned to, and in
what format?

Regards
Ken...........

"K.S.Warrier" wrote:

Thanks a lot Mr.Ken Wright.
I meant not simply highlighting.
K.S.Warrier

"Ken Wright" wrote:

Do you mean simply highlight them?

If so then select A1:B10, do Format / Conditional formatting, change cell
vaue is to formula is and put in =$A1<$B1

Now hit the format button, choose a nice pastel colour and hit OK until you
are out.

Regards
Ken....................

"K.S.Warrier" wrote:

Is there any formula to find the different(1st,2nd,3rd...) values in column
B(B1:B10) corresponding to repeated values in column A(A1:A10),without
creating another column?

Hi Mr.Biff,
I have tried.But I am not getting the result correctly.In one example,first
value/text is got,for 2nd,3rd,...values ,same value is showing.In some
example error message #value! or #REF! is shown.After entering the formula,I
have pressed CTRL,SHIFT,ENTER.I have only 2 columns(A1:A10 ) &(B1:B10) in the
example.Whether the formula is applicable for values(both numbers & texts) in
column A & B.
Thanks in advance
K.S.Warrier

"K.S.Warrier" wrote:

Thanks a lot Mr.Biff
K.S.Warrier


"Biff" wrote:

Hi!

Try this array formula by entering with the key combo of
CTRL,SHIFT,ENTER:

=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=1,ROW
($A$1:$A$10)),ROW(1:1)))

Replace the "1" in the IF function with whatever the
repeated value from col A is or use a cell reference.

You can find every Nth value by copying down. If you only
want to find the specific Nth value just change the 1:1 in
the second ROW function to the Nth value you want to find.
Example: 3:3 for the 3rd value.

An alternative would be to use a reference cell to hold
the Nth value you want to return. For example, in C1 you
enter 3 to return the 3rd value:

=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=1,ROW
($A$1:$A$10)),ROW(INDIRECT("A"&C1))))

Biff

-----Original Message-----
Is there any formula to find the different
(1st,2nd,3rd...) values in column
B(B1:B10) corresponding to repeated values in column A
(A1:A10),without
creating another column?
.

Hi Mr. Ken Wright,
What I want is not to highlight,but to get value/text in a cell(D1) where I
apply the formula.Suppose A1:A10 contains 10 values/texts (with some
repetitions) &B1:B10 contains the corresponding values/texts.I want to verify
the value/text of C1 in A1:A10 & to get corresponding value/text from
B1:B10,even though the value in C1 is a repeated one in BA1:A10.
Thanking you,
K.S.Warrier

"Ken Wright" wrote:

I'm a tad confused - If you don't want to highlight them and don't want
another column, where exactly do you want the data to be returned to, and in
what format?

Regards
Ken...........

"K.S.Warrier" wrote:

Thanks a lot Mr.Ken Wright.
I meant not simply highlighting.
K.S.Warrier

"Ken Wright" wrote:

Do you mean simply highlight them?

If so then select A1:B10, do Format / Conditional formatting, change cell
vaue is to formula is and put in =$A1<$B1

Now hit the format button, choose a nice pastel colour and hit OK until you
are out.

Regards
Ken....................

"K.S.Warrier" wrote:

Is there any formula to find the different(1st,2nd,3rd...) values in column
B(B1:B10) corresponding to repeated values in column A(A1:A10),without
creating another column?