Stocklist - Combine Duplicate entries, Delete rows

**ibs2012** · 03-06-2013, 05:12 AM

Hi,

I've recently started working and got myself into trying to automize a quite laboriouse stock-checking process. In my search on how to do a certain task I stumbled across this forum and have found it extremely useful. I'm quite a noob what goes for macro's and all, but I hope I'll be able to learn. Anyway:

I have a material consumption list from a certain project which has to be compared to an estimated materials consumption list.
In the list, all Material's are grouped by task (which have a unique numerical code in the form 00000000-0000) while each material also has a unique alphanumerical code. In a seperate column the quantity of the item consumed is listed. The issue is that within some of the tasks, materials are repeated. How could I combine the rows with the same task code and material code where the quantities are added in a single row before deleting the duplicate row?

The table is set up as following:

Name (C4) | QTY (D4)| Unit (E4) | Material Code (F4) | Stock (G4) | Identifier (H4) | Area (I4) | Task Code (J4)

Thanks in Advanced!

Regards

**arlu1201** · 03-06-2013, 06:14 AM

Do you have a sample that you can upload?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**AB33** · 03-06-2013, 06:19 AM

I think Pivot Table should do it

**ibs2012** · 03-06-2013, 08:52 PM

Thanks for the replies! I've attached an altered data sample to illustrate the format of the data. For the data on sheet 1 I think a pivot table would do fine. However it would be nice to have a more elegant solution, especially for the table on sheet 2. I'm trying to make this work with any set of data in the same format, with each data set being 500-2000 rows long.

Data Sample.xlsx

**AB33** · 03-07-2013, 05:56 AM

"How could I combine the rows with the same task code and material code where the quantities are added in a single row before deleting the duplicate row?"
If you combine task code and material code, there will be not any duplicate. Yes, you have duplicates in both columns on their own, but if you were to see both as one, there is none. For e.g in table 2 you have duplcates in rows, 5,6 and 7 in task column, but if you look at the same rows in material, there is not duplicates. So, you should decide where the duplicates are.