Return Array with Array
 

Thanks for taking the time to read my question.

I am just learning how to use Array formulas in Excel. I can Sum, Count
etc. I am wondering if you can return an array of data with an array formula

Example:

Name Replied?

Bob N
Brian Y
Robyn N
Rachel Y

From the table above if I test for "N" I'd like the formula to return a list
of names, in this case Bob and Robyn.

Thanks again for your help,

Brad

Return Array with Array
 

Assumptions:

A1:B4 contains your data

C1 contains your criteria, such as 'N'

Formula:

D1, copied down:

=IF(ROWS($D$1:D1)<=COUNTIF($B$1:$B$4,$C$1),INDEX(A $1:A$4,SMALL(IF($B$1:$B
$4=$C$1,ROW($B$1:$B$4)-ROW($B$1)+1),ROWS($D$1:D1))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"Brad" wrote:

Thanks for taking the time to read my question.

I am just learning how to use Array formulas in Excel. I can Sum, Count
etc. I am wondering if you can return an array of data with an array formula

Example:

Name Replied?

Bob N
Brian Y
Robyn N
Rachel Y

From the table above if I test for "N" I'd like the formula to return a list
of names, in this case Bob and Robyn.

Thanks again for your help,

Brad

Return Array with Array
 

=INDEX($A$1:$A$4,SMALL(IF($B$1:$B$4="N",ROW(B1:B4) ),ROW(1:1)))
array entered
assuming A1to a4 is your cells holding the names and b1:b4 holds the replies

Copy this formula all the way down till you encounter a #NUM! error
or ask it within a If(isserror(ind... to get rid of the errors too




"Brad" wrote in message
...
Thanks for taking the time to read my question.

I am just learning how to use Array formulas in Excel. I can Sum, Count
etc. I am wondering if you can return an array of data with an array
formula

Example:

Name Replied?

Bob N
Brian Y
Robyn N
Rachel Y

From the table above if I test for "N" I'd like the formula to return a
list
of names, in this case Bob and Robyn.

Thanks again for your help,

Brad

Return Array with Array
 

Hi!

One way:

Assume data is in the range A2:B5

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF(B$2:B$5,"N"),INDEX(A$2:A$5 ,SMALL(IF(B$2:B$5="N",(ROW(A$2:A$5)-ROW(A$2))+1),ROWS($1:1))),"")

Copy down until you get blanks meaning the data has been exhausted.

Biff

"Brad" wrote in message
...
Thanks for taking the time to read my question.

I am just learning how to use Array formulas in Excel. I can Sum, Count
etc. I am wondering if you can return an array of data with an array
formula

Example:

Name Replied?

Bob N
Brian Y
Robyn N
Rachel Y

From the table above if I test for "N" I'd like the formula to return a
list
of names, in this case Bob and Robyn.

Thanks again for your help,

Brad

Return Array with Array
 

Select 4 cells, array enter this formula in all 4 cells

=IF(ISERROR(SMALL(IF(B2:B5="Y",ROW(B2:B5),""),ROW( 1:4))),"",INDEX(A1:A5,SMAL
L(IF(B2:B5="Y",ROW(B2:B5),""),ROW(1:4))))

note that index starts from row 1, I find that easier to understand than to
have to offset the result because you would want to use A2:A5


--

Regards,

Peo Sjoblom

"Brad" wrote in message
...
Thanks for taking the time to read my question.

I am just learning how to use Array formulas in Excel. I can Sum, Count
etc. I am wondering if you can return an array of data with an array
formula

Example:

Name Replied?

Bob N
Brian Y
Robyn N
Rachel Y

From the table above if I test for "N" I'd like the formula to return a
list
of names, in this case Bob and Robyn.

Thanks again for your help,

Brad

Return Array with Array
 

Hello.. I am not an Excel expert...but here's a very simple formula that will
return the names of those who have not replied. Assuming that your data is
in a1:b5, enter this into Col. C,

=IF(B$2:B$5="N",A$2:A$5,"") then drag down to bottom

(Translation: if any cell in column B range is N, then return corresponding
values in column A range, otherwise return a blank).

Of course, you'll have a bunch of blank cells, but you can always filter the
column where your results are (after you've transformed them to values), and
filter in all the non-blanks.

"Brad" wrote:

Thanks for taking the time to read my question.

I am just learning how to use Array formulas in Excel. I can Sum, Count
etc. I am wondering if you can return an array of data with an array formula

Example:

Name Replied?

Bob N
Brian Y
Robyn N
Rachel Y

From the table above if I test for "N" I'd like the formula to return a list
of names, in this case Bob and Robyn.

Thanks again for your help,

Brad

Return Array with Array
 

hi Brad.. I'm not one of the super-experts..but there is a very simple
formula that will also work, if you don't mind blank cells in your results
column. Assuming your data is in A1:B5, paste this in say, cell C2, and then
drag all the way down:

=IF(B$2:B$5="N",A$2:A$5,"")

You can then convert your results to values, and use the autofilter arrows
to filter in all your "nonblanks" - that way you get rid of all your blanks.

M

"Brad" wrote:

Thanks for taking the time to read my question.

I am just learning how to use Array formulas in Excel. I can Sum, Count
etc. I am wondering if you can return an array of data with an array formula

Example:

Name Replied?

Bob N
Brian Y
Robyn N
Rachel Y

From the table above if I test for "N" I'd like the formula to return a list
of names, in this case Bob and Robyn.

Thanks again for your help,

Brad

Return Array with Array
 

Thanks everyone for the help. Great formulas. Wow do I ever need to learn
more about Excel functions.

I was hoping to return all the names listed in one cell, but this is ok too.

Thanks again,

Brad

"Brad" wrote:

Thanks for taking the time to read my question.

I am just learning how to use Array formulas in Excel. I can Sum, Count
etc. I am wondering if you can return an array of data with an array formula

Example:

Name Replied?

Bob N
Brian Y
Robyn N
Rachel Y

From the table above if I test for "N" I'd like the formula to return a list
of names, in this case Bob and Robyn.

Thanks again for your help,

Brad

Return Array with Array
 

Brad-

I am trying to do something similar and need my data to appear in one cell
also. If you figure out how to get the output in one cell only, please reply.

Thanks.

"Brad" wrote:

Thanks for taking the time to read my question.

I am just learning how to use Array formulas in Excel. I can Sum, Count
etc. I am wondering if you can return an array of data with an array formula

Example:

Name Replied?

Bob N
Brian Y
Robyn N
Rachel Y

From the table above if I test for "N" I'd like the formula to return a list
of names, in this case Bob and Robyn.

Thanks again for your help,

Brad

Return Array with Array
 

You can use the formulas to return the data then a second formula to
concatenate, assume the result are in B1:B4 then you can use

=B1&" "&B2&" "&B3&" "&B4

to put the result in one cell


--

Regards,

Peo Sjoblom

"jackie" wrote in message
...
Brad-

I am trying to do something similar and need my data to appear in one cell
also. If you figure out how to get the output in one cell only, please
reply.

Thanks.

"Brad" wrote:

Thanks for taking the time to read my question.

I am just learning how to use Array formulas in Excel. I can Sum, Count
etc. I am wondering if you can return an array of data with an array
formula

Example:

Name Replied?

Bob N
Brian Y
Robyn N
Rachel Y

From the table above if I test for "N" I'd like the formula to return a
list
of names, in this case Bob and Robyn.

Thanks again for your help,

Brad

Return Array with Array
 

Hi Jackie,

Here is some code I made quickly.

Paste it into a module, then go back to the worksheet. Click on the Insert
menu, then choose Function. Under the Function category choose User Defined.
Then on the right side select SingleCellString.

The Data range refers to the list of info that has the values you want to
return in the string in a single cell. The ConditionalData is the data your
testing against, and TestValue is the value you want to test for in the
ConditionalData.

If you need more variables, let me know and I can add more.

Brad

code
-------------------------------------
Function SingleCellString(Data As Range, ConditionalData As Range, TestValue
As Range) As String
Dim x, y, RowOfData As Integer

x = 0
y = 0
For Each a In Data
x = x + 1
Next

For Each b In ConditionalData
y = y + 1
Next

If x < y Then
SingleCellString = "The 'Data' range of cells does not equal the number
of cells in the 'ConditionalData' range of cells. Please check the ranges and
try again."
Exit Function
End If

RowOfData = 0
For Each c In ConditionalData
RowOfData = RowOfData + 1
If c = TestValue.Value Then
If SingleCellString = "" Then
SingleCellString = Cells(Data.Row + RowOfData - 1,
Data.Column).Value
Else
SingleCellString = SingleCellString & ", " & Cells(Data.Row +
RowOfData - 1, Data.Column).Value
End If
End If
Next

End Function
-----------------------------------



"jackie" wrote:

Brad-

I am trying to do something similar and need my data to appear in one cell
also. If you figure out how to get the output in one cell only, please reply.

Thanks.

"Brad" wrote:

Thanks for taking the time to read my question.

I am just learning how to use Array formulas in Excel. I can Sum, Count
etc. I am wondering if you can return an array of data with an array formula

Example:

Name Replied?

Bob N
Brian Y
Robyn N
Rachel Y

From the table above if I test for "N" I'd like the formula to return a list
of names, in this case Bob and Robyn.

Thanks again for your help,

Brad