Need to aggregate data on a filtered result whitout changing the original data

[mahhdy]

Hi,
I have a material list for different work orders, each work order may have the same material called multiple time. I want to filter my desired work order and have a clean and aggregated data, without and duplication of materials, while their quantity is aggregated. I want to have the outcome data on a different sheet. So I don't want any change happens in my original list.
I think I have to use <code>"Scripting.Dictionary" for teh aggregation purpose, So by looping the all rows twice I can get there. But it is hard for me to implement that, becuase I never tryed </code><code>"Scripting.Dictionary" before. Can anyone help?
By now I am using a third page, so at the first I am copying filtered result there and the aggregation and removing the duplications happens there. I want to avoid copying and directly putting datas on my form (destination sheet).
Your help would be appreciated.


</code>

 

[Marcelo Branco]

Have you tried a Pivot Table?
See
http://contextures.com/CreatePivotTable.html

Would be helpful if you can post a smal data sample for testing purposes along with expected/desired outcome.

M.

 

[Amanda Wilso]

1. Select any cell in the source data table.
2. On the Ribbon, click the Insert tab.
3. In the Tables group, click Recommended PivotTables.
4. In the Recommended PivotTables window, scroll down the list, to see the suggested layouts. Click on a layout, to see a larger view.
5. Click on the layout that you want to use, then click OK.

​By creating a PivotTable you can easily operate with the table to aggregate datas without changing the original datas.

 

[mahhdy]

Hi,
No Pivot table is mostly for analyzing purpose, and by the way is a third sheet, Which I already am using.
Here are some pictures. The first one is actual table list. The second the form I want to fill according to that table, Some of the fields are hidden in the picture (Master list).
Right now I am copying all the filtered result to a new sheet, aggregating them and filling the form by using a loop (for / next) order per row. I also developed another procedure which directly fills the form, from filtered result by putting if not rows.entirerow.hidden check inside the for/ next loop.
Cheers,
First, the master list

second, the form to be filled:

Have you tried a Pivot Table?
See
http://contextures.com/CreatePivotTable.html

Would be helpful if you can post a smal data sample for testing purposes along with expected/desired outcome.

M.

 

[mahhdy]

thanks for your clear instruction, Actually I am a big fan of Pivot table, but I am automating a process, so by pressing one click a form being generated for the work order containing that data. It is a matter of programming not practicing one by one.

I think I have to use <code>"Scripting.Dictionary" because in the parent loop, i need to return to a list, while the nested loop will check and aggregate the quantities for each material. </code>

1. Select any cell in the source data table.
2. On the Ribbon, click the Insert tab.
3. In the Tables group, click Recommended PivotTables.
4. In the Recommended PivotTables window, scroll down the list, to see the suggested layouts. Click on a layout, to see a larger view.
5. Click on the layout that you want to use, then click OK.

By creating a PivotTable you can easily operate with the table to aggregate datas without changing the original datas.

 

[Marcelo Branco]

Pictures are not helpful. It's not possible to copy your data for testing purposes.
Try a small data sample: See
B - Posting Aids in
http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

M.

 

[mahhdy]

Hi,
Here is the link:
https://1drv.ms/x/s!ArGi1KRQ5iItgYpDkrBctcLnPLX-SA

By pressing Ctrl/Q a user form would be showed up, By pressing new method or old method you will see what I meant by. The new method doesn't aggregate the data but directly copies the fields, but old method uses the sheet2 as a temp sheet, aggregate data there and then fills the form.

Thanks in advance

Pictures are not helpful. It's not possible to copy your data for testing purposes.
Try a small data sample: See
B - Posting Aids in
http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

M.

 

[Marcelo Branco]

This is a complex project and to be honest i did not understand what you are trying to do.
Sorry, i don't have time to help you

Do you want to know more about Dicitonary?
Some links
Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery
VBA for smarties: Dictionaries
https://www.experts-exchange.com/articles/3391/Using-the-Dictionary-Class-in-VBA.html

Hope this helps

M.

 

[mahhdy]

Thanks by the way,
Actually, I knew that, so I just was trying to get help on the idea, not the solution.
Really appreciate it.
Yours,
M

This is a complex project and to be honest i did not understand what you are trying to do.
Sorry, i don't have time to help you

Do you want to know more about Dicitonary?
Some links
Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery
VBA for smarties: Dictionaries
https://www.experts-exchange.com/articles/3391/Using-the-Dictionary-Class-in-VBA.html

Hope this helps

M.

 

[mahhdy]

Also, an easier way came to my mind, I can aggregate the rows in the form (Destination sheet), instead of doing that in the list itself or a third sheet. It is the easiest way. But I have to learn dictionary or other collection tools anyway.