Related Posts
Conversation Starter
Conversation Starter
Bowl Leader
More Posts
Hi Fishes, I am planning to switch my profile to Google Cloud.
Current YOE: 6 Current Tech Stack: C++
Please help me to kickstart my journey. Where to start what need to be studied/practised. Thanks in Advance.
Google Capgemini Tata Consultancy Tech Mahindra Wipro Cognizant Infosys CTS cts Accenture Jio Atos HCL Technologies Mindtree NTT DATA
Visual Storyteller
HCL Technologies offer being delayed and recruiter keeps saying that there's a problem with their "Offer release portal system." I have already stated my issues with relocating until after I have the offer signed and ready. The project is supposed to start on the 19th and I'm supposed to join before, or on that date. It's already coming up soon and I don't have the official offer so that I can confidently start the relocation process. Has anyone else recently dealt with this?
What’s the best swag you’ve received?
Conversation Starter
Mentor
Additional Posts in Excel Genius
To be honest, in the absence of software, I would put this in a SQL database and write a few scripts to get this done
Using basic pivot and lookups I have already found the total child items and their corresponding units to fulfill the parent items demand. I recorded a macro and now it’s doing the job for me. Now I am trying to find out the child items that comes under each parent item and their corresponding units to make 1 parent item. I am trying to create a PL for all the Parent items.
Another option is to Google excel solutions and formulas for maintaining supply chain supersession or bills of materials data. This is a very common supply chain problem, so you might be able to find some excel resources online.
Keep in mind that Excel has a 6-deep nested IF statement limit. Don't do it that way.
Subject Expert
If by some chance you really need to do only excel, it is possible, although complicated. Here is a short version:
Step 1 - you need to compile in every row the children and the mother (or mothers). You will use this to identify the rows for the second table
Step 2 - create a unique if formula with wildcard. Basically, you will use =FILTER() to filter all identifiers that contains the pare t you're looking for (ex.: AB123F and you're looking for "B" - you will then ask the filter function to look for *B*, so he looks at the string and see if there's a B). Then you use unique to make sure you're not doubling lines
Step 3 - unpack the identifier. This way you can recreate every parent and child on that row
Step 4 - use =SUMIFS() to calculate the quantity required for each item
-------
For the second table, the process is similar, but you only need to extract the first parent.
Step 1 - create your Identifier
Step 2 - use =UNIQUE combined with =FILTER() to select your the onde you want to see
Step 3 - use =SUMIFS() to calculate the desired quantity
P.s.: you can automate the =FILTER() formula so you can do it for every parent at once
Yes you are right, it’s complicated 😞
How big is your company? There’s a lot of software that will just do this for you…
It’s a big one but unfortunately this department is currently running on excel and their existing process is time consuming and complicated.
In the absence of SQL or software, let me confirm something: am I correct in reading that the same item can be both a child and a parent? For example, item E.
Yes an item can be a standalone Parent that contains 100% child items such as 1,2,3, etc etc or a parent can be a child item for another parent item, this is where you have complication starts.
A can have 1,2 & B as it’s child items, in which 1 & 2 are pure child parts but B is a parent .
Now to make 1 B , you might need 2,3 & C child items - in this 2 & 3 are pure child and C is a parent, now you have to further explode C to its last child item.
Like this I have few items where you have to go 8 layers deep to find the last child item for the original parent item.
Another question: and in what format do you have the data now? The table all the way on the left?
Yes
You can do this with solver
It’s not that I have only 50 lines, in total I have 250,000 lines items
It’s sort of like a market basket analysis