+ Reply to Thread
Results 1 to 15 of 15

Tracking Referral Members Sales and Report : MLM Business

  1. #1
    Registered User
    Join Date
    12-10-2016
    Location
    Malaysia
    MS-Off Ver
    2011
    Posts
    11

    Tracking Referral Members Sales and Report : MLM Business

    I need to a file that can trace or find members and sales in a referral multilevel structure.

    Anyone has some formulas or templates that i can work on?

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Tracking Referral Members Sales and Report : MLM Business

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Tracking Referral Members Sales and Report : MLM Business

    Welcome to the forum.

    How about:

    VLOOKUP
    INDEX MATCH MATCH
    SUMIF(S)

    If you want more specific help, you are going to need to be more specific.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    12-10-2016
    Location
    Malaysia
    MS-Off Ver
    2011
    Posts
    11

    Re: Tracking Referral Members Sales and Report : MLM Business

    Sorry All,

    Was new to this forum thing.

    Appreciate all the advice given to me.

    Here i attach an excel file explaining my problem with a sample database.

    Thanks ALL.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Tracking Referral Members Sales and Report : MLM Business

    In order to get started it would seem as if there would have to be some referred by's for sales persons 01728, 88778 or 22222 whom are marked 'highest leaders', however when I filer I don't see any.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    12-10-2016
    Location
    Malaysia
    MS-Off Ver
    2011
    Posts
    11

    Re: Tracking Referral Members Sales and Report : MLM Business

    Firstly, I would like to thank you for the quick response.

    Secondly,
    To answer JeteMC question, the Highest Leader has no upper person that referred them. They are the 1st Line sales person appointed by the company.

    They will get 35% (Level 1) commission from his/her downlines.

    I now have included the full referral listing so that all sales person's line or referral levels are included in the latest attached file here.


    Once again, I thank all of you for lending a helping hand and thanks to EXCELFORUM for being a great platform.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-10-2016
    Location
    Malaysia
    MS-Off Ver
    2011
    Posts
    11

    Re: Tracking Referral Members Sales and Report : MLM Business

    Hi JeteMC,

    I have used the "Reply to Thread" button to reply your questions but i am not sure whether you will be notified.

    Therefore i am sending you a personal reply instead to inform you as well.

    Thanks.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Tracking Referral Members Sales and Report : MLM Business

    I understand that a highest leader has no upper person. What I don't understand is how to identify a salesperson that has a highest leader as an upper person.

  9. #9
    Registered User
    Join Date
    12-10-2016
    Location
    Malaysia
    MS-Off Ver
    2011
    Posts
    11

    Re: Tracking Referral Members Sales and Report : MLM Business

    Hi JeteMac,

    The highest level is appointed by the company not based on requirements of sales or anything. There are shortlisted partners and their responsibility is to recruit sales persons to join the company.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Tracking Referral Members Sales and Report : MLM Business

    If you were having to manually identify the sales persons who were recruited by the highest leaders, how would you do that?
    If we could know how it would be done manually, we might be able to write a formula and/or VBA code that would automate the process.

  11. #11
    Registered User
    Join Date
    12-10-2016
    Location
    Malaysia
    MS-Off Ver
    2011
    Posts
    11

    Re: Tracking Referral Members Sales and Report : MLM Business

    JeteMC,
    Highest Leader is manually registered by the company. Those downline is registered by their highest upline and with a column that states referral by whom and the registered date info then we can determine which downline sales person comes in first compared to the other downlines.

    By identifying which Sales person comes in before the others in that particular team line then we can know what level is that particular sale person.

    With all the info above, we should be able to calculate the sales x the level commission % and work out each member's total commission.


    I hope you understand what i am trying to explain.

    Thanks.

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Tracking Referral Members Sales and Report : MLM Business

    Let me go about asking a different way, as I am no closer to understanding than I was in post #5.
    Can you tell me the name and/or ID of a salesperson that is directly downline from RICO, ID 01294, and can you tell me how you determine that.

  13. #13
    Registered User
    Join Date
    12-10-2016
    Location
    Malaysia
    MS-Off Ver
    2011
    Posts
    11

    Re: Tracking Referral Members Sales and Report : MLM Business

    Hi JeteMC,

    I redo the file and removed the names which is non-importance.


    SAMPLE LINE 1

    I colored few cells in orange to help explain a referral level example.

    01229798 : Highest Leader : Level 1 : 35% Commission : 24/01/2020 14:39 (Earliest Register compared to below)

    01675408 : 1st Downline : Referral - 01229798 : Level 2 - 25% Commission : Registered 24/01/2020 17:37 (after Highest Lever member)

    01227371 - 2nd Downline : Referral - 01229798 : Level 3 - 20% Commission : Registered 02/02/2020 23:39 (after above member)


    SAMPLE LINE 2

    Then 01675408 recruits his own line as well below, (Green Cells): Now 01675408 becomes a Highest Leader with his/her own downline,

    01675408 : Highest Leader : Level 1 : 35% Commission : 24/01/2020 17:37 (Earliest Register compared to below)

    01956018 : 1st Downline : Referral - 01675408 : Level 2 - 25% Commission : Registered 24/01/2020 22:12 (after Highest Lever member)




    Here i reattached the new file Ver 2.0

  14. #14
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Tracking Referral Members Sales and Report : MLM Business

    Please try at

    C2:Cxx
    =IF(D2="HIGHEST LEADER",TEXT(1+COUNTIFS(D$2:D2,D2)/100,"00.00"),TEXT(VLOOKUP(RIGHT(D2,8),B$1:C1,2,0)+1,"00.00"))

    Then sort column C ascending.
    If you need to sort by ID. Copy Column C and Paste Value to override the formula.

    Commission
    G2:Gxx
    =SUMIFS($F$2:$F$1277,$C$2:$C$1277,"*"&RIGHT(C2,2),$C$2:$C$1277,">="&UNICHAR(173)&C2)*VLOOKUP(INT(C2),$I$2:$J$11,2)

  15. #15
    Registered User
    Join Date
    12-10-2016
    Location
    Malaysia
    MS-Off Ver
    2011
    Posts
    11

    Re: Tracking Referral Members Sales and Report : MLM Business

    Cheers Bo_Ry, I will give it a try and get back to you if i needed more help.

    Cheers

+ 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: 6
    Last Post: 01-27-2016, 07:38 AM
  2. Report for Daily Sales from cumulative Sales report
    By raj0102 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-06-2014, 02:50 PM
  3. Sales Report: Percentual contribution of each client's vertical to overall sales
    By panther98 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2013, 07:07 AM
  4. [SOLVED] Sales report from sales data sheet if i select the date
    By loki7431 in forum Excel General
    Replies: 4
    Last Post: 02-06-2013, 09:43 PM
  5. linking weekly sales report to monthly sales report
    By sueatcigaretshopper in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-27-2012, 09:22 PM
  6. Form Email from Report, Only if Report summary sheet lists Acct for that Sales Rep
    By lukep10 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2008, 01:38 PM
  7. [SOLVED] Sales tracking report
    By PSikes in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-02-2006, 08:25 PM

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