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


--
DunderMifflin
------------------------------------------------------------------------
DunderMifflin's Profile: http://www.excelforum.com/member.php...o&userid=23780
View this thread: http://www.excelforum.com/showthread...hreadid=374161

Hi!

Is it possible to have more than 1 match?

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

Biff

"DunderMifflin"
wrote in message
news:DunderMifflin.1pnqs7_1117142146.0052@excelfor um-nospam.com...

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


--
DunderMifflin
------------------------------------------------------------------------
DunderMifflin's Profile:
http://www.excelforum.com/member.php...o&userid=23780
View this thread: http://www.excelforum.com/showthread...hreadid=374161

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 not
really familiar with VBA through Excel. Would it be easier to loop
through the row and use variables? I don't know, just throwing it out
there.

Thanks.


--
DunderMifflin
------------------------------------------------------------------------
DunderMifflin's Profile: http://www.excelforum.com/member.php...o&userid=23780
View this thread: http://www.excelforum.com/showthread...hreadid=374161

bump..........


--
DunderMifflin
------------------------------------------------------------------------
DunderMifflin's Profile: http://www.excelforum.com/member.php...o&userid=23780
View this thread: http://www.excelforum.com/showthread...hreadid=374161

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 A1:D2

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

=IF(ISERROR(SMALL(IF(FREQUENCY(A$2:D$2,A$2:D$2)1, ROW($1:$4)),ROW(1:1))),"",INDEX(A$2:D$2,SMALL(IF(F REQUENCY(A$2:D$2,A$2:D$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"
wrote in message
news:DunderMifflin.1pp9ue_1117213503.0551@excelfor um-nospam.com...

bump..........


--
DunderMifflin
------------------------------------------------------------------------
DunderMifflin's Profile:
http://www.excelforum.com/member.php...o&userid=23780
View this thread: http://www.excelforum.com/showthread...hreadid=374161