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?
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?
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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)
Cheers Bo_Ry, I will give it a try and get back to you if i needed more help.
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks