Yossi,

Doing that with formulas would be complicated, and difficult to maintain. A
macro would be easier. With your table starting in A1, with headers in row
1, and columns of labels in A and B, the macro below will give you the table
you want, in two columns to the right of your main table. It could be fired
from a worksheet change event, to update the tally table automatically.

HTH,
Bernie
MS Excel MVP

Sub YossiTable()
Dim myCell As Range
Dim myCol As Integer
Dim myTable As Range

myCol = Range("A1").CurrentRegion.Columns.Count + 2
Set myTable = Range("A1").CurrentRegion.Offset(1, 2) _
.Resize(Range("A1").CurrentRegion.Rows.Count - 1, myCol - 4)

Cells(1, myCol).Resize(1, 2).EntireColumn.Clear
Cells(1, myCol).Value = "Name"
Cells(1, myCol + 1).Value = "Rank"

For Each myCell In myTable
If myCell.Value = 1 Then
Cells(65536, myCol).End(xlUp)(2).Value = _
Cells(myCell.Row, 1).Value
Cells(65536, myCol + 1).End(xlUp)(2).Value = _
Cells(1, myCell.Column).Value
End If
Next myCell

Cells(1, myCol).CurrentRegion.Sort Cells(1, myCol + 1), _
xlAscending, header:=xlYes
End Sub




"Yossi" wrote in message
...
Well, since you asked, the problem is more complicated than that :-)
I have a kind of a table with a list of values on the left columns and a
matrix on the right ones.
Each value on the left can have one or more appearances in the matrix
represented by the value 1 (on the equivalent row).
it looks like this:

Name Last Name 1st 2nd 3rd 4th 5th
Tim Brown 1 1
Jack Erdwin 1
Maya Kohen 1 1 1

I want to represent the information like this or in similar form:
Name Position
Tim 1st
Jack 2nd
Tim 3rd
Maya 3rd
Maya 4th
Maya 5th

How can I extract the names of all who are in 1st position, then all who
are
in 2nd position etc.?

"Bernie Deitrick" wrote:

Yossi,

Yes, using an array formula entered into multiple cells. Is that what
you
want, or do you want the values returned to a single cell? If so, you
could
use a user-defined-function, or use two formulas (one additional one to
concatenate the results of the multi-cell array formula.)

Any preference? But, more importantly, how will you be using that
information?

HTH,
Bernie
MS Excel MVP


"Yossi" wrote in message
...
Hi,
I have a reference to a row and in that row, several cells have the
value
of
1. I would like to get the positions of all of them from the array.
For example:
A1 A2 A3 A4 A5 A6
1 0 1 1 0 0

I would like to get A1, A3, A4
is there a way to retrieve that information in a single formula?
thanks