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.

Employee NameEmailCustomerDateOTDBOTDB Check
Barnard, Robert@gmail.comAimee Cullen5/6/2013101481273Elead:AimeeCullen
(blank):AimeeCullen
@infionline.netArt Curtis.....5/7/2013Fresh-UpsFreshUp:ArtCurtis.....
(blank):ArtCurtis.....
@gmail.comBuddy Cotton5/25/2013Fresh-UpsFreshUp:BuddyCotton
(blank):BuddyCotton
@yahoo.comBarbara Carter..5/18/2013101522398Elead:BarbaraCarter..
(blank):BarbaraCarter..
@gmail.comBill McCoy5/9/2013Fresh-UpsFreshUp:BillMcCoy
(blank):BillMcCoy
@gmail.comBrad Feitush5/21/2013101557140Elead:BradFeitush
5/22/2013(blank):BradFeitush
@aol.comMillicent Calero5/19/2013Fresh-UpsFreshUp:MillicentCalero
(blank):MillicentCalero
@tampabay.rr.comDavid Badger5/16/2013101517359Elead:DavidBadger
(blank):DavidBadger
@msn.comCarol Johnson....5/22/2013B-BackB-Back:CarolJohnson....
@lkqcorp.comChad Butler5/14/2013101511688Elead:ChadButler
(blank):ChadButler
@aol.comDouglas Ball.....5/22/2013(blank):DouglasBall.....
@gmail.comJames And Debra Stallings....5/30/2013Fresh-UpsFreshUp:JamesAndDebraStallings....
5/31/2013(blank):JamesAndDebraStallings....
@gmail.comDeanna Holden.5/7/2013101485957Elead:DeannaHolden.
(blank):DeannaHolden.
@netzero.netDennis Stoops5/17/2013101519554Elead:DennisStoops
(blank):DennisStoops
5/18/2013101522577Elead:DennisStoops
5/20/2013101529445Elead:DennisStoops
5/24/2013101564935Elead:DennisStoops
5/25/2013101568596Elead:DennisStoops
@yahoo.comDon Darville5/7/2013101486546Elead:DonDarville
(blank):DonDarville
@gmail.comDave Peters..5/6/2013Fresh-UpsFreshUp:DavePeters..
(blank):DavePeters..
@stvdentistry.comDavid Shelton5/27/2013Fresh-UpsFreshUp:DavidShelton
(blank):DavidShelton
@tampabay.rr.comVirginia Thomas.5/9/2013101495043Elead:VirginiaThomas.
5/10/2013(blank):VirginiaThomas.
@aol.comWayne Haney5/18/2013Fresh-UpsFreshUp:WayneHaney
(blank):WayneHaney
@yahoo.comFred Golden5/20/2013101551604Elead:FredGolden
(blank):FredGolden
@aol.comFred Kehne..5/17/2013101521543Elead:FredKehne..
5/18/2013(blank):FredKehne..
@gmail.comIsaac Flecha...5/30/2013Fresh-UpsFreshUp:IsaacFlecha...
5/31/2013(blank):IsaacFlecha...
@yahoo.comGaston Petridis5/10/2013B-BackB-Back:GastonPetridis
@live.comGeorge Green5/5/2013Fresh-UpsFreshUp:GeorgeGreen
(blank):GeorgeGreen
@tampabay.rr.comGene Johnson5/16/2013101518443Elead:GeneJohnson
(blank):GeneJohnson
@yahoo.comKrista Knight5/27/2013101576020Elead:KristaKnight
(blank):KristaKnight
@yahoo.comJoseph Greene5/15/2013101514699Elead:JosephGreene
(blank):JosephGreene
5/16/2013B-BackB-Back:JosephGreene
@gmail.comJoe Needham5/9/2013Fresh-UpsFreshUp:JoeNeedham
(blank):JoeNeedham
@yahoo.comJan Connelly5/15/2013101514507Elead:JanConnelly
(blank):JanConnelly
@hotmail.comJohnney Henley.5/15/2013101514391Elead:JohnneyHenley.
(blank):JohnneyHenley.
5/16/2013Fresh-UpsFreshUp:JohnneyHenley.
@gmail.comJonathan Thompson5/5/2013101479184Elead:JonathanThompson
(blank):JonathanThompson
@yahoo.comJonn Claybrook....5/18/2013Fresh-UpsFreshUp:JonnClaybrook....
(blank):JonnClaybrook....
@aol.comJeannine Paquette5/5/2013Fresh-UpsFreshUp:JeanninePaquette
(blank):JeanninePaquette
@tampabay.rr.comKaren Labrant5/30/2013101592148Elead:KarenLabrant
(blank):KarenLabrant
@hotmail.comAndre Lafreniere..5/14/2013101512197Elead:AndreLafreniere..
5/15/2013(blank):AndreLafreniere..
@gmail.comTony Menendez5/19/2013Fresh-UpsFreshUp:TonyMenendez
(blank):TonyMenendez
@msn.comLoyd VanSickle....5/15/2013Fresh-UpsFreshUp:LoydVanSickle....
(blank):LoydVanSickle....
@aol.comAndrew And Maggie Krantz5/11/2013(blank):AndrewAndMaggieKrantz
Maggie Grantz5/15/2013101513738Elead:MaggieGrantz
101513768Elead:MaggieGrantz
(blank):MaggieGrantz
@telus.netRobert Marcinkoski5/22/2013B-BackB-Back:RobertMarcinkoski
@hotmail.comMartin Houle5/6/2013Fresh-UpsFreshUp:MartinHoule
(blank):MartinHoule
@hotmail.comMichael Barton5/10/2013Fresh-UpsFreshUp:MichaelBarton
(blank):MichaelBarton
@centurylink.netMike Gemtile5/18/2013101522806Elead:MikeGemtile
(blank):MikeGemtile
@yahoo.comMilford Hawksley.5/29/2013101587453Elead:MilfordHawksley.
5/30/2013(blank):MilfordHawksley.
@hotmail.comLori Broadwell..5/24/2013101567561Elead:LoriBroadwell..
5/25/2013(blank):LoriBroadwell..
@hotmail.comMichael Frazier.5/8/2013101490706Elead:MichaelFrazier.
5/9/2013(blank):MichaelFrazier.
@me.comIgnacio Laviosa5/1/2013101463951Elead:IgnacioLaviosa
(blank):IgnacioLaviosa
5/2/2013Fresh-UpsFreshUp:IgnacioLaviosa
@yahoo.comPauline Woodley5/5/2013101477419Elead:PaulineWoodley
(blank):PaulineWoodley
@earthlink.netWilliam Payner5/31/2013Fresh-UpsFreshUp:WilliamPayner
(blank):WilliamPayner
@videotron.caRejeanne Leblanc5/1/2013101464418Elead:RejeanneLeblanc
(blank):RejeanneLeblanc
@yahoo.comRachel Ezell5/31/2013101595092Elead:RachelEzell
(blank):RachelEzell
@yahoo.comRachel Ezell.5/31/2013101595528Elead:RachelEzell.
(blank):RachelEzell.
@yahoo.comRichard Pettys.5/14/2013101510296Elead:RichardPettys.
(blank):RichardPettys.
@verizon.netRay Stacey5/18/2013101524216Elead:RayStacey
(blank):RayStacey
@precisionaxis.comSean Nyitray5/27/2013(blank):SeanNyitray
5/31/2013101594599Elead:SeanNyitray
@yahoo.comJames Durden5/27/2013Fresh-UpsFreshUp:JamesDurden
(blank):JamesDurden
@live.comSteve Katzman....5/1/2013Fresh-UpsFreshUp:SteveKatzman....
(blank):SteveKatzman....
@verizon.netSteve And Connie Ulm5/12/2013Fresh-UpsFreshUp:SteveAndConnieUlm
(blank):SteveAndConnieUlm
@yahoo.comSteve Underwood5/26/2013Fresh-UpsFreshUp:SteveUnderwood
(blank):SteveUnderwood
@mainelywired.netTommy Kitchen5/21/2013Fresh-UpsFreshUp:TommyKitchen
(blank):TommyKitchen
@aol.comVinny Ortiz5/31/2013Fresh-UpsFreshUp:VinnyOrtiz
(blank):VinnyOrtiz
@aol.comTom Sacher-brown5/25/2013Fresh-UpsFreshUp:TomSacher-brown
(blank):TomSacher-brown
@yahoo.comJon Hardwick.....5/19/2013101524781Elead:JonHardwick.....
(blank):JonHardwick.....
@yahoo.comWillie Sande.5/17/2013101521574Elead:WillieSande.
5/18/2013(blank):WillieSande.
@tampabay.rr.comBill Meekins....5/17/2013Fresh-UpsFreshUp:BillMeekins....
(blank):BillMeekins....
(blank)Adam Ross5/3/2013Fresh-UpsFreshUp:AdamRoss
(blank):AdamRoss
Arbra X5/3/2013101473263Elead:ArbraX
(blank):ArbraX
Earsee Miller5/15/2013Fresh-UpsFreshUp:EarseeMiller
(blank):EarseeMiller
James And Nancy Pierce5/16/2013(blank):JamesAndNancyPierce
Jeff Cass5/26/2013Fresh-UpsFreshUp:JeffCass
(blank):JeffCass
Jhon Gonzalez5/14/2013(blank):JhonGonzalez
John Claybrook5/18/2013(blank):JohnClaybrook
Mark Russell...5/7/2013(blank):MarkRussell...
Mike Farrar5/18/2013Fresh-UpsFreshUp:MikeFarrar
(blank):MikeFarrar
Ron And Judy Highhouse.....5/9/2013Fresh-UpsFreshUp:RonAndJudyHighhouse.....
(blank):RonAndJudyHighhouse.....

<tbody>
</tbody>


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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You must log in or register to reply here.

Similar threads

S
  • Question
Remove rows that contain full or partial email addresses
Replies
2
Views
515
Serafin54
S
J
  • Question
Take Excel Worksheet, Divide into Worksheets/Workbooks by Employee/Card #, Email via Gmail to Employee for Input
Replies
9
Views
590
jennifer7i884
J
K
  • Question
Cell Formatting When Using Index Match
Replies
4
Views
600
kxellis26
K
D
  • Question
Formula using If statement with Xmatch
Replies
1
Views
151
dharv3y78
D
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
374
AndyEd
A

Forum statistics

Threads
1,214,386
Messages
6,119,216
Members
448,876
Latest member
Solitario

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