Home

Matching clients to providers

%3CLINGO-SUB%20id%3D%22lingo-sub-469000%22%20slang%3D%22en-US%22%3EMatching%20clients%20to%20providers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-469000%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20clients%20with%20requirements.%20I%20have%20a%20list%20of%20service%20providers%20with%20services%20the%20provide.%20I%20want%20to%20call%20up%20a%20client%20on%20a%20query%20page%2C%20is%20type%20in%20ref%20number%2C%20it%20shows%20name%20and%20needs%2C%20and%20the%20page%20then%20shows%20the%20providers%20that%20have%20the%20most%20services.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20the%20moment%20the%20query%20page%20vlookup%20the%20clients%20needs%2C%20listing%20each%20need%20at%20the%20top%20of%20a%20column.%20There%20is%20a%20%22shadow%20page%22%20where%20each%20need%20is%20checked%20in%20its%20own%20column%2C%20needs%20along%20the%20top%2C%20provider%20along%20the%20side.%20I%20then%20use%20MATCH%20to%20see%20if%20the%20need%20is%20in%20the%20providers%20list%2C%20putting%20a%201%20in%20if%20it%20is.%20The%20rows%20are%20then%20added%20up.%20(A%20macro%20then%20sorts%20and%20presents%20the%20top%20matches-%20not%20worried%20about%20this).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20in%20the%20cell%20that%20is%20the%20intersection%20for%20N1%2C%20P4%2C%20I%20MATCH%20N1%20to%20the%20list%20for%20P4%2C%20putting%201%20in%20the%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20better%2Fmore%20elegant%20way%20to%20do%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-469000%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Ian_Hopping
Occasional Visitor

I have a list of clients with requirements. I have a list of service providers with services the provide. I want to call up a client on a query page, is type in ref number, it shows name and needs, and the page then shows the providers that have the most services.

 

At the moment the query page vlookup the clients needs, listing each need at the top of a column. There is a "shadow page" where each need is checked in its own column, needs along the top, provider along the side. I then use MATCH to see if the need is in the providers list, putting a 1 in if it is. The rows are then added up. (A macro then sorts and presents the top matches- not worried about this).

 

So in the cell that is the intersection for N1, P4, I MATCH N1 to the list for P4, putting 1 in the cell.

 

Is there a better/more elegant way to do this?

Related Conversations