Can someone help me with this? I have sales transaction data on one worksheet from the ERP with individual SKUs and bundles, and then I have a second sheet where I have all the components of each bundle. Is there a way to create a sales data table by individual SKU and bundles get rolled up into each SKU. Thank you for your help.

Post Photo
like
Posting as :
works at
You are currently posting as works at

Go ask r/excel and say some really incorrect solution. Someone will try one-upping you

funnylike

Shockingly this is actually really good advice and I have done a similar thing in the past over at r/powerapps lol

like

Can you put an example of how you’d like the output to be. Coz I’m not sure I followed you well

like

Just wanna make sure I followed do you mean bundles as in BOM (Bill of materials)?

like

Looks like you have 3 skus. 1 is product A, 2 is product B, and 3 is product A&B? So you’re trying to show if sku 3 sells 1 unit is is actually 1 of sku 1&2?

like

Thank you, I thought it would be an easy solution as an ERP system report. I Will ask thé project lead to look into it.

Is SQL an option? If not, powerquery both sheets into a single workbook and build a data model in excel.

like

Please show screenshots of sample of input data and desired result, from there it will be easy to give accurate answer

like
Post Photo
like

Step 1: Identify the maximum number of "components" a single SKU can have

Step 2: in either Sheet 1 or a replica, create additional columns with components and quantity (similar to sheet 2 table); where the number of columns will be equivalent to the number of step 1 x 2 (since for all "components" you also have "quantity"

Step 3: Assuming you only have unique SKUs in sheet 2, do:
=FILTER(table_2_range,(table_2_SKU_range=table_1_SKU))

Step 4: perform any operations you may need in this new table - which is basically a copy of the table in sheet 1, but with additional columns with the components presented in table 2

like

Np - good luck! If you need help with the formulas, feel free to reply :)

like

You can use PowerQuery in Excel, first step you need to transform your Bundle details data by SKU one rows for each component, then perform a merge query / join and then add in additional calculations

like

Thank you…I will brush up the power query skills over the weekend :)

Ask ChatGPT

Thanks but it didn’t work.

How do you not know how to do this…

Related Posts

Hi Fishes, I have 2.2 yoe in Java, Spring, Springboot, Microservices, Mysql and Ci/CD I have 2 months of notice period please refer my profile if you have any opportunity for the same skillset. Please DM/Comment. TIA

I have an interview for an AM position at a prop tech company. It’s my first AM role after doing sales. Is it common for AM’s to not receive any further compensation outside of their basic salary (so no commissions, bonuses or equity).

like

Which is your favourite Use case so far in your Data Science journey?

like

Hi All ,

I am joining Accenture applied AI in Pune as my joining location. Will they ask me to relocate to Bangalore or Mumbai ?

like
like

For a top Producer of 3 years, is it appropriate to now ask for a raise in my base pay? Base was 42,500 when I started, but now inflation + 2 children later the base pay barely gets me by. Any thoughts are greatly appreciated!

like

Hey investors - question from the other side: when you’re looking to invest, what are the biggest components you look for?

like

Has your company announced when you’ll be going into the office? It’s strange to me that no one has announced if perm wfh would be a possibility. Feel like I’m in a state of limbo and want to be able to plan to move somewhere else if that’s a possibility.

like

Hello everyone,

How is Siemens GBS? Is it a Service Based Vertical? How about Job Security?

like

Is it weird that I hate attending work happy hours because all people do is talk about work?

like

Once you hit your FI number, what are your plans? 1 yr? 3-5yrs? beyond?

like

Going through a miscarriage at the moment. Was 5 weeks-ish in and did three days of HIIT workout and here I am. I’m young so not that sad but I just hope I don’t have to get the D&C surgery. :(

like

I’m having a really hard time with my changing body. 13 weeks, I don’t have too many bad side effects of pregnancy or anything so I consider myself lucky (no nausea ever etc) but I’m depressed at work, my skin is completely broken out and showing new broken face capillaries, and I’m not showing a bump yet so I just feel fat and big. I don’t know where this “glow” is but I look in the mirror and literally am grappling with not recognizing myself :( it’s been impossible to find any clothes too.

like

Im working at an agency where the strategists expect to see all the work before the creative directors review it. They all pretend this is normal and I've never experienced this anywhere else?

funnylike

Is 54000 pounds a good salary to stay in London? As an EY seren senior consultant?

YOE: 3.5
Current package in India : 25+ LPA

like

Hey everyone,
I just applied for
Citi bank for an AML compliance analyst position. It’s a hybrid position in Tampa FL. Any feedback on the company, salary, Company values, benefits please and thank you. Citi

like

Hi does hsbc gives joining bonus how much salary band we can expect for gcb 5 ? This is my 3rd post on fishbowl but not a single reply I am getting ..

like

What opportunities would I have in law enforcement without going to college?

like

Why is the RSM bowl so dead compared to GTs? I'm about to join from GT and wondering if things are really that fine and dandy here?

like

Additional Posts in Excel Genius

Anybody use a personal computer for work? How's your experience been, any horror stories/watch-outs?

like

How can I extract just the year into a new cell from a “yyyymmdd” cell (labeled PD here) that isn’t recognized as a date? I basically need to sort this data by year. Thanks in advance!

Post Photo
like

Can dates be grouped just by Month-Year in pivot tables on a Mac without having the year as its own dimension?

Post Photo
like

Hi excel geniuses, how do I quickly copy info from one cell to blanks for same if. Semi below I have products and names, for the blank fields w same product how do I quickly copy for the rest of the blank cells?

Post Photo

This is dumb, but. How can I pull and average the numbers in column b associated with the 4 values in column a? So, (sum of all As/total number of As) without filtering?

Post Photo
like

Timesaver: If you’re working on a file saved on the cloud (teams, share point, etc.) and need the URL to communicate out, tap FILE -> INFO -> COPY PATH and the url will be on your clipboard. Pre-established sharing privileges of orig folder will apply. Hopefully this saves you time reshuffling open windows! Bonus: Same on standard MS suite files.

likesmart

Is there anything similar to the INDIRECT function that I can use to build a formula reference to another workbook? INDIRECT creates errors when the referenced workbook is closed..

like

I’m trying to learn more about Excel functionality and there’s several simple functions that I don’t know, this is probably one. If I am color coding data, is there a way to automatically move those lines to the top or bottom of the spreadsheet once the color code for the line is made?

like

Would anyone be willing to teach me vlookup?

like

Hey, I have been tasked with teaching some of my coworkers excel from the very basics to more advanced. Does anyone have recommendations on the skills to teach them about? Thank you all in advance!

helpful

What are your top Excel ninja tricks? Ones that few people know...

like

Does 2-finger touchpad scroll not work on Excel sheets anymore? I used to be able to swipe quickly on my laptop's touchpad to navigate sheets but now it doesn't work. Based on the handful of posts I've read online about it, the problem seems unresolved. Has anyone dealt with the same issue and fixed it?

What’s the keyboard shortcut to get into the “insert options” dialogue when you’ve inserted a new row with Ctrl+Shift+Plus?

like

I’m helping my GF with excel and need help lol. She works at a hospital and is trying to make an excel schedule for doctors to reserve rooms. She wants drop downs for days of the week (column K) and also different drop down menus in column P for the reserved time slots.

Is it possible to do this with two sets of drop downs (days of week + time slots) and not use index match here? She could make 5 tables for each day of the week, but prefers one table that can be easily navigated by doctors.

Post Photo
like

Our Data is only showing when something has changed, is there any way to make excel fill in the values?

Post Photo
like

Best free data sites to tap into to build a city analysis model? Basically I want to "discover" cities with certain dimensions, like: population, elevation, climate, and proximity to other things (ski resorts, particularly)?

like

How long did it take you to become good at excel? Like fast?

like

How do I set the formula to calculate “If x falls between 1-10, return 1-10, if it falls between 11-20, return 11-20, and so on”

like

How do I compare two sheets to make sure they are identical?

like

Anyone know what these badges are?

Post Photo
likefunny

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Scan your QR code to download
Fishbowl app on your mobile

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal