treeleaf20
Board Regular
- Joined
- Mar 17, 2009
- Messages
- 159
- Office Version
- 365
- Platform
- Windows
I have the following entries in a spreadsheet:
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Date[/TD]
[TD]Days[/TD]
[TD]Total Balance[/TD]
[TD]Used Balance[/TD]
[TD]Booked Balance[/TD]
[TD]Remaining[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]4/25/2019[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]4/26/2019[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]1/2/2019[/TD]
[TD]1[/TD]
[TD]37[/TD]
[TD]3[/TD]
[TD]32[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]1/3/2019[/TD]
[TD]1[/TD]
[TD]37[/TD]
[TD]3[/TD]
[TD]32[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]1/4/2019[/TD]
[TD]1[/TD]
[TD]37[/TD]
[TD]3[/TD]
[TD]32[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
I would like the output to be something like this then:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Total Balance[/TD]
[TD]Used Balance[/TD]
[TD]Booked Balance[/TD]
[TD]Remaining Balance[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]22[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]37[/TD]
[TD]3[/TD]
[TD]32[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
Basically, I'd like to only select a unique row where the total balance, used balance, booked balance, and remaining balance aren't the same and then sum those selections together.
In the Name1, you can see the two rows that aren't unique so when I sum them I get the output. For Name2, all the rows are unique so I would only like to select the one row.
Any ideas on how to do this with a formula?
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Date[/TD]
[TD]Days[/TD]
[TD]Total Balance[/TD]
[TD]Used Balance[/TD]
[TD]Booked Balance[/TD]
[TD]Remaining[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]4/25/2019[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]4/26/2019[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]1/2/2019[/TD]
[TD]1[/TD]
[TD]37[/TD]
[TD]3[/TD]
[TD]32[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]1/3/2019[/TD]
[TD]1[/TD]
[TD]37[/TD]
[TD]3[/TD]
[TD]32[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]1/4/2019[/TD]
[TD]1[/TD]
[TD]37[/TD]
[TD]3[/TD]
[TD]32[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
I would like the output to be something like this then:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Total Balance[/TD]
[TD]Used Balance[/TD]
[TD]Booked Balance[/TD]
[TD]Remaining Balance[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]22[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]37[/TD]
[TD]3[/TD]
[TD]32[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
Basically, I'd like to only select a unique row where the total balance, used balance, booked balance, and remaining balance aren't the same and then sum those selections together.
In the Name1, you can see the two rows that aren't unique so when I sum them I get the output. For Name2, all the rows are unique so I would only like to select the one row.
Any ideas on how to do this with a formula?
Last edited: