+ Reply to Thread
Results 1 to 19 of 19

How can I sort "New" & "Old" client data and copy it to two different sheets

  1. #1
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Question How can I sort "New" & "Old" client data and copy it to two different sheets

    I have been searching this for many days but could not find out the exact solution for my problem. Description about what I want -

    I've created a simple master sheet where all the records are entered in daily basis. Column A - Serial No., Column B - Client Code, Column C - Client Type, Column D - Age.

    S.N. Client Code Client Type Date
    155 N-13-07-16-155 New 2013-07-16
    156 N-13-07-16-156 New 2013-07-16
    157 O-13-07-16-157 Old 2013-07-16
    158 N-13-07-16-158 New 2013-07-16
    159 N-13-07-16-159 New 2013-07-16
    160 O-13-07-17-160 Old 2013-07-17

    (This is the sample which you can find on the attached workbook.)

    I want the the data from my Master sheet to be copied New Clients only in "New Clients sheet" and Old clients only in "Old Clients sheet" automatically when I enter data in the master sheet.


    Can any one help me. I'll try to clarify the my question if needed.


    Thank you in advance.


    Client Code.xlsx
    Last edited by SubashP; 09-01-2014 at 01:53 AM.

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    Hi SubashP,
    Welcome to the Forum.

    This can easily be achieved with the help of a Worksheet_Change Event code. Please find the attached sheet to see if this is what you are trying to achieve.
    Please read the instructions on the Master_Sheet to know, how this code will work.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    Hi. This is an array formula and must be set by using CTRL + SHIFT + ENTER and not just ENTER. No code is needed.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    Thank you for your instant reply. I believe I've not been clear in submitting my question. Let me clarify further.

    I’ve written the issues in bullet points which will probably help you to understand.


    There is a long list of columns in Master Sheet which will be in New Clients sheets and old clients sheet.


    The client code I've stated there is on my own (I cannot submitted my original sheet because of Confidentiality which I should maintain)

    [LIST]


    Client Code will be unique for New clients only.


    Unique code can be repeated several times but as an old client.


    All these data of new and old clients will be in entered Master sheet.


    Focus of the recorded data is the unique client code along with new and old clients.


    When I copy paste or fill any data (new or old client) in Master sheet I want it be filled automatically in the other 2 specific sheets. (New clients in “New Clients” Sheet and old clients in “Old clients” sheet)


    I believe the sheet has some conditions (if “new” + “Unique Code” copy it to “New Clients” sheet, if “old”+ “Unique Code” copy it to “Old clients” sheet)


    The main motive is to find how many of the clients have revisited and how times of their visit.

    (To find out the status of our services how well we are delivering. More the clients re-visiting service sites frequently means we are doing well)


    Please see the codes as it can be:

    S.N. Client Code Client Type Date
    155 KAV|BH|89|10095 New 16/07/2013
    157 KAV|KC|93|10098 New 16/07/2013
    156 KAV|BH|89|10095 Old 02/08/2013
    158 KAV|SH|83|10076 New 03/08/2013
    159 KAV|MA|87|10083 New 05/08/2013
    160 KAV|KC|93|10076 Old 12/09/2013
    161 KAV|BH|89|10098 New 10/10/2013
    162 KAV|SH|83|10076 Old 15/10/2013



    Look forward for your support.

    Thank you.
    [/SIZE]
    Attached Files Attached Files
    Last edited by SubashP; 09-01-2014 at 04:48 AM.

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    see the attached file
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    The solution i suggested seems to work well with your revised data. Can you explain how it does not?

  7. #7
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    Thank you all for your replies.

    It worked well and will help me minimize time in calculation and for accuracy of data.

    Further can you can you please suggest me a way to do it as soon as I enter it in master sheet without coping and pasting the formula each time I enter data.


    Thank you again.

  8. #8
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    Glenn! this is my first post so, I just saw the first solution by sktneer and instantly replied. Your code is working well and thank you for your instant reply. Further, I want it to be automatically filled once I enter it in the master sheet rather than coping and pasting formula each time I enter the data in master sheet.

    Hope to hear from you soon.


    Thank you.

  9. #9
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    The problem was solved but it takes long time to load excel which is very time taking. Is there any other way to do so?


    Thank you.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    The solution i suggested seems to work well with your revised data. Can you explain how it does not?

  11. #11
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    Quote Originally Posted by SubashP View Post
    The problem was solved but it takes long time to load excel which is very time taking. Is there any other way to do so?
    Thank you.
    If you mean that your sheet has become slow due to its size and formulas present in it, you can try a VBA code to achieve this.
    Please find the attached sheet to see if this works as per your requirement.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    Thank you very much to all the experts who have helped me with solution.

  13. #13
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    Which solution worked for you?

    If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) -> Mark thread as solved.
    Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.

  14. #14
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    Quote Originally Posted by sktneer View Post
    If you mean that your sheet has become slow due to its size and formulas present in it, you can try a VBA code to achieve this.
    Please find the attached sheet to see if this works as per your requirement.
    Thank you for the work sheet. I am now entering the data in the sheet you provided. I want one more favour in the sheet. What ever I enter in the master sheet it automatically.

    I want a specific column named "Repeated Times" only in "Old Client Sheet" after the "Remarks" Column.

    As you know, all the "New" Clients will have unique code.

    Once the unique code is repeated it is the "old clients" which now automatically comes to "Old Clients Sheet"

    Can we fill the "repeated times" column after "Remarks" in "old client sheet" that how many times the client visited our service center after receiving the Unique code (After enrolled as new) with the help of VBA.

    I also want conditional formating to be done. If between the repeated number is

    1 to 3 Fill Cell - "Light Red", Text - "Dark Red"

    3 to 5 Fill Cell - "Light Yellow", Text - "Dark Green"

    5 above Fill Cell - "Light Green", Text - "Dark Green"


    Look forward for your response and thank you in advance.
    Attached Files Attached Files
    Last edited by SubashP; 09-02-2014 at 06:52 AM.

  15. #15
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    Please find the attached sheet to see and let me know if this works as per your requirement.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    Quote Originally Posted by sktneer View Post
    Please find the attached sheet to see and let me know if this works as per your requirement.
    Many Many Thanks to you Sir it worked well.

  17. #17
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    You're welcome.
    You may also click on * (star) to Add Reputation if the solution provided helped you. This is another way to say thanks.

  18. #18
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    *****
    Stars for this thread and I've already rated the thread.

  19. #19
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How can I sort "New" & "Old" client data and copy it to two different sheets

    Rating a thread is different from clicking on * to Add Reputation.
    While reading this reply, you will see a * Add Reputation below my profile details, you were supposed to click that * to Add Reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  2. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  3. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  4. Enter Data on "Main" or "Input" Sheet and Copy to One of Many Other Sheets
    By timothy_no7 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2012, 07:29 PM
  5. How can i copy value from "HTMLText"(EMBED("Forms.HTML:Text","")),using Macro
    By andrewyang in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2010, 12:47 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1