• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

List people with Balance Due

scout

New Member
I have a list where some people have paid and others not. I want to create a sheet that has only the people with “0″ dollars balance. Columns I am choosing from are name, rank, balance. These are unsorted (order cannot be changed). I would like the “Balance Due” sheet to choose which person has a non-0 balance and list them in another sheet with their name, rank, and balance.


I know I need to use a IF statement with the balance column 0 (not equal to 0), but I can’t figure exactly how to return the corresponding name and rank along with the balance. I am thinking I will need to nest a INDEX or MATCH in there somehow.


Thanks in advance for speedy reply!
 

Hui

Excel Ninja
Staff member
Scout, I would add a helper column which will have an integer number which is either 0 or the maximum value above it in the helper column + 1 if the Balance is 0

ie: +if(balance col=0, max(helper col above)+1,0)


Then on the second page you can do an Offset and match to retrieve the individual Names, Rank and Balances for each item using the Row as the basis for the offset ie:

+offset(original data area, match(row no - fixed amount,helper column),column no)
 

scout

New Member
Thanks for the reply,


Not exactly following what you said, so I included a few rows of my 2 worksheets. The data is coming from 1 worksheet and I want to place the extracted data on another. I tried the helper column, but didn't understand all of it, so I messed it up and am getting errors. Can you re-enter what you had with the following colmns. Where do I put this "helper column?"


I have this (about 30 total rows) and would like to make another sheet (see format at bottom) that if their balance is >0, then put their name and rank on the new table.


Name Den Total Sales Amount Due Paid Balance

1 Fred Wolf $660.00 $660.00 $570.00 $90.00

2 Jack Bear $295.00 $295.00 $295.00 $0.00

3 John Tiger $555.00 $555.00 $130.00 $425.00

4 Tom Tiger $445.00 $445.00 $445.00 $0.00


Here is what I want to put it in. I don't need to have the columns of take orders, total, or paid, just need to show the balance if >0, along with their name and den. I would like this "automated" so when I enter more later, I don't have to use filters, etc.


Name Den Balance

1

2

3

4


Thanks for your help!
 

scout

New Member
Oh darn, it lost it's spacing. The 1st column is just the "number" of people, so that is column 1, with the other columns going from there.


Sorry!
 

Hui

Excel Ninja
Staff member
if spreadsheet Sheet1 is

1A B C D E F G H I

2 Name Den Total Sales Amount Due Helper

3 1 Fred Wolf $660.00 $660.00 $570.00 $90.00 0

4 2 Jack Bear $295.00 $295.00 $295.00 $0.00 1

5 3 John Tiger $555.00 $555.00 $130.00 $425.00 0

6 4 Tom Tiger $445.00 $445.00 $445.00 $0.00 2


ie: where I6 is =+IF(H6=0,+MAX($I$3:I5)+1,0)


on the report page you will have something like


A1 B C D

2 No Name Balance

3 1 Jack 0

4 2 Tom 0

5 3 0 0

6 4 0 0

7 5 0 0


where

C3 is =+IF(MAX(Sheet1!$I$3:$I$6)<Sheet2!$B3,0,OFFSET(Sheet1!$C$2,MATCH(Sheet2!B3,Sheet1!$I$3:$I$6),0))

D3 is =+IF(MAX(Sheet1!$I$3:$I$6)<Sheet2!$B3,0,+OFFSET(Sheet1!$C$2,MATCH(Sheet2!B3,Sheet1!$I$3:$I$6),5))


and I would use dynamic named ranges so that the ranges extend automatically when you add data
 
Top