I need some help with this! I have a parts list that can be very long. If the same part shows up in more than one row I want to add the totals and remove the duplicate part number's row. Below is an example of what I want to accomplish...
I have this...
P/N.......... Description............... U/M..........Qty
1234.........rivet..........................ea.............1
1234.........rivet..........................ea.............4
345...........washer.....................ea.............12
65.............nut...........................ea.............4
3846.........prepreg.....................sf..............24
3846.........prepreg.....................sf..............12
64.............primer......................ga.............14
I need it to look like...
P/N..........Description................ U/M..........Qty
1234.........rivet..........................ea.............5
345...........washer.....................ea.............12
65.............nut...........................ea.............4
3846.........prepreg.....................sf.............36
64.............primer......................ga.............14
As you can see the part numbers (p/n) 1234 and 3846 now show totals and are not duplicated. This also needs to happen dynamically because this parts list changes all the time. I would like not to have to do a macro for this but if it's the only way then I will. Please help! Thanks!
Hi,
Use Data Filter Advanced to extract a unique list of P/Ns, then alongside them use VLOOKUP to get the Description and U/M from the original list, and use a SUMIF() function to sum the total Qty.
Unfortunately and without a macro to do this automatically you're going to have to repeat this process every time.
HTH
Richard Buttrey
If this was useful then please rate it appropriately.
Click the small star iconat the bottom left of my post.
crc21,
Detach/open workbook crc21 - EF752796 - SDG12.xls and run macro CreateReport.
Have a great day,
Stan
stanleydgromjr
Windows Vista Business, Excel 2003 and 2007
If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
Richard,
Your direction lead me down the right path and I ended up getting it to work dynamically. Thanks again.
Stanley,
I'll try that macro to just to see how it works. Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks