removing duplicates; no specific pattern

I am trying to remove duplicates from an output that I have. Based on two columns I want to keep unique tax amounts. For example I know in this data set the number 14413855 has 4 tax amounts that total 1.97. There are 4 sets of this amount though and I only need one. Each number has a different amount of tax amounts. Is there a way that I can keep 1 single grouping for each number? Please see attached.

Example.xlsx

Datasets

Accepted answers

All comments

aatalai

@gduffy could you use the Summarise tool, grouping by number and tax rate, and summing tax amount?

aatalai

also look at the unique tool

gduffy

@aatalai I need the separate tax amounts unfortunately so cannot summarize. Also I considered using the the unique tool but there are some of the same amounts in one string that I need and will be removed if I use the unique tool

See example output!

@gduffy ---> are there columns you have omitted for data privacy? otherwise --- can you take a step back and explain how you identify which duplicates to drop and which to keep? This should be a straight forward group by columns x,y,z,a,b,c ---> where those are your key columns. That doesn't tie between your two files so either you have not delineated your logic, the second upload is wrong, or there are missing columns.

gduffy

There are omitted columns for privacy as well as rows because the actual data log is quite large. The three columns help with determining what to keep and what to drop. attached is the example output updated for what the starting point was that I uploaded.

Example output.xlsx

gduffy

apathetichell see above

apathetichell

@gduffy --- hey -- -this isn't working --- see 14412649 --- there are multiple values for .98 .0005 --> why? there is a reason you want to keep these multiple values. This is probably because of other columns. I'm not a mind reader. @abacon isn't a mind reader (probably --- again this is my a priori knowledge so it could be off). Here's the deal --> you figure out what columns you need and what are the determinants of a distinct entry. you feed them into the tile tool set up in UNIQUE VALUE mode. you click those buttons. you add a filter tool to filer only the situations where tile_sequence_number is 1. you look at the result.

Unique tool does the same but honestly --- I hate it - and I love the Tile tool.

gduffy

@apathetichell apologies, I can tell what I need to keep based off summing the tax amount column based on a the total of each grouping within the number column, if that makes sense. (ie 14413855 total is 1.97 in the tax amount column that needs to remain. in the example excel there are 3 additional groupings for this number group and I removed them in the example output.) I will continue to look into this. thanks for looking

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703