+ Reply to Thread
Results 1 to 2 of 2

unique values & specific text query

  1. #1
    Registered User
    Join Date
    01-27-2010
    Location
    chicago
    MS-Off Ver
    Excel 2007
    Posts
    4

    unique values & specific text query

    Scenario: My employees get a referral fee for every employee they refer. The amount increases when 3 of the individuals they refer each refer 3 people and this continues in a trickle down effect for 3 cycles.

    In the example below Brad Pitt, James bond & Demi Moore have each referred 3 people. I am in need of a formula for column C & column D below.

    Query: What formula [that I can just copy & paste down the column] do I put in c2 to figure out if brad pitt has referred at least 3 unique names in column A?

    Then what formula [that I can just copy & paste down the column] do I put in d2 to tell me if at least of the 3 unique names that Brad Pitt referred have also referred 3 unique names in column A?

    Finally, what formula [that I can just copy & paste down the column] do I put in e2 to tell me how many of Brad Pitt's that referred 3 people have referrals that have also referred 3 unique names in column A?

    Any help offered would be greatly appreciated. Thanks in advancenew employee referred by referred 3 people # of referals who referred 3 people # of those who referred 3 people
    james bond brad pitt yes 1 0
    angelina jolie james bond no 0 0
    bill gates brad pitt yes 0 0
    donnie brasco no 0 0
    justin timberlake james bond no 0 0
    sigourney weaver brad pitt yes 0 0
    demi moore will smith no 1 0
    bruce willis demi moore yes 0 0
    ashton kutcher demi moore yes 0 0
    elvis presley james bond no 0 0
    michael jordan demi moore yes 0 0
    Peyton Manning no 0 c

    HTML Code: 
    <b>Sheet11</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt;  padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px;  " /><col style="width:161px;" /><col style="width:148px;" /><col style="width:117px;" /><col style="width:243px;" /><col style="width:215px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >new employee</td><td >referred by</td><td >referred 3 people</td><td > * *# of referals who referred 3 people</td><td ># of those who referred 3 people</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >james bond</td><td >brad pitt</td><td >yes</td><td style="text-align:center; ">1</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >angelina jolie</td><td >james bond</td><td >no</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >bill gates</td><td >brad pitt</td><td >yes</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >donnie brasco</td><td > </td><td >no</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >justin timberlake</td><td >james bond</td><td >no</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >sigourney weaver</td><td >brad pitt</td><td >yes</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >demi moore</td><td >will smith</td><td >no</td><td style="text-align:center; ">1</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >bruce willis</td><td >demi moore</td><td >yes</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >ashton kutcher</td><td >demi moore</td><td >yes</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >elvis presley</td><td >james bond</td><td >no</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >michael jordan</td><td >demi moore</td><td >yes</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >Peyton Manning</td><td >*</td><td >no</td><td style="text-align:center; ">0</td><td style="text-align:center; ">c</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=IF(B2="","",IF<span style=' color:008000; '>(COUNTIF<span style=' color:#0000ff; '>($B$2:$B$13,B2)</span>>=3,"yes","no")</span>)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>





























    --------------------------------------------------------------------------------



    subjects that are probably not the solution that i read on only to become more confused:



    count the number of unique values occurring posted by specific user

    count if referred by is c12[mike dee] - then see what dates are < d12 - range?
    =COUNT(IF((Sheet6!I7:I10008=C12)*(Sheet6!L7:L10008<=D12),Sheet6!L7:L10008))

    add up unique values [freq] - match=text - len=blank cells -
    SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))

    Check if a cell matches specific text
    =IF(A2="Buchanan", TRUE, FALSE)

    Count how often multiple number values occur by using functions
    count if referred by is c12[mike jackson] - then see what dates are < d12 - range?
    =COUNT(IF((c2:c8=C12)*(d2:d8<=d12),d2:d8))
    =COUNT(IF((c2:c8=C12)*SUM(IF(FREQUENCY(b2:b10,b2:b10)>0,1)))))

    Count the number of unique values by using functions
    =SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))
    Count the number of unique values by using functions
    add up unique values [freq] - match=text - len=blank cells -
    SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))

    =COUNT(IF((c2:c8=C12)*(d2:d8<=d12),d2:d8)
    Last edited by verbatim; 01-27-2010 at 06:54 PM.

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,327

    Re: unique values & specific text query

    Hi verbatim
    Aliitle more about the lay out
    Column A has a name and Column B has who referred them?
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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