I have 2 sheets, let's call them sheet 1 and sheet 2.
In sheet 1, I have some data where essentially at its core its a Scoring tally, something like this:
Name | Age | Gender | Score |
---|---|---|---|
Jack | 12 | Male | 155 |
Mary | 67 | Female | 0 |
Jill | 16 | Female | 61 |
Sarah | 44 | Female | 219 |
Peter | 5 | Male | 0 |
Nathan | 32 | Male | 0 |
Greg | 25 | Male | 44 |
I want to then create a new table in sheet 2, where I collect all the rows that have a score greater than 0, and only the Name and Score columns, like this:
Name | Score |
---|---|
Jack | 155 |
Jill | 61 |
Sarah | 219 |
Greg | 44 |
I want this to be continually updated, so for example, if Mary gains a few points, I would want the table to look like:
Name | Score |
---|---|
Jack | 155 |
Mary | 21 |
Jill | 61 |
Sarah | 219 |
Greg | 44 |
And if any newcomers come, and they have more than 0 points, I would also like that to be added. For example if Luke joined:
Name | Score |
---|---|
Jack | 155 |
Mary | 21 |
Jill | 61 |
Sarah | 219 |
Greg | 44 |
Luke | 98 |
I am not really sure where to begin with this, I am working on google sheets, and have tried to do INDEX-MATCH, but my issues are that match doesn't seem to do greater than nicely, I can't seem to choose the pairs of column I want displayed, and I am also not sure how to get it to continuously update. I would prefer a solution with no use of VBAs, and if possible to just keep it to formulas in the cells.