2 Ways to Solve Duplicate Transactions on Amazon Sales Tax Report

0:32 Downloading the combined sales tax report

I'm going to show you guys two ways to do this filter. This is the Amazon orders report after you download it. It is the combined report that you can download from Amazon Seller Central, which is right here. You will click on generate a report, and then click on the combined sales tax report, then select the month that you want to report from, then click on generate. And after you do that you can click on download tax report, and after you open that it'll be like this. I've highlighted the columns that I think are particularly important.

1:07 Why one order appears four times

And as you guys can see, this particular order 114 1902003, this one is duplicated one, two, three, four times. And the reason is, these are all the same information for the same transaction, and tax is collected and calculated by Amazon for $17.

And the reason that this is duplicated so many times is because on the jurisdiction level they are calculating it on the state, city, county and district level. And because they're repeating all of these and calculating the tax for all of these different ones, and when these all add up, it adds up to the 17 right here, but all of these are showing zero.

2:01 Method one, filter by jurisdiction level

So the easiest way to fix this, it's just a simple filter you can add to your pivot table, and I'm going to show you right here.

So same process like we've talked about in the video before. You will click on this arrow, that's not arrow, this little triangle that selects everything on here, and go to insert and go to pivot table. And then select everything, new worksheet, that's okay. And we've done this before, so if you need a refresher go to my other video.

But here we will do the ship to state, which is all the states that we want to get information for. And then we will do the tax exclusive selling price is still the value. But note that these values are really big. It's bigger than what they should be, because it's duplicated many, many times. Because on here, even though the 17 is only stated once, my exclusive selling price is stated four times, but I only want them to stay once.

3:12

So then the easiest way that I've found is to add jurisdiction level. If you go back here, because this one, we only want it to calculate the 17 and there's 289, and this is on the same line as the state. So we only want to include state on this jurisdiction level.

So what we're going to do is jurisdiction level, you're going to drag it and drop it on filters, and then under filters you can select state. And then this way it gets rid of all the duplicated transactions.

3:58 Checking the numbers manually

An easy way to check is, let's make this prettier, so we'll use AL, Alabama, so just the abbreviation, because some people spell it out, some people leave it abbreviated, so we'll use this abbreviated way.

And let's go, and we'll do a manual check. So we'll go to data and click on filter, and ship to, let's select AL. And here we can see again all of these are duplicated again, and we don't want that. So then if we go to jurisdiction level and we only select the state, then it gets rid of all the duplications, and we can sum up all of these numbers, and that's $6,521.53, which matches this one exactly.

So again, the easiest way to get rid of the duplication is to add this jurisdiction level under the filter, and then click on this button and only select state. When you do this, then that'll get rid of all the duplications, and then you can proceed to the way that you were doing it before.

So that was one way to do it, and I found that the easiest way. But if you want a super clean way, I'm going to show you the second method of getting rid of those duplications.

5:27 Method two, filter blanks and paste values

Okay, so this is the second method of getting rid of those duplications. So we will go over here, total tax collected by Amazon. I want to filter out all of these that are blanks. So I would click on this and go to data, go to filter, and then scroll all the way down and unclick blanks, so then it won't show any blanks. And none of these are duplicated.

But you can't just insert a pivot table based on this, because the filtered data are still going to be in the pivot table. So what we can do is you click on this triangle and then click on Ctrl C to copy all the data, and then go to a new tab, and then you want to paste value. So you paste all these data that's already been filtered, and then you insert a pivot table based on this new one.

And we'll do the same, let me move myself, we'll do the same of going to ship to state, which is right here, and tax exclusive selling price. And we'll use AL as an example again, and you can see it's $6,521.53, which is exactly the same from the first method.

6:52

So to recap this one, step one is to filter out blanks. So right here, tax collected by Amazon, you want to go all the way down and click blanks so it doesn't include blanks. And then you select all of it. You can right click, you can say copy, and go to a new tab and right click, and instead of just paste you want to say paste values. So then it gets all of the numbers in here without any formula. And then since it's already selected you want to do insert pivot table on this one. And then everything else is the same.

7:39 Questions and next steps

I am so glad you guys are asking me those questions to help me realize what other information I need to put out there. If you have more suggestions or questions that you have about sales tax, feel free to drop it in a comment. Subscribe to our channel so every time I publish a good video like this you will get notified first hand.

And if you're an ecommerce seller that has over 2 million in your sales revenue and you want someone to take sales tax off your hands and your accounting, feel free to contact us at ledgergurus.com. And I'm so excited to see you in my next video. Bye, thank you.