if statement to return a list of values where answer is true

[Eric D]

is it possible to have an if statement or something that could return true
values in a list? ex. on a separate tab i have a sheet that has 2 columns in
column 1 is names and in column 2 is $$. Is there a formula i can enter on a
separate sheet so that it gives the answer but without spaces.

spreadsheet 1 spreadsheet 2
desired result
col1 col2 col1 col2
eric 20 eric 20
dave 0 clark 10
clark 10 mike 5
lori 0
stan 0
mike 5

any thoughts?

 

[Tonto]

is it possible to have an if statement or something that could return true
values in a list? ex. on a separate tab i have a sheet that has 2 columnsin
column 1 is names and in column 2 is $$.  Is there a formula i can enter on a
separate sheet so that it gives the answer but without spaces.

spreadsheet 1                              spreadsheet 2
                                                      desired result
col1        col2                              col1         col2
eric          20                              eric            20
dave         0                               clark           10
clark        10                               mike            5
lori            0
stan          0
mike          5

any thoughts?

seel vlookup in help

 

[T. Valko]

How many rows of data is there on Sheet1?

The numbers in column B, are they always positive numbers?

 

[Eric D]

there are 300+ rows and the numbers are not always positive

 

[T. Valko]

Assuming every name is unique and there are no empty cells in the $$ column.

Assuming your data is on Sheet1A2:B300

In the formula...

Name: refers to Sheet1!A$2:A$300
Amount: refers to Sheet1!B$2:B$300

On Sheet2 enter this formula in A1:

=COUNTIF(Amount,"<>0")

This will return the number of records that meet the criteria.

Enter this array formula** in A2 to get the names:

=IF(ROWS(A$2:A2)>A$1,"",INDEX(Name,SMALL(IF(Amount<>0,ROW(Name)),ROWS(A$2:A2))-MIN(ROW(Name))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down until you get blanks. If you will need to do this often and the
number of records will vary then you'll have to copy the formula to a number
of cells that you know will be enough to get all the results. You're the
only one that knows how many that will be!

Enter this formula in B2 to get the amounts:

=IF(A2="","",SUMIF(Name,A2,Amount))

Copy down to the same number of cells as the formula in column A.

 

[Eric D]

ok thank you so much ... i tried these formula's and they seem to work, but
the long formula returns a #VALUE but when i click on the "fx" beside the
formula bar to look at it, it says its returning a value that would be
correct but on the spreadsheet it is saying #VALUE. did i do something wrong?

 

[David Biddulph]

Did you remember the Control Shift Enter?

 

[Eric D]

ok i got it to work ... whooo hooo ... thank you

now to make it even harder ... is this possible if i am pulling from 2
different spreadsheets. i.e. i have sheet 1 and sheet 2 both with names and
amounts and i want the info to show up on sheet 3 ... is that possible?

 

[T. Valko]

I don't think you'll be able to pull from 2 sheets into one list.

You'll have to use side-by-side lists. One list for Sheet1 and the other
list for Sheet2.