Forum Discussion
Return only one instance of value for a repeated Item Codes in Excel
Hello,
I have a large data set at the transaction level. For context, let's start with Item Code#1234. In the month of January 2025, there may be a 1,000 recorded transactions for this 1 Item Code. However, I would like to bring in just one instance of Advertising $$for Jan 2025 for this Item. For example, assume that Item Code# 1234 has an allocation of $11 in Advertising spend for Jan 2025. How do I now bring in just one instance of this $$ for Item Code#1234 when there might be 1,000 recorded sales transactions in the month of January for Item Code#1234? Hope you can help.
You want to avoid double-counting ad spend for an item that has multiple transactions in a month. Essentially, you want one value per item code per month, regardless of how many rows there are for that item, if I understand correctly.
You can use UNIQUE and FILTER (Excel 365 dynamic arrays)
If the value truly never changes within the month for an item, MAXIFS or MINIFS is the easiest and fastest.
If there’s a chance values differ and you want the first instance only, UNIQUE(FILTER(...)) is more precise.
hope this helps you
2 Replies
Have you considered creating a Pivot Table?
Item code in the Rows area and the sum of $$ in the Values area. In case your transactions span multiple months, put the date in the Columns area. Excel will then aggregate everything by month with grand totals for each item code and total spending by month for all item codes. Just an idea.
You want to avoid double-counting ad spend for an item that has multiple transactions in a month. Essentially, you want one value per item code per month, regardless of how many rows there are for that item, if I understand correctly.
You can use UNIQUE and FILTER (Excel 365 dynamic arrays)
If the value truly never changes within the month for an item, MAXIFS or MINIFS is the easiest and fastest.
If there’s a chance values differ and you want the first instance only, UNIQUE(FILTER(...)) is more precise.
hope this helps you
