Forum Discussion
How to merge fields to perform math functions - imported from Amazon
On Amazon we sell many of the same item under several different ASINs. Once we import our monthly sales reports into Excel, we are trying to find a way to group those ASINs that correlate to the same item and then add, for example, total units sold for each and profit. That way, rather than having several different sales and profit values for the same item, they are already merged.
For example, let's say ASIN B01636FI6K and B01636FEXM are the same item. What we want is a formula to search for those particular ASINs and then add the #Sales for each.
| ASIN | #Sales | Profit |
| B01636FI6K | 5 | 15 |
| B01636FG44 | 2 | 20 |
| B01636FFKY | 1 | 18 |
| B01636FEXM | 4 | 25 |
| B07B68KR34 | 6 | 34 |
You might create a pivot table based on the data.
Add ASIN to the Rows area and #sold and profit to the Values area.
See for example Pivot Tables
- Thank you that seems to work! Is there a way that we can save the Pivot Tables and automatically run them for each month when sales reports are imported? The other issue is the number of Pivot Tables that would be needed to be run, as we have about 800 unique items and 2600+ total SKUs.
In that case you might need something else - perhaps PowerQuery. I hope that others can comment on that.
