Returning more than one value

[DunderMifflin]

Hi,

If I have data that looks like the following:

(Row)
(1) Dan Ted Tom Bill

(2) 1 2 4 2


I am trying to create a formula that tests for ties. If there is a tie
in a row (in this case, Ted and Bill, keeping in mind there could be
more than two who tie) ideally I would like row 3 to output each
person's name who tie and the tie value.

Maybe some output like this:

(3) 2 Ted Bill

 

[Biff]

Hi!

Is it possible to have more than 1 match?

Dan Ted Tom Bill
...1........2.........1.......2

Biff

"DunderMifflin" <[email protected]>
wrote in message
news[email protected]...

 

[DunderMifflin]

Biff,

Thanks for the reply.

Yes, it is possible to have more than one 'tie'.

Could this be done easier using VBA? I know VB very well, but no
really familiar with VBA through Excel. Would it be easier to loo
through the row and use variables? I don't know, just throwing it ou
there.

Thanks

 

[DunderMifflin]

bump.........

 

[Biff]

Hi!

Sorry about responding so late. I lost track of this thread. Anyhow....

I suppose this would be easier if you know VBA but I don't know VBA at all!

Using worksheet formulas it would not be possible to get all of the possible
data in a single row if there is more than one match. That is:

Dan Ted Tom Bill
...1........2.........1.......2


From that sample you can get:

1.....Dan.....Tom
2.....Ted.....Bill

Here are the formulas that will do what you want.

So, assume your data is in the range A12

In A3 enter this formula with the key combo of CTRL,SHIFT,ENTER:

=IF(ISERROR(SMALL(IF(FREQUENCY(A$2$2,A$2$2)>1,ROW($1:$4)),ROW(1:1))),"",INDEX(A$2$2,SMALL(IF(FREQUENCY(A$2$2,A$2$2)>1,ROW($1:$4)),ROW(1:1))))

Copy *down* enough rows to cover the highest number of possible matches. If
your data really is only 4 columns then the highest possible number of
matches would be 2.

In B3 enter this formula with the key combo of CTRL,SHIFT,ENTER:

=IF(ISERROR(SMALL(IF($A$2:$D$2=$A3,COLUMN($A2:$D2)),COLUMN(A:A))),"",INDEX($A$1:$D$1,SMALL(IF($A$2:$D$2=$A4,COLUMN($A2:$D2)),COLUMN(A:A))))

Again, copy *across* to enough columns to cover the highest possible number
of ties. If your data really is only 4 columns then the highest possible
number of ties would be 4. Copy across then down.

Biff

"DunderMifflin" <[email protected]>
wrote in message
news[email protected]...