Macro to find the sum of shares against each name

[arun.sj]

Hi Team,

Need help to create macro for the manual activity that i does.

I have data like attached where each resource holds share in many projects. I need resources who have sum of shares as more than 100 (sum of share across all projects) to be in one sheet and resources with sum of shares less than 100 (sum of share across all projects) to be in another sheet. Resources with sum of shares as exactly 100 should be ignored.

The ID is unique for each resource. So i usually creates a pivot with ID and sum of shares, then copy paste the “above 100 shares” and “less than 100 shares” to the respective sheets.

I have did the same manual activity for the attached sample and pasted the “above 100 shares” and “less than 100 shares” in the respective sheets.
Please help me with codes to automate this.

[1aaaaaaaaaaaaaaa]

Try this... (macro workbook attached, run update sub)

Please Login or Register  to view this content.

[arun.sj]

Hi,

Thanks for replying.
This is not working in the way that I was expecting.. let me explain in detail.

I want the people whose sum of shares more than 100 to be placed in sheet “share above 100”. As per the attached sample, Arun’s share is 135 (sum of shares across all projects) and similarly Arjun’s share is 315. Since both of these people have more than 100, these 2 names alone have to be placed in sheet “share above 100”.


In the same way Patric’s share is 60 (sum of shares across all projects) and similarly Vimal’s share is 1. Since both of these people have less than 100, these 2 names alone have to be placed in sheet “share below 100"

I have pasted the details that has to come in the sheets “above 100 shares” and “less than 100 shares.. I am looking for the same details after applying the codes.

Please help me..

[arun.sj]

Hi Team, Please help

[jaslake]

Hi arun.sj

What it it's equal to 100 (David)?

[jindon]

You want to exclude the 100 shares in total....

Please Login or Register  to view this content.

[PCI]

For the fun another one
Note:
in sheet "Share below 100" is included total share =100
if you want the opposite change
"If (ObjDic1.Item(K) > 100) Then" to "If (ObjDic1.Item(K) >= 100) Then"

Please Login or Register  to view this content.

[arun.sj]

Hi arun.sj

What it it's equal to 100 (David)?

Hi John, if the sum of shares is equal to 100, it should be ignored.. Only above 100 shares and less than 100 have to be pasted to the respective sheets..

[arun.sj]

Hi arun.sj

What it it's equal to 100 (David)?

Hi John, if the sum of shares is equal to 100, it should be ignored.. Only above 100 shares and less than 100 have to be pasted to the respective sheets..

[arun.sj]

You want to exclude the 100 shares in total....

Please Login or Register  to view this content.

Thanks for replying.. I am getting syntax error..

Can you help me by updating the codes in the sample attached please.

[arun.sj]

You want to exclude the 100 shares in total....

Please Login or Register  to view this content.

Thanks for replying.. I am getting syntax error..

Can you help me by updating the codes in the sample attached please.

[jindon]

Syntax error??

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[arun.sj]

For the fun another one
Note:
in sheet "Share below 100" is included total share =100
if you want the opposite change
"If (ObjDic1.Item(K) > 100) Then" to "If (ObjDic1.Item(K) >= 100) Then"

Please Login or Register  to view this content.

This is working..
The only thing is share equal to 100 should be completely ignored which is not happening..
Only share above 100 and below 100 should be available in the respective sheets..

Please help..

[PCI]

"share equal to 100 should be completely ignored " Yes it was prepared like that
So use next code

Please Login or Register  to view this content.

[arun.sj]

Syntax error??

This is working perfectly fine..
I need your help to understand this code so I can make necessary changes when I use this in different files..

Can you please help me by adding a comment beside the code so that I will be able to make changes.. Please help..

[arun.sj]

Maybe:

Please Login or Register  to view this content.

This is working but have few minor issues.. After applying the codes, few cells are colored as in the heading in the sheet "share above 100"..

[arun.sj]

"share equal to 100 should be completely ignored " Yes it was prepared like that
So use next code

Please Login or Register  to view this content.

Thanks for replying .. unfortunately i am getting a "run-time error 9 :Subscript out of range" ..

If you can update the codes in the sample file, it would be of great help.. Please help..

[PCI]

The macro run OK with the sample you sent
See attached

[JOHN H. DAVIS]

I tested with your sample data and didn't have any issues. However, try this.

Please Login or Register  to view this content.

[jindon]

You probably nedd to change

Please Login or Register  to view this content.

This creates the data set only columns that is necessary to work with, also match up with the column order to output heading.
i.e

ID	Name	Share	Grade	Location	Supervisor Name
2	3	4	6	7	8

If you place Column "ID" in 1, "Share" in 3, no need to change the rest.

[arun.sj]

The macro run OK with the sample you sent
See attached

This is working perfectly fine... .. Thank you so much for your help..

need your help to understand the code.. requesting you to please explain me the codes so i can make changes while using it in diff files... Please help..

[arun.sj]

You probably nedd to change

Please Login or Register  to view this content.

This creates the data set only columns that is necessary to work with, also match up with the column order to output heading.
i.e

ID	Name	Share	Grade	Location	Supervisor Name
2	3	4	6	7	8

If you place Column "ID" in 1, "Share" in 3, no need to change the rest.

Thanks for your reply.. Sorry but i need a bit more detailed since i am not that good with the codes..

i understood the part [{2,3,4,6,7,8}]).. but when i tried with a diff sample by updating this part, the columns are placed correctly as expected but the details were incorrect .. like shares with below 100 values are also placed in sheet "Share above 100"..

Please help..

[jindon]

Upload your original file with dummy data, but data layouts must be correct order.

[PCI]

"need your help to understand the code"
The code is very simple but you need first to understand how "Scripting.Dictionary" is working.
There is 2 Dictionaries:
- One to make the list of the ID and the sum of the share
- One to make the list of the ID and the information attached to the ID
A first part of the macro is preparing the Dictionary
A second part of the macro is doing the display versus the sum of the share recorded in the first dictionary
From first dictionary, depending of the sum, the corresponding ID is used to search in the second dictionary the corresponding information and display it.

[arun.sj]

Upload your original file with dummy data, but data layouts must be correct order.

Thank you so much for asking the original format.. Attached is the original file with sample data.. I have highlighted the columns that has to be brought to the sheets "Share above 100" and "Share below 100".

The column which has the share value is column AN..

Please help..

[cyiangou]

And now for something completely different...

over_share_cy.xlsm

Please Login or Register  to view this content.

[arun.sj]

"need your help to understand the code"
The code is very simple but you need first to understand how "Scripting.Dictionary" is working.
There is 2 Dictionaries:
- One to make the list of the ID and the sum of the share
- One to make the list of the ID and the information attached to the ID
A first part of the macro is preparing the Dictionary
A second part of the macro is doing the display versus the sum of the share recorded in the first dictionary
From first dictionary, depending of the sum, the corresponding ID is used to search in the second dictionary the corresponding information and display it.

Thanks a lot for explaining .. But sorry to bother you , i was trying to use this code to my original dump however i am getting error ..

If you can help me by updating the codes in the attached file , i no more have to worry and it would be of great help.. Requesting for your help..

I have highlighted the columns that has to be considered. The column which has the share value is column AN..

Please help..

[jindon]

Please Login or Register  to view this content.

[PCI]

No big difference

Please Login or Register  to view this content.

[PCI]

A remake
( Thx Jindon )

Please Login or Register  to view this content.

[arun.sj]

A remake
( Thx Jindon )

Please Login or Register  to view this content.

Thanks a lot for replying .. i am getting error that says "subscript out of range" for this code...

but the code in your previous post : -

Please Login or Register  to view this content.

This code is working perfectly fine.. shall i consider this code ? do you think there should be any updates? please help

[arun.sj]

Please Login or Register  to view this content.

Thanks a Million Jindon This is working perfectly fine..

[PCI]

"do you think there should be any updates? "
No keep the first one, it is a technical more VBA improvement with no effect on the result.
But can you give me more details where it stops and beter send the file for test.

[arun.sj]

"do you think there should be any updates? "
No keep the first one, it is a technical more VBA improvement with no effect on the result.
But can you give me more details where it stops and beter send the file for test.

Sure.. Attaching the file.. Requesting you to check..

[jindon]

Thanks a Million Jindon This is working perfectly fine..

You are welcome and thanks for multiple reps.

[PCI]

" i am getting error that says "subscript out of range" for this code..."
Heu... yes because I changed the sheet's name
Use next statement if you want to use the code : See "Data" changed to "Sheet1"

Please Login or Register  to view this content.

[arun.sj]

No big difference

Please Login or Register  to view this content.

Hi PCI .. Thanks for explaining in detail in this code..
i need one more help before closing the thread.. After the data is pasted in both the sheets, it would be good if there is border across the data in both the sheets.. border in all the cells with black or blue color..

Requesting for your help to update the above code..

[PCI]

Is it what you want !

Please Login or Register  to view this content.

[arun.sj]

Is it what you want !

Please Login or Register  to view this content.

Yes.. That was exactly what i was looking for.. Thanks a Ton for the help.. The codes are working fantastic...

[PCI]

Good news, and thank you for your thanks
Enjoy Excel
PCI