Тёмный

Excel - BOM: Determine Parent Part from a Structured List 

Michael Humphreys
Подписаться 463
Просмотров 50 тыс.
50% 1

Using REPT & LOOKUP, this is part of the Advanced Excel training series which goes over how to format a Bill of Materials that is being uploaded to an ERP system (an LSMW for SAP in this case). The formatting includes adding indentations to the part number (REPT function) and also indicating the closest parent by searching a column from bottom to top (LOOKUP).
The LOOKUP is a useful trick for searching from bottom to top rather than the typical VLOOKUP which searches from top to bottom.
Excel File:
feito.s3-us-we...

Опубликовано:

 

27 авг 2024

Поделиться:

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист
Посмотреть позже
Комментарии : 20   
@thephilsweet
@thephilsweet 5 лет назад
Good grief, I have just spent two days trying to write a recursive vba function that traverses from top to bottom, and you have achieve the same with a single line in Excel. Brilliant, many thanks.
@redmust
@redmust Год назад
This is awesome. Well written. Solved my problem in a very short time. Many Thanks !
@halfpast45
@halfpast45 3 года назад
Its has been 5 years, glad to found this video, nice man! 👍
@steelforbrains
@steelforbrains 5 лет назад
Awesome. I did something like this a couple years ago and it was complicated as hell. I needed to do it again and this tutorial was great! Thanks!
@xyz123770
@xyz123770 8 лет назад
Wow. What you demonstrated was very,very similar to what I needed to accomplish. I was able to copy your formula - just changing the target cells - and automate what had been a very time-consuming and laborious process. In truth I don't even understand the subtleties of how the formula works, but it kicks out exactly what I want it to. Thank you so much.
@minarendra
@minarendra 3 года назад
Amazing Formula , Thanks for explaining it in detail.
@QuestForFun1
@QuestForFun1 4 года назад
Hi Michael You have amazing Skill in BOM and Excel (combination is Rare). Looking forward to see more videos on your channel topics related to BOM and Excel. (Viewer from India)
@jobychacko123456
@jobychacko123456 6 лет назад
Michael This was very helpful .Thanks for sharing
@jezdel6298
@jezdel6298 3 года назад
Hey. This is the function that I REALLy need to use. The end result is what i’m trying to achieve. The only drawback is that the way you explain it is so technical. Can you explain it in a way that is easier to understand or “user” friendly for dummies like me. Thanks.
@fredericboulay7299
@fredericboulay7299 5 лет назад
Thanks a lot, that might be very helpful
@Kishorshinde27
@Kishorshinde27 9 месяцев назад
Hi, how do I make hierarchy for bom
@Silverwolph
@Silverwolph Год назад
Super video! Can you also have it show multiple parents of the number it 2 or more places in the entire bom structure? So when you move down the list and you reach 12345 for the 2 time, but this time the parent is B, and next time the parent might be C. How would you handle this???
@Zosius
@Zosius 7 лет назад
Thank you so much. This was very useful! I got another puzzler. If I have a price of each sub component (lvl4), is it possible to get the overall cost of parent component (lvl3) and so on? I have hard figuring this out without manually setting ranges to sum. SUMIF with condition also does not work, because same components repeat in many occasions.
@falconhazard
@falconhazard 4 года назад
That's a good point! Have you got the solution for this already?
@maxfactorone
@maxfactorone 9 лет назад
Hi Michael, Any chance you could do a video on summing quantities in a bill of materials. In your video above for instance row 16 is for 26 off Block, Terminal Gray so if the level above was for 2 off instead of 1 off, the total quantity required would be 52. Also this Block may be required in multiple places throughout the BOM. Is it possible to have a formula to calculate the total quantity required. Thanks, Darol
@x68507
@x68507 9 лет назад
+Darol Wilson Sorry for the late reply, but I like your question. It was a little bit of a brain puzzler, but the solution is actually pretty straightforward and is basically what you've already done to get the PARENT part in column G. I created a new "dummy" column just to the right of SAP_Qty (column I) and labeled it Order_Qty. Next, you use the same equation as in column G, but replace the $E$2:E16 with $J$2:J16 (this is now the "parent order quantity"). Then, you have to multiply the "parent order quantity" with the "component quantity" in column I. The final equation you should put in J2 and copy all the down would be: =IFERROR(LOOKUP(2,1/($D$2:D2=(D2-1)),$J$2:J2),1)*I2 See the Master_v2.xlsx at this link: drive.google.com/open?id=0B2nEQjKE2EhxVEJyUlcwQ1I3VjA
@maxfactorone
@maxfactorone 9 лет назад
+Michael Humphreys Hi Michael, Thanks very much for your reply. I'm really busy at the moment so haven't had chance to try your solution. Hopefully I'll get to it at the weekend. Many Thanks, Darol
@getitdone6255
@getitdone6255 6 месяцев назад
Hello, without your V2 sheet I spent 20 minute to understand, and it WORKS ! Thank you man you are one of in a million.
@maxfactorone
@maxfactorone 9 лет назад
Brilliant.... Thanks :-)
@Scars_13
@Scars_13 6 лет назад
This is the amazing formulas to see the relationship of BOM. I actually have issue about how to concert BOM from excel to pivot report. Do you mid if I would like you to advise me for my excel case
Далее
Make a Search Bar in Excel to Find Anything!
10:35
Просмотров 352 тыс.
ПОЁМ НАРОДНЫЕ ПЕСНИ🪗
2:04:11
Просмотров 1,1 млн
multi level bom explosions in excel using vba
13:20
Просмотров 11 тыс.
Excel - Userform Placeholders
18:16
Просмотров 17 тыс.
BOM comparison v2 | Tutorial
32:22
Просмотров 680
Beginning Engineers Bill Of Materials
4:11
Просмотров 75 тыс.
3 Essential Excel skills for the data analyst
18:02
Просмотров 1,5 млн
5 Excel Secrets You'll Be Embarrassed You Didn't Know
17:32