Solved

Part II of Excel lookup of data and copy rows to new worksheet

Posted on 2011-09-08
16
269 Views
Last Modified: 2013-12-27
This is the second part to the original question - http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27287678.html?cid=1575#a36500735

Now that we have created the separate worksheets for each sales person, I'd like to have a summary of the totals owed to each sales person.  As they are paid commission I need to manually indicate the amount paid but want to make sure that this info is not overwritten when the amounts owed are updated each time.

0
Comment
Question by:mdg1
  • 11
  • 5
16 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 36504156
can you pls specify the col that you want or need to be pushed to the new worksheet and show a simple example with the data already there so we can emulate it correctly.
gowflow
0
 

Author Comment

by:mdg1
ID: 36504297
Sure - I added a few columns that I realized that I needed on the main page and then added the worksheet call "Commission SUmmary Report."  

So - IDEALLY...
1.  A calculation can be automatically performed for the commission amount on the main worksheet.  You will see that sometimes there is a flat dollar commission instead of a %.  I'm thinking there may need to be a if/then stmt that indicates that if it's not a percentage then to take the flat dollar amt???

2.  The added worksheet - commission summary report - will automatically update "potential" and "earned" commission but not overwrite the Paid amount that will be added manually.

Let me know if you need more explanation.

This is great!!!!
Salesman-Split-comm-summ.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36504515
What about Potential Commission - SP #2      Earned Commission - SP #2
how are they taken into concideration in your summary commission you seemed to have given an example showing only
Potential Commission - SP #1      Earned Commission - SP #1
whereas for Sales-JG (which happens to be coincidently my initials !!!) you have data for all of
Potential Commission - SP #1      Earned Commission - SP #1      Potential Commission - SP #2      Earned Commission - SP #2

So question:
1) how do you account for htese diffrent SP1 SP2 Potential / Earned (shall we add them together and show them under Potential and Earned in the sheet ?
2) How often you want to run this commission computation ? Should it add on top what is new ? if yues then we will need to flag all items that were transfered to commission so we don't take them into account on the next run !
3) What about when you make modifications/addition/deletions in the main file how should it reflect in this sheet if the amounts were already captured for commission ?
4) In your sheet that you created there is no date or month is this normal ?
5) You have a column Paid will you update that manually there ?

gowflow
0
 

Author Comment

by:mdg1
ID: 36504675
1) how do you account for htese diffrent SP1 SP2 Potential / Earned (shall we add them together and show them under Potential and Earned in the sheet ? - I GUESS THIS GETS CONFUSING...I THINK HERE, I SHOULD HAVE CALLED JG SP#1 SINCE THIS SALES PERSON IS REOCCURING AND THE OTHERS AS SP#2.  I DID CALCULATE THOSE POTENTIAL AND EARNED COMMISSIONS.  NOT SURE IF I AM ANSWERING YOUR QUESTION HERE.  I DO NEED THESE SEPARATED AS IT WILLL BE FOR TWO DIFFERENT PEOPLE WHO ARE SHARING THE COMM.

2) How often you want to run this commission computation ? Should it add on top what is new ? if yues then we will need to flag all items that were transfered to commission so we don't take them into account on the next run !  NO SET FREQUENCY TO RUN THE COMPUTATION.  FROM TIME TO TIME WE EITHER REPORT ON WHAT THE SALESPERSON HAS EARNED OR WE PAYOUT ON EARNED COMMISSIONS.  IDEALLY, I'D LIKE ALL NEWER ADDITIONS ON THE BOTTOM


3) What about when you make modifications/addition/deletions in the main file how should it reflect in this sheet if the amounts were already captured for commission ?  I GUESS IN THE END, WE JUST NEED TO BE SURE THAT WE ARE PAYING FOR THE FINAL PMTS.  IF WE PAY OUT ON SOMEONE WHO CANCELS AND RECEIVES A REFUND THEN WE'LL SHOW THAT WE PAID OUT TOO MUCH.  SOUNDS LIKE WE MAY NEED TO SAVE THE WORKSHEET AT EACH PAYOUT SO WE KNOW WHO THEY WERE PAID OUT ON


4) In your sheet that you created there is no date or month is this normal ?  THERE WILL BE DATES - I JUST SIMPLIFIED IT FOR OUR EXAMPLE


5) You have a column Paid will you update that manually there ?  YES - WILL MANUALLY UPDATE THE PAID COLUMN

THANKS, JG!

0
 
LVL 29

Expert Comment

by:gowflow
ID: 36512205
Sorry hv been caught with other urgent issues will give it a look once possible tks for your patience.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36522333
Sorry again for this delay your the next inline I should hv something for you later today !! Tks ur patience. Sorry again
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36522892
I am reading again your last post now that I  need to developp something and seems you are not sure what you want !! in item 1) you seem to be hasty and not sure if you add or not the SP's in item 2 you mention that all new items will go to the bottom each time so you will have something like this
in the sheet Commission Summary Report
Salesperson      Potential      Earned      Paid      Owe
AB       250.00        125.00              125.00
CD       500.00        500.00              500.00
EF                        
GH                        
...
then
Salesperson      Potential      Earned      Paid      Owe
AB       850.00        225.00              675.00
CD       800.00        700.00              100.00
EF      350.00      450.00            100.00
etc,,,

Question how can you trace 1 salesman situation if new items comes at the bottom each time you run them ??? Then also in 3) your not clear on the answer then on 4) you simplified by not putting dates !!!!!

HEY HOOOO !!!!! I need something clear with dates and exactly on what you need so I can build a scheme or else sorry you need to get your ideas together and when it is clear let me know.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36523935
mdg1
Here is my suggestion and solution for you to the best of my knowledge and with the limited info oyu provided and the multiple tweaky options you had as per my previous post.

Content
1) The formulas for Commissions in
Col O titles Potential Commission - SP #1
Col P Earned Commission - SP #1
Col Q Potential Commission - SP #2
Col R Earned Commission - SP #2
Have all been ammended so if there is a percentage it will take the formulas you already had (perc * amount) if no percent but flat amount then it will take the flat amount.

2) Each and every time you run the previous Macro: Split Worksheet by Salesman then all the data that exist in the sheet 'Commission Summary Report' will be automatically deleted. becoz when you run this macro it automatically delete all sales sheets and redo them from scratch then it is logical not to keep trace of any commission that was previously transfered to this sheet so that data is always consistent.

3) I have created a new button called: Compile Commissions what will it do is the follwoing:
It will go thru all the SALES- sheets and look each and every record and if the last column (AQ Moved to Commission column that I created) is blank then it will take this data in concideration and after moving it to commission Summary it will flag it to YES so that if you run this commission report the next time it will not take this data anymore hence avoiding duplicates. It will do this for all the sheets and you will get a compilation in this Commission Summary Sheet.

Please run the file try first to run the Spilt Worksheets by Salesman and go check the results. then run Compile comission and check the results.

Pls let me know your fedback so we can adjust.
gowflow
Salesman-Split-comm-110912.xlsm
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:mdg1
ID: 36524562
Hi There,

I'm sorry I haven't been so clear.  With each new aspect that you present me with, I realize that I need to re-think our needs.  Having said that, what you've done so far is remarkable.  Here are some things that I don't understand fully or are a little problematic...

It looks like if I list two salespeople in column L and if say someone who had always been listed second - say TT - is now listed first - then there is an entire new spreadsheet created for her as opposed to be included in tab for which she is listed second.  Can this be fixed?

Second - I may likely be misunderstanding your instructions/explanation about the commissions summary...If I indicate that something was paid - Column J - this of course gets wiped out when I run the commissions macro again.  So, I'm unclear as to how I keep track of what has been paid out and therefore owing...

Please lose patience with me... we are almost there and I truly apprciate this remarkable system you are creating for me!

Thanks.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36527847
ok here it is:
Let me first address your issues:
1)
It looks like if I list two salespeople in column L and if say someone who had always been listed second ....
>>> Well the issue is not as you discribed list first or second does not matter but what happened in fact is when you switched the salesman you added a space inadvertantly before the salesman name which resulted in a new sheet creation " TT" where before it was "TT". Anyway this has been addressed in this version and wether small letters/capital/spaces it will always find the correct salesman and affect the data to the existing sheet.

2)
Second - I may likely be misunderstanding your instructions/explanation about the commissions
>>> well I am trying to create a model for you based on the so multiple if and but and open possibilities whcih obviously the past file was not optimum and had still issues to be set and dealt with.

ABOUT THIS VERSION
===================
This version is somewhat closer to a package but this means flexibility is somewhat more restrictive. In principle the more flexibility to do whatever you want with data any time the more developpement time to adapt to all possible situations.

In this version you will see following Buttons
1) Delete Sales WS and Rest Commission in red
This button is made when you need to rest your workbook it will delete all sales worksheets, rest col that keep track that sales has been distributed and rest all commission data. This is good when you are starting a year fresh or a period (3 months or 6 months ... whatever ) and you still have data in the main sheet and you need to start from scratch.

2) Split Worksheet by Salesman: I have now created a new Col AR 'Moved to Sales' that will flag every record that is moved to a Sales worksheet. So when you run this procedure next time it will not move the record again. So basically every day when you enter data in Main Summary Page (as long as there is no Salesman code) the record will never be moved. If you are done with the record then you label its salesman and when you run this routine it will move it to the coresponding sheet.

3) Compile Commissions
This button will go thru all the Sales sheets and every record where Col AQ moved to commission is blank will calucualte the data and affect it in Summary Commission and will label Col AQ with YES so it is not duplicated on next run.

LIMITATIONS
===========
You have to understand that when a record is flaged with YES wether in Main Summary Sheet or in individual Sales Sheets then any modification to the actual record will not be populated in the related sheets. Like if the record in Main Summary Sheet has already been distributed to sales sheet if you decide that you want to revise the rate or the commission then as the record is labeled YES all changes will not be recorded. Now if you remove the YES manually deleted it from Main Summary sheet then you need to go to the coresponding sales sheet and manually delete the coresponding record and if the commission was captured also go in the commission sheet and subtract the said amount (I am outlining the limitation and do not favor manual correction but drawing your attention for maybe rasing new issues that we can probably solve by automation in new questions)

Hope I have help you to see clearer in this pls do not hesitate to let me kow of your comments and suggestions. Finally as long as you don't activate the Delete worksheets then you will always have your data and whatever you input in commissions.
gowflow
 
Salesman-Split-comm-110913.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36548195
mdg1
Did you have a chance to test the last version posted ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36585770
Any news ???
gowflow
0
 

Author Comment

by:mdg1
ID: 36600681
So gowflow...wasn't in the office much last week.  I think we are good!  can you give me instructions on how to get the code onto the excel document?  Thanks!
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 36708430
To implement version 'Salesman-Split-comm-110913.xlsm' attached do the following:

1) download the attached 'Salesman-Split-comm-110913.xlsm' version on your PC/Netwrok
2) Save your existing production workbook into a new name. (like copy/paste the file in the same directory and give it a new name)
3) open your production workbook. (the one just created)
4) from within your production workbook choose file/Open and choose the attached 'Salesman-Split-comm-110913.xlsm' version.
5) Choose Developper Menu and click on Visual Basic
6) In hte left pane you will see both your production file and the attached file 'Salesman-Split-comm-110913.xlsm'
7) YOU WILL BE COPYING FROM the attached file to your production file and not vice versa. (keep it in mind)
8) if your production file has
a) no Modules attached then goto point 9).
b) If your production file has module1 and it is the one I provided in the last question and not more like it does not contain other Sub or functions that you wish to keep then do the following:
Doubleclick on Module1 of your production file choose from the Edit menu Select All and then press delete. It should delete all the code that is there. after that go to point 9)
c) If your production file has Subs that you were developped and you want to keep then right click on the name of your production file and choose Insert Module it will create Module2 or 3 a subsequent to the last one there. After that doubleclik on Module1 of the attached file and from the edit menu click on select all right click in the code and choose copy then go back to the new created module in your production file and paste the code there. Now you need to go back to your existing module in the production file and delete from it all the subs that already existed to which there is a new version imported in Module2 like if your previous module was module1 open the right combo on top and not all the subs that are there on a pice of paper each one that you see the same name in module2 delete it from module1 (you need to tick on the left bottom icon when you display module1 so you can view each sub by iteslef. Once done you will endup in module1 Subs that I did not developp and in Module2 the attached Subs that are in the attached file. Goto item 10)
9) click on Module1 of the attached file and drag it on top of the name of your production file.
10) SAVE your production workbook at this stage.

11) We need to move the sheet called 'Commission Summary Report' form the attached file to your production file. display the attached file and click on the sheet Commission Summary Report then right click and choose move or copy. Tick on the Create a copy box then from the top combo choose your production file and click where you wanted to be. press ok and the sheet will be copied to your production file.
12) SAVE your production workbook at this stage.

13) We need to create the diffrent command buttons with their attached code. click on your production workbook 'Main Summary Page' and click on menu developper then make sure the button called Design Mode is clicked if it is not just click on it. if you have buttons there just delete them all.
14) go back to the attached file and click on 'Main Summary Page' and also make sure you are in design mode. Do the following for each button that is in the sheet (3 times 3 buttons)
- right click on the first button choose copy
- goback to your production file 'Main Summary Page' and paste the button there at the same spot where it was in the attached.
- do this for the remaining 2 other buttons.

15) Once the buttons have been copied to oyur production file click on the file to make sure it has the focus and SAVE the workbook.
16) Now will need to update the command buttons to get the code you should have:
Commandbutton1 >> Split Worksheets by Salesman
Commandbutton2 >> Compile Commissions
Commandbutton3 >> Delete Sales WS Reset Commissions (in Red)

17) Goto VBA double click on the sheet Main Summary Page in the left pane coresponding to your production workbook. if there is code pertinent to any commandbutton listed above simply delete it and paste the below code there.

 
Private Sub CommandButton1_Click()
If MsgBox("This process will move all pending data (Data in Col AR that is blank) to coresponding SALES sheets based on the data in sheet 'Main Summary Page' grouped by Salesman." & Chr(10) & Chr(10) _
    & "Are you ready to proceed with Sales sheets moving/creation", vbQuestion + vbYesNo, "Salesman Sheet Creation") = vbYes Then
    SalesSplit
End If
End Sub

Private Sub CommandButton2_Click()
If MsgBox("This process will go thru all 'SALES-' labeled sheets and will transfer to Commission Summary Sheet all records where in Column AQ 'Transfered to Commission' is still blank. Values will be grouped by Salesman." & Chr(10) & Chr(10) _
    & "Are you ready to proceed with Commission Summary Report?", vbQuestion + vbYesNo, "Commission Summary Report") = vbYes Then
    CompileCommissions
End If
End Sub

Private Sub CommandButton3_Click()
If MsgBox("This process will delete all 'SALES-' labeled sheets. It will also reset 'Commission Summary Sheet' and erase all existing data in this sheet. The Sheet Main Summary Page will not be touched but only Col AR (Moved to SALES) will be reset to blank." & Chr(10) & Chr(10) _
    & "Are you ready to proceed with this process?", vbCritical + vbYesNo, "Salesman Sheet Creation") = vbYes Then
    DeleteSalesSheets
End If
End Sub

Open in new window


18) SAVE your production workbook.
19) IMPORTANT all along make sure you NEVER save the attached file but only your production workbook.
20) Exit both workbook and if prompt to save the attached file say NO.
21) Start the new production workbook and test it.

Let me know
gowflow
Salesman-Split-comm-110913.xlsm
0
 

Author Closing Comment

by:mdg1
ID: 36911867
Thanks so much.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36912322
Your welcome glad I could help. Tks for the grade
gowflow
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I recently purchased an HP EliteBook 2540p notebook/laptop. It has two video ports on it – VGA and DisplayPort. HP offers an optional docking station for the 2540p that also has both a VGA port and a DisplayPort. There are numerous online reports do…
In the modern office, employees tend to move around the workplace a lot more freely. Conferences, collaborative groups, flexible seating and working from home require a new level of mobility. Technology has not only changed the behavior and the expe…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now