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

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

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

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

My knowledge in VB (is this what you used for the Macro?) is lacking,
although I can slightly understand what you are doing. I might have a friend
who could help me adjust it to my data.
thank you!

"Bernie Deitrick" wrote:

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

Yossi wrote:
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

If the functions in the freely downloadable file at
http:?home.pacbell.net/beban are available to your workbook

=INDEX(ArrayMatch(1,$A$1:$A$6,"A",4),ROW(A1)) entered in a cell and
copied down until you get a #REF! error; or
=IF(NOT(ISERROR(INDEX(ArrayMatch(1,$A$1:$A$6,"A",4 ),ROW(A1)))),INDEX(ArrayMatch(1,$A$1:$A$6,"A",4),R OW(A1)),"")
copied down the length of the data file (6 in this case).

Alan Beban,

Hi Alan,

I think I need to use this formula you show on your website but I can't get
it to work.
I have a column of values in column a. Can you help me ?

For example the formula should look up col.A # 22 and return fro col.D the
following list: Dog Biscuit Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle



Function VLookups(lookupValue, lookupArray, ColumnNumber)
'This function acts somewhat like the built-in VLookup
'Function with exact matches, except that it returns an
'array of all the values corresponding to multiple
'occurrences of the sought value in the left hand column
'(i.e., the first column vector in the lookup array).


"Alan Beban" wrote:

Yossi wrote:
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

If the functions in the freely downloadable file at
http:?home.pacbell.net/beban are available to your workbook

=INDEX(ArrayMatch(1,$A$1:$A$6,"A",4),ROW(A1)) entered in a cell and
copied down until you get a #REF! error; or
=IF(NOT(ISERROR(INDEX(ArrayMatch(1,$A$1:$A$6,"A",4 ),ROW(A1)))),INDEX(ArrayMatch(1,$A$1:$A$6,"A",4),R OW(A1)),"")
copied down the length of the data file (6 in this case).

Alan Beban,

see your other post

--
Biff
Microsoft Excel MVP


"ORLANDO VAZQUEZ" wrote in message
...
Hi Alan,

I think I need to use this formula you show on your website but I can't
get
it to work.
I have a column of values in column a. Can you help me ?

For example the formula should look up col.A # 22 and return fro col.D the
following list: Dog Biscuit Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle



Function VLookups(lookupValue, lookupArray, ColumnNumber)
'This function acts somewhat like the built-in VLookup
'Function with exact matches, except that it returns an
'array of all the values corresponding to multiple
'occurrences of the sought value in the left hand column
'(i.e., the first column vector in the lookup array).


"Alan Beban" wrote:

Yossi wrote:
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

If the functions in the freely downloadable file at
http:?home.pacbell.net/beban are available to your workbook

=INDEX(ArrayMatch(1,$A$1:$A$6,"A",4),ROW(A1)) entered in a cell and
copied down until you get a #REF! error; or
=IF(NOT(ISERROR(INDEX(ArrayMatch(1,$A$1:$A$6,"A",4 ),ROW(A1)))),INDEX(ArrayMatch(1,$A$1:$A$6,"A",4),R OW(A1)),"")
copied down the length of the data file (6 in this case).

Alan Beban,