@randy, after years of struggling with categorizing Amazon transactions in Tiller I finally thought to search for an existing solution, and found this. Thank you, it is really helping me and works very well, like everything else of yours I have used.
In case you are still maintaining it, I had one problem I want to describe. (I hope this is the right discussion among the several on this issue to post this, it seems like the most current one.)
I made a mistake and ordered some items I needed quickly to be shipped to my daughter across the country. When I realized this a day later, I placed an identical order to be shipped to me. When I first imported the CSV, only one of the two orders appeared. I then made a short CSV with just the second order and imported it, and that time it worked. The orders are distinguishable by having different order IDs and shipping addresses (and some other fields), though the contents are identical. If youāre interested Iāll be happy to send you a 7-line CSV with the two orders.
This is not a very common situation and was easily able to fix it, so no need to give this high priority. Thanks!
Thanks for the feedback. Iām glad to hear the template is still helpful after all of these yearsā¦ itās celebrating itās 3.5 year birthday!
I agree that Amazon workflows have been frustrating. Theyāve changed the format CSV format multiple times and have made it harder to download order data/history.
Iām afraid I donāt have a great solution for de-duping multiple identical orders. We often discuss this as a team more in the context of deduping transactions. The prototypical scenario is: I go to Starbucks order a short latte, then, as I leave, order a second identical coffee. How can a computer know that this is an intentional duplicate versus a redundant record?
Are you saying that the workflow should do more with the Amazon Order Id?
@randy One of my banks is not processing transactions right now so Iām reverting to this amazing workflow - but since last used I added a Tag column with an ArrayFormula (arrayformula(IFS($J$2:J=āAmazonā, āIraā,ā¦) which checks $J$2:J and assigns a tag based on Account names in column J, starting with row J2.
When I run the workflow and it inserts new rows / transactions, my arrayformula moves down and no longer produces correct results. Is there a simple fix Iām not thinking through? Appreciate any help. Thanks.
Wrap the range with an INDIRECT string to prevent the range from changing when rows are inserted above the range: INDIRECT("$J$2:J")
If your ARRAYFORMULA is in the row 2 cell, move it to the row 1 cell, something like: ={"My Tag";ARRAYFORMULA()}
And keep in mind that Transactions sheet column name Tags (plural) is a reserved column name for Tiller tools. That could be a little confusing, if your column name is Tag.
Hi Randy, thanks for your prompt response. Boy, I wish Iād searched for this 3+ years ago!
I havenāt examined a lot of Amazon data, but from what I have seen they do use Order ID correctly: every order seems to have a unique Order ID and every item in the order seems to share the same Order ID. In my case above, the two orders with identical contents did have different Order IDs. Based on this, I would advocate doing duplicate detection of orders based on Order ID alone (which I expect would be very easy to code). If thereās any uncertainty about this approach, a possibility would be to offer the user a choice between the current approach to duplicate detection and use of Order ID.
