•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Assigning one value to multiple unique IDs?
Our take
Navigating complex spreadsheets can be daunting, especially when you need to calculate billbacks at the item level across multiple unique IDs. In your case, with Table AL3 featuring various price points and chargeback amounts, leveraging functions like LOOKUP, INDEX, and MATCH can significantly streamline your process. By aligning each unique value to the corresponding IDs, you can transform your current table into a more manageable format, yielding the 120 unique rows you need.
I recently inherited a monster of a spreadsheet and I'm working on a project where I need to calculate billbacks down to the item level but at the moment they are calculating at the Table level.
Each table can have multiple price points and billback amounts.
Ex) Row 1 - Table AL3 is being sold at $35 and the charge back amount is $5
Row 2 - AL3 is being sold at $35.50 and the charge back amount is $2.50
Table AL3 contains 40 unique values that I need to match with each of the 3 unique IDs above, returning 120 unique rows. Hopefully that makes sense..
I'm currently at a loss regarding how to go about this. I read that using a lookup, index and Match functions might work but I was not able to work out exactly how.
Any help at all would be greatly appreciated!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Having issues counting unique instances of pick up and drop off locations based on if money is in the same row.I have a contract I'm working on for work so I will give what I can to see if someone can help. We have roughly 62k rows of information. It is a combination of pickup and dropoff locations. There are 9 pickup locations with various dropoff locations. Each pickup and dropoff are repeated 3 times based on another column. What I am looking to do is on a summary tab I want it to reference the origin location go to the full data sheet and count each row that has that origin with money and count only the first time the pickup and drop off location appears. I'm not sure if this is possible or if I just need to manually counting. For reference I've tried a combination of ROW, UNIQUE, and FILTER with various COUNTIFS or IF statements but nothing has worked. Even resorted to asking AI but either my prompts aren't correct or it just doesn't understand. Thanks in advance for any advice or tips to try! submitted by /u/Reaper4779 [link] [comments]
- Returning multiple matches per ID (auto-fill when dragged down?)Hi there! I have multiple assigned IDs for a single code so I need to return all matching values, not just one. I also want this to be kind of automated (?) when I drag down the upstream ID column, it can pull all corresponding values, even if they share or don't share the same code I tried using FILTER, but it only spills results in one place and doesn’t carry over properly when dragged down for other rows. I don’t want to adjust ranges every time manually since my data might get longer. Is there a way to make a formula that: works when dragged down, returns multiple matches per ID, and still updates properly even if the data goes beyond the current rows? Left Side = source workbook; Right Side = destinatioon workbook Thank you so much in advance! submitted by /u/jeankrstein [link] [comments]
- Matching Numeric Data across two columns?What I thought would be a simple formula has taken me over 2 hours of searching and no results as yet.. So im reaching out for help please. Heres where Im at. - Using 16.108.2 version - I have 2 sets of data of differing row amounts. There is one numeric column in each set that definitely has matching numbers across them. I need to see which numbers are matching and which numbers aren't. - I need to align those 2 columns where the numbers are matching, and also keep all other information aligned across the columns that are alongside the 2 I am matching. I have tried the XLOOKUP formulas that I can see listed when i do a google search, but i cant get it to work, either some information or step is being left out, or I am finding the wrong info on how to do this. Can anyone provide a step by step on this please? submitted by /u/Sentallmentall [link] [comments]