Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-06-2021, 04:07 PM
Dual Trace Dual Trace is offline Formatting Bill of Materials Windows 10 Formatting Bill of Materials Office 2010
Novice
Formatting Bill of Materials
 
Join Date: Jan 2019
Location: Beaverton, OR
Posts: 4
Dual Trace is on a distinguished road
Default Formatting Bill of Materials

How can you eliminate duplicates in a bill of materials while also showing the reference designators separated by commas?
The image shows a simple example of what I am after. Starting with the table on the left, how to make it look like the one on the right?
Thank you.
Attached Images
File Type: jpg Capture.JPG (47.9 KB, 15 views)
Reply With Quote
  #2  
Old 05-06-2021, 11:21 PM
ArviLaanemets ArviLaanemets is offline Formatting Bill of Materials Windows 8 Formatting Bill of Materials Office 2016
Expert
 
Join Date: May 2017
Posts: 640
ArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of light
Default

An example how organize data entry and get bill details table for any chosen bill.

Columns in Tables having formulas are colored differently. In case you feel there is no need for users to see some of calculated columns, you can hide them. Some calculated columns are helper columns, and are needed to allow to use SUMIFS()/COUNTIFS() formulas.

I used Defined Tables in my example. In Defined Tables (From menu: Insert>Table), formulas and formats are automatically expanded whenever a new row is added (assumed the formula/format remains same for entire column). And whenever you change Table or Column Name, those changes are automatically reflected in all Table formulas over entire workbook.
Attached Files
File Type: xlsx PurchasesExample.xlsx (19.9 KB, 5 views)

Last edited by ArviLaanemets; 05-07-2021 at 06:12 AM.
Reply With Quote
  #3  
Old 05-07-2021, 09:37 AM
Dual Trace Dual Trace is offline Formatting Bill of Materials Windows 10 Formatting Bill of Materials Office 2010
Novice
Formatting Bill of Materials
 
Join Date: Jan 2019
Location: Beaverton, OR
Posts: 4
Dual Trace is on a distinguished road
Default

Thanks a lot. Will work on digesting that.
Reply With Quote
  #4  
Old 05-07-2021, 02:16 PM
p45cal p45cal is offline Formatting Bill of Materials Windows 10 Formatting Bill of Materials Office 2019
Expert
 
Join Date: Apr 2014
Posts: 516
p45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to beholdp45cal is a splendid one to behold
Default

Update the table on the left, then right-click the table on the right and choose Refresh.
I've assumed a grouping based on Val and Size.
Attached Files
File Type: xlsx msofficeforums46945.xlsx (18.2 KB, 4 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting Bill of Materials Conversion of Columnwise Bill of Material into Rowwise Bill of Material using Excel Macro VBA mpapreja Excel Programming 4 05-21-2018 05:35 AM
Formatting Bill of Materials BOM (bill of material) umairn Project 1 04-17-2017 02:11 PM
Formatting Bill of Materials Integrating Bill Of Materials (BOM) GRT45 Project 1 03-16-2017 10:26 PM
Formatting Bill of Materials Materials/Work Costs Per task cool28uo Project 1 02-01-2015 03:59 PM
Tracking materials over time Guloluseus Project 3 04-02-2013 11:08 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:56 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft