Comparing Cells in Named Rrange (Array Formula?) Using Partial Text criteria

O

Ordic

New Member
Joined
Jun 11, 2013
Messages
1
Ok so I've been trying to figure this out on my own and I'm just completely stumped (Using Excel 2007).

I have a couple of employee reports that I'm working on and I need a little help to finish them up. I have two Worksheets (Employee Reports 1, Employee Reports 2) that I'm using to display information on. On 'Employee Reports 2' I have a series of Pivot Tables, two of which are from out side sources and a third linked to the named range tblCombination in Worksheet 'Combination'. 'Employee Reports 1" Summarizes the data in 'Employee Reports 2'.

The summery on 'Employee Reports 1' shows stats on the origin of certain customers. Here is what I need and what I've worked out for myself.

  1. I need the unique Elead:Name (I believe I figured this out myself check formula below)
  2. and the number of FreshUp:Name visits and/or B-back:Name visits for just these Elead customers. (Separate Counts)
  3. The count of customers not Associated with an Elead, Fresh Up, or B-Back.
  4. The Count where :Name is created at a prior date of FreshUp:Name added with #3 giving me a total of Phone Leads
  5. Finally the Count of FreshUp:Name then B-Back:Name (again separate) for #4 (where :Name was create to a prior date to FreshUp and or B-Back)

Now my pivot table for tblCombination(Named Range) is set up something like this.

[TABLE="width: 875"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Email[/TD]
[TD]Customer[/TD]
[TD]Date[/TD]
[TD]OTDB[/TD]
[TD]OTDB Check[/TD]
[/TR]
[TR]
[TD]Barnard, Robert[/TD]
[TD]@gmail.com[/TD]
[TD]Aimee Cullen[/TD]
[TD="align: right"]5/6/2013[/TD]
[TD="align: right"]101481273[/TD]
[TD]Elead:AimeeCullen[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:AimeeCullen[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@infionline.net[/TD]
[TD]Art Curtis.....[/TD]
[TD="align: right"]5/7/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:ArtCurtis.....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:ArtCurtis.....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@gmail.com[/TD]
[TD]Buddy Cotton[/TD]
[TD="align: right"]5/25/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:BuddyCotton[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:BuddyCotton[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Barbara Carter..[/TD]
[TD="align: right"]5/18/2013[/TD]
[TD="align: right"]101522398[/TD]
[TD]Elead:BarbaraCarter..[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:BarbaraCarter..[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@gmail.com[/TD]
[TD]Bill McCoy[/TD]
[TD="align: right"]5/9/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:BillMcCoy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:BillMcCoy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@gmail.com[/TD]
[TD]Brad Feitush[/TD]
[TD="align: right"]5/21/2013[/TD]
[TD="align: right"]101557140[/TD]
[TD]Elead:BradFeitush[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/22/2013[/TD]
[TD](blank)[/TD]
[TD]:BradFeitush[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@aol.com[/TD]
[TD]Millicent Calero[/TD]
[TD="align: right"]5/19/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:MillicentCalero[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:MillicentCalero[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@tampabay.rr.com[/TD]
[TD]David Badger[/TD]
[TD="align: right"]5/16/2013[/TD]
[TD="align: right"]101517359[/TD]
[TD]Elead:DavidBadger[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:DavidBadger[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@msn.com[/TD]
[TD]Carol Johnson....[/TD]
[TD="align: right"]5/22/2013[/TD]
[TD]B-Back[/TD]
[TD]B-Back:CarolJohnson....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@lkqcorp.com[/TD]
[TD]Chad Butler[/TD]
[TD="align: right"]5/14/2013[/TD]
[TD="align: right"]101511688[/TD]
[TD]Elead:ChadButler[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:ChadButler[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@aol.com[/TD]
[TD]Douglas Ball.....[/TD]
[TD="align: right"]5/22/2013[/TD]
[TD](blank)[/TD]
[TD]:DouglasBall.....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@gmail.com[/TD]
[TD]James And Debra Stallings....[/TD]
[TD="align: right"]5/30/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:JamesAndDebraStallings....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/31/2013[/TD]
[TD](blank)[/TD]
[TD]:JamesAndDebraStallings....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@gmail.com[/TD]
[TD]Deanna Holden.[/TD]
[TD="align: right"]5/7/2013[/TD]
[TD="align: right"]101485957[/TD]
[TD]Elead:DeannaHolden.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:DeannaHolden.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@netzero.net[/TD]
[TD]Dennis Stoops[/TD]
[TD="align: right"]5/17/2013[/TD]
[TD="align: right"]101519554[/TD]
[TD]Elead:DennisStoops[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:DennisStoops[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/18/2013[/TD]
[TD="align: right"]101522577[/TD]
[TD]Elead:DennisStoops[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/20/2013[/TD]
[TD="align: right"]101529445[/TD]
[TD]Elead:DennisStoops[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/24/2013[/TD]
[TD="align: right"]101564935[/TD]
[TD]Elead:DennisStoops[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/25/2013[/TD]
[TD="align: right"]101568596[/TD]
[TD]Elead:DennisStoops[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Don Darville[/TD]
[TD="align: right"]5/7/2013[/TD]
[TD="align: right"]101486546[/TD]
[TD]Elead:DonDarville[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:DonDarville[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@gmail.com[/TD]
[TD]Dave Peters..[/TD]
[TD="align: right"]5/6/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:DavePeters..[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:DavePeters..[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@stvdentistry.com[/TD]
[TD]David Shelton[/TD]
[TD="align: right"]5/27/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:DavidShelton[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:DavidShelton[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@tampabay.rr.com[/TD]
[TD]Virginia Thomas.[/TD]
[TD="align: right"]5/9/2013[/TD]
[TD="align: right"]101495043[/TD]
[TD]Elead:VirginiaThomas.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/10/2013[/TD]
[TD](blank)[/TD]
[TD]:VirginiaThomas.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@aol.com[/TD]
[TD]Wayne Haney[/TD]
[TD="align: right"]5/18/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:WayneHaney[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:WayneHaney[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Fred Golden[/TD]
[TD="align: right"]5/20/2013[/TD]
[TD="align: right"]101551604[/TD]
[TD]Elead:FredGolden[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:FredGolden[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@aol.com[/TD]
[TD]Fred Kehne..[/TD]
[TD="align: right"]5/17/2013[/TD]
[TD="align: right"]101521543[/TD]
[TD]Elead:FredKehne..[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/18/2013[/TD]
[TD](blank)[/TD]
[TD]:FredKehne..[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@gmail.com[/TD]
[TD]Isaac Flecha...[/TD]
[TD="align: right"]5/30/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:IsaacFlecha...[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/31/2013[/TD]
[TD](blank)[/TD]
[TD]:IsaacFlecha...[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Gaston Petridis[/TD]
[TD="align: right"]5/10/2013[/TD]
[TD]B-Back[/TD]
[TD]B-Back:GastonPetridis[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@live.com[/TD]
[TD]George Green[/TD]
[TD="align: right"]5/5/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:GeorgeGreen[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:GeorgeGreen[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@tampabay.rr.com[/TD]
[TD]Gene Johnson[/TD]
[TD="align: right"]5/16/2013[/TD]
[TD="align: right"]101518443[/TD]
[TD]Elead:GeneJohnson[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:GeneJohnson[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Krista Knight[/TD]
[TD="align: right"]5/27/2013[/TD]
[TD="align: right"]101576020[/TD]
[TD]Elead:KristaKnight[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:KristaKnight[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Joseph Greene[/TD]
[TD="align: right"]5/15/2013[/TD]
[TD="align: right"]101514699[/TD]
[TD]Elead:JosephGreene[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:JosephGreene[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/16/2013[/TD]
[TD]B-Back[/TD]
[TD]B-Back:JosephGreene[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@gmail.com[/TD]
[TD]Joe Needham[/TD]
[TD="align: right"]5/9/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:JoeNeedham[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:JoeNeedham[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Jan Connelly[/TD]
[TD="align: right"]5/15/2013[/TD]
[TD="align: right"]101514507[/TD]
[TD]Elead:JanConnelly[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:JanConnelly[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@hotmail.com[/TD]
[TD]Johnney Henley.[/TD]
[TD="align: right"]5/15/2013[/TD]
[TD="align: right"]101514391[/TD]
[TD]Elead:JohnneyHenley.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:JohnneyHenley.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/16/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:JohnneyHenley.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@gmail.com[/TD]
[TD]Jonathan Thompson[/TD]
[TD="align: right"]5/5/2013[/TD]
[TD="align: right"]101479184[/TD]
[TD]Elead:JonathanThompson[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:JonathanThompson[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Jonn Claybrook....[/TD]
[TD="align: right"]5/18/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:JonnClaybrook....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:JonnClaybrook....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@aol.com[/TD]
[TD]Jeannine Paquette[/TD]
[TD="align: right"]5/5/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:JeanninePaquette[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:JeanninePaquette[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@tampabay.rr.com[/TD]
[TD]Karen Labrant[/TD]
[TD="align: right"]5/30/2013[/TD]
[TD="align: right"]101592148[/TD]
[TD]Elead:KarenLabrant[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:KarenLabrant[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@hotmail.com[/TD]
[TD]Andre Lafreniere..[/TD]
[TD="align: right"]5/14/2013[/TD]
[TD="align: right"]101512197[/TD]
[TD]Elead:AndreLafreniere..[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/15/2013[/TD]
[TD](blank)[/TD]
[TD]:AndreLafreniere..[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@gmail.com[/TD]
[TD]Tony Menendez[/TD]
[TD="align: right"]5/19/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:TonyMenendez[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:TonyMenendez[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@msn.com[/TD]
[TD]Loyd VanSickle....[/TD]
[TD="align: right"]5/15/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:LoydVanSickle....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:LoydVanSickle....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@aol.com[/TD]
[TD]Andrew And Maggie Krantz[/TD]
[TD="align: right"]5/11/2013[/TD]
[TD](blank)[/TD]
[TD]:AndrewAndMaggieKrantz[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Maggie Grantz[/TD]
[TD="align: right"]5/15/2013[/TD]
[TD="align: right"]101513738[/TD]
[TD]Elead:MaggieGrantz[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]101513768[/TD]
[TD]Elead:MaggieGrantz[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:MaggieGrantz[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@telus.net[/TD]
[TD]Robert Marcinkoski[/TD]
[TD="align: right"]5/22/2013[/TD]
[TD]B-Back[/TD]
[TD]B-Back:RobertMarcinkoski[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@hotmail.com[/TD]
[TD]Martin Houle[/TD]
[TD="align: right"]5/6/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:MartinHoule[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:MartinHoule[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@hotmail.com[/TD]
[TD]Michael Barton[/TD]
[TD="align: right"]5/10/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:MichaelBarton[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:MichaelBarton[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@centurylink.net[/TD]
[TD]Mike Gemtile[/TD]
[TD="align: right"]5/18/2013[/TD]
[TD="align: right"]101522806[/TD]
[TD]Elead:MikeGemtile[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:MikeGemtile[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Milford Hawksley.[/TD]
[TD="align: right"]5/29/2013[/TD]
[TD="align: right"]101587453[/TD]
[TD]Elead:MilfordHawksley.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/30/2013[/TD]
[TD](blank)[/TD]
[TD]:MilfordHawksley.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@hotmail.com[/TD]
[TD]Lori Broadwell..[/TD]
[TD="align: right"]5/24/2013[/TD]
[TD="align: right"]101567561[/TD]
[TD]Elead:LoriBroadwell..[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/25/2013[/TD]
[TD](blank)[/TD]
[TD]:LoriBroadwell..[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@hotmail.com[/TD]
[TD]Michael Frazier.[/TD]
[TD="align: right"]5/8/2013[/TD]
[TD="align: right"]101490706[/TD]
[TD]Elead:MichaelFrazier.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/9/2013[/TD]
[TD](blank)[/TD]
[TD]:MichaelFrazier.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@me.com[/TD]
[TD]Ignacio Laviosa[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]101463951[/TD]
[TD]Elead:IgnacioLaviosa[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:IgnacioLaviosa[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/2/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:IgnacioLaviosa[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Pauline Woodley[/TD]
[TD="align: right"]5/5/2013[/TD]
[TD="align: right"]101477419[/TD]
[TD]Elead:PaulineWoodley[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:PaulineWoodley[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@earthlink.net[/TD]
[TD]William Payner[/TD]
[TD="align: right"]5/31/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:WilliamPayner[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:WilliamPayner[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@videotron.ca[/TD]
[TD]Rejeanne Leblanc[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]101464418[/TD]
[TD]Elead:RejeanneLeblanc[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:RejeanneLeblanc[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Rachel Ezell[/TD]
[TD="align: right"]5/31/2013[/TD]
[TD="align: right"]101595092[/TD]
[TD]Elead:RachelEzell[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:RachelEzell[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Rachel Ezell.[/TD]
[TD="align: right"]5/31/2013[/TD]
[TD="align: right"]101595528[/TD]
[TD]Elead:RachelEzell.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:RachelEzell.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Richard Pettys.[/TD]
[TD="align: right"]5/14/2013[/TD]
[TD="align: right"]101510296[/TD]
[TD]Elead:RichardPettys.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:RichardPettys.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@verizon.net[/TD]
[TD]Ray Stacey[/TD]
[TD="align: right"]5/18/2013[/TD]
[TD="align: right"]101524216[/TD]
[TD]Elead:RayStacey[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:RayStacey[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@precisionaxis.com[/TD]
[TD]Sean Nyitray[/TD]
[TD="align: right"]5/27/2013[/TD]
[TD](blank)[/TD]
[TD]:SeanNyitray[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/31/2013[/TD]
[TD="align: right"]101594599[/TD]
[TD]Elead:SeanNyitray[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]James Durden[/TD]
[TD="align: right"]5/27/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:JamesDurden[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:JamesDurden[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@live.com[/TD]
[TD]Steve Katzman....[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:SteveKatzman....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:SteveKatzman....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@verizon.net[/TD]
[TD]Steve And Connie Ulm[/TD]
[TD="align: right"]5/12/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:SteveAndConnieUlm[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:SteveAndConnieUlm[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Steve Underwood[/TD]
[TD="align: right"]5/26/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:SteveUnderwood[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:SteveUnderwood[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@mainelywired.net[/TD]
[TD]Tommy Kitchen[/TD]
[TD="align: right"]5/21/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:TommyKitchen[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:TommyKitchen[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@aol.com[/TD]
[TD]Vinny Ortiz[/TD]
[TD="align: right"]5/31/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:VinnyOrtiz[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:VinnyOrtiz[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@aol.com[/TD]
[TD]Tom Sacher-brown[/TD]
[TD="align: right"]5/25/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:TomSacher-brown[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:TomSacher-brown[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Jon Hardwick.....[/TD]
[TD="align: right"]5/19/2013[/TD]
[TD="align: right"]101524781[/TD]
[TD]Elead:JonHardwick.....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:JonHardwick.....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@yahoo.com[/TD]
[TD]Willie Sande.[/TD]
[TD="align: right"]5/17/2013[/TD]
[TD="align: right"]101521574[/TD]
[TD]Elead:WillieSande.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/18/2013[/TD]
[TD](blank)[/TD]
[TD]:WillieSande.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]@tampabay.rr.com[/TD]
[TD]Bill Meekins....[/TD]
[TD="align: right"]5/17/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:BillMeekins....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:BillMeekins....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD](blank)[/TD]
[TD]Adam Ross[/TD]
[TD="align: right"]5/3/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:AdamRoss[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:AdamRoss[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Arbra X[/TD]
[TD="align: right"]5/3/2013[/TD]
[TD="align: right"]101473263[/TD]
[TD]Elead:ArbraX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:ArbraX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Earsee Miller[/TD]
[TD="align: right"]5/15/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:EarseeMiller[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:EarseeMiller[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]James And Nancy Pierce[/TD]
[TD="align: right"]5/16/2013[/TD]
[TD](blank)[/TD]
[TD]:JamesAndNancyPierce[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Jeff Cass[/TD]
[TD="align: right"]5/26/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:JeffCass[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:JeffCass[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Jhon Gonzalez[/TD]
[TD="align: right"]5/14/2013[/TD]
[TD](blank)[/TD]
[TD]:JhonGonzalez[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]John Claybrook[/TD]
[TD="align: right"]5/18/2013[/TD]
[TD](blank)[/TD]
[TD]:JohnClaybrook[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Mark Russell...[/TD]
[TD="align: right"]5/7/2013[/TD]
[TD](blank)[/TD]
[TD]:MarkRussell...[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Mike Farrar[/TD]
[TD="align: right"]5/18/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:MikeFarrar[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:MikeFarrar[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Ron And Judy Highhouse.....[/TD]
[TD="align: right"]5/9/2013[/TD]
[TD]Fresh-Ups[/TD]
[TD]FreshUp:RonAndJudyHighhouse.....[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](blank)[/TD]
[TD]:RonAndJudyHighhouse.....[/TD]
[/TR]
</tbody>[/TABLE]


Which Changes when I change the filter the Employee I want the report on. Now you'll notice in OTDB Check is actually a helper column I created in the source data in order to do these calculations. In this column there are only for types of data.

Elead:Name
FreshUp:Name
B-Back:Name
:Name​

I've figured out the first part on my own as to how many unique Eleads are in this list with the following array formula (must hit Ctrl+Shift+Enter)
Code: 
=SUMPRODUCT(--(IF(FREQUENCY(IF(ISNUMBER(SEARCH("Elead",er2FootstepsOTDBcheck)),MATCH(er2FootstepsOTDBcheck,er2FootstepsOTDBcheck,0)),IF(ISNUMBER(SEARCH("Elead",er2FootstepsOTDBcheck)),MATCH(er2FootstepsOTDBcheck,er2FootstepsOTDBcheck,0)))>0,1)))

On the above list this returns 35 which is correct.

Now what I need to do is Take the unique "Elead:NameA" find the COUNT of associated "FreshUp:NameA" or "B-Back:NameA" (these need to be separated out) Where NameA is the common aspect from all accounts.

Now what you'll notice is that for each Elead:Name or FreshUp:Name or B-back:Name there is one :Name which represents the creation of that customers account. Multiple Elead:Name will only have one :Name same with Freshup and B-back. What I need is the count of :Name that does not also have a Elead:Name, FreshUp:Name, or B-back:Name. And for the really tricky one the count of the :Name where the date asscociated with it is different.



I know this is a tall order and Any help at would be amazing. At this point I've reach my limit with limited experience in excel. If need to I can add more helper columns or edit the current one. I don't care at all if this is done in a formula or in VBA.(Email have been removed for customer privacy)

Broc
 
You must log in or register to reply here.

Similar threads

O
  • Solved
Index/Match, Nested IF statements, or Array Formula?
Replies
9
Views
645
Ol Reliable
O
T
  • Question Question
Dynamic Sheet Queries
Replies
1
Views
307
Vogateer
V
M
  • Solved
XLOOKUP help (maybe), not sure what function to use
Replies
4
Views
379
MeaclH
M
J
  • Question Question
Take Excel Worksheet, Divide into Worksheets/Workbooks by Employee/Card #, Email via Gmail to Employee for Input
Replies
9
Views
972
jennifer7i884
J
S
  • Question Question
Remove rows that contain full or partial email addresses
Replies
2
Views
731
Serafin54
S

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top