Identify amounts that could be zeroed out

[savosean]

Hello, I have been struggling to try and find a solution to my problem. Or if there even is any.

Basically speaking I have about 50K lines of data, within this list of data I have names and amounts associated to it:

Example as follows :

John $30
John -$10
John -$20
John $53
Samantha $20
Samantha $67
Samantha -$15
Samantah -$5
Jerry $50
Jerry -$35

I have to identify which people I have the ability to clear a portion of their entries, therefore for John I can remove the +$30 and the -$20 and -$10. And with Samantha the +$20 and -$15, $-5.

This is for a data set of 50,000+ entries, therefore you can see why I may be trying to search for a faster method in doing this.

I have attempted to try and use solver, but I can't seem to get that to work and I am now at a loss. Any help would be appreciated it.

Thanks,
savosean

 

[admiral100]

Hi,

Why not summarize with a pivot table ....

[TABLE="width: 188"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Jerry[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]53[/TD]
[/TR]
[TR]
[TD]Samantha[/TD]
[TD="align: right"]67[/TD]
[/TR]
</tbody>[/TABLE]

 

[savosean]

Would a pivot table allow me to identify which ones have values that are zeroed out. Like would I be able to see within my pivot table data that Jerry has other entries that were equal to 0. Because if I cannot view those then it wouldn’t be of any help to me since that’s the same as the subtotal feature.

 

[admiral100]

See if this might help you

https://www.get-digital-help.com/20...d-negative-amounts-that-net-to-zero-in-excel/

 

[savosean]

See if this might help you

https://www.get-digital-help.com/20...d-negative-amounts-that-net-to-zero-in-excel/

Appreciate your help this is exactly what I tried to do with solver, the problem is it doesn’t work with the fact that I also need it to understand which transactions are associated with certain names. As solver only does the one column.

 

[admiral100]

I think it's a start ... pay attention to the instructions so that the formulas work as they should


1. Sort by Name (Level One)
2. Sort by amount - from largest to smallest (level two)
3. C - Helper column
4. D - Unique list of names


Ctrl+Shift+Enter NOT just Enter


C2 =MAX(IFERROR(MATCH(TRUE,INDEX(MMULT(--(ROW(B2:$B$11)>=TRANSPOSE(ROW(B2:$B$11))),B2:$B$11)=0,,),0),C1-1),0)
D2 =IFERROR(INDEX($A$2:$A$11,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$11)),0),"")
E2 =SUMPRODUCT(--($A$2:$A$11=D2),--($C$2:$C$11=0),$B$2:$B$11)


[TABLE="width: 429"]
<colgroup><col span="4"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD]Unique List[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jerry[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]Samantha[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jerry[/TD]
[TD]-35[/TD]
[TD]0[/TD]
[TD]John[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]John[/TD]
[TD]53[/TD]
[TD]0[/TD]
[TD]Jerry[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]John[/TD]
[TD]30[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]John[/TD]
[TD]-10[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]John[/TD]
[TD]-20[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Samantha[/TD]
[TD]67[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Samantha[/TD]
[TD]20[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Samantha[/TD]
[TD]-5[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Samantha[/TD]
[TD]-15[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[savosean]

This is really good stuff, thank you. I am currently working on implementing it.

 

[SpillerBD]

All I do is add a helper column that indicates the ABS() of the values I need to compare and then sort with the appropriate grouping.
A Conditional filter can be used to highlight where values match, but because three values with the same ABS() result can't all clear, it still requires some human factor.

 

[savosean]

Would you be able to elaborate on the ABS() please? As for the formula provided by admiral100 unfortunately it doesn't work correctly if the values matching values are not right beside you for example : top-bottom 45,4,-6,-45... it will not show that the two 45's can be cleared as they are not directly beside each other.

 

[SpillerBD]

ABS(Value) is used in a Helper column.
ABS(-45)=ABS(45)=45
Sorting by the ABS() value will put like values +or- next to each other.

An additional helper column may be needed if there is a need to return the data to the original list-order.