• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting Customer Rating Lists

N

nine

New Member
I have three customer rating lists (A=excellent, F=poor service), one sheet per product. At the end of the year, we have to enter this data into our companies HQ database, which has all products listed alphabetically.


For example, I may have Smith B, Jones B, Wise C but the central database has Abbie, Mike, Susan, Wise.


I can download the central database which contains the names of all sales persons, listed alphabetically, but only a fraction of those are listed in our local excel sheet. In my example I have smith, jones and wise working for me, but the central database has Abbie, Mike, Susan, Wise (list 1) and David, Jones, Peter, Smith, Taylor (list 2).


Is there anyway of having a conditional format to combine our local sheets into one local master sheet and then have excel compare and sort it to the HQ master list 001 and 002, so the names match the master and we can then input the customer rating for each sales person.


Thanks
 
Hi Nine,


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


Regarding uploading file,please see the below link:

http://chandoo.org/forums/topic/posting-a-sample-workbook


Now regarding your question, I could comprehend your requirement to some extent but not entirely.Would request you to please upload a sample workbook and explain your requirement by giving some further data example.


Looking forward to your sample workbook(s).


Regards,

Kaushik
 
Here is the link for the demo excel workbook


http://www.2shared.com/document/cKkxSjSA/Sort-local-lists-and-match-to-.html


In a nutshell, four times a year, we are supposed to log on to a central database and enter the customer rating of our employees. In this example worksheet "Central Office MasterSheet 1" and "Central Office MasterSheet 2" represent the central database.


Each office has only a select number of employees (sales, delivery, technical) and these are represented by the other worksheets "Mechanics", "Field Engineers", "Sales Force Office 1", "Sales Force Office 2", "Delivery Service" and "Warehouse Supply Chain". These would be separate workbooks, but in this example i added them to the same excel workbook as sheets.


Question: How Can I do a global search, such that excel takes these workbooks and sorts the various local lists into two master lists which match the central list .


In other words, if I alphabetize all lists and convert the numerical score to a letter score, how do i automatically combine local lists ("Mechanics", "Field Engineers", "Sales Force Office 1", "Sales Force Office 2", "Delivery Service" and "Warehouse Supply Chain") into one temporary list and then have this list reordered and resorted into two lists which match the entries show in the master lists (Central Office MasterSheet 1 and Central Office MasterSheet 2)

Thank you
 
Hello nintynine,


Thank you for uploading the file.


I will not be able to upload the file as I am posting this from office network. So please follow the instruction below:


1) Firstly, list down all the sheets name somewhere in "Central Office MasterSheet 1" (say from L2 onwards[L1 is the header], select the list and give a named range (left click on name box [left to the formula bar], write ShtNames and hit enter)


2)Now at Mechanics sheet, insert a column left to the Col A (FIRST). At A2 write:


=IF(B2="","",B2&C2) and drag it down (say till 500 rows)


Repeat this exercise for other 5 sheets


3)Now at C2 write the below formula:


=IFERROR(VLOOKUP(A2&B2,INDIRECT("'"&INDEX(ShtNames,MATCH(1,--(COUNTIF(INDIRECT("'"&ShtNames&"'!$A$2:$D$100"),A2)>0),0))&"'!$A$2:$D$100"),4,FALSE),"No match found") press Ctrl+shift+enter (not just enter)from key board to enter it as an array formula, and drag it down as you need.


Let us know if this is fine.


Regards,

Kaushik
 
Kaushik


Thank you.

I tried the following. In the L1 cell master sheet 1, i typed ShtNames, left of the fx symbol on the formula bar and also labelled it ShtNames. Underneath it, in cell L2-L7, i typed

Mechanics

Field Engineers

Sales Force Office 1

Sales Force Office 2

Delivery Service

Warahouse Supplie Chain


In cell C2 on master sheet 1 i typed =IFERROR(VLOOKUP(A2&B2,INDIRECT("'"&INDEX(ShtNames,MATCH(1,--(COUNTIF(INDIRECT("'"&ShtNames&"'!$A$2:$D$100"),A2)>0),0))&"'!$A$2:$D$100"),4,FALSE),"No match found") and then ctrl shift enter and saw } symbol and the text read No match found


In A2 of mechanics i typed =IF(B2="","",B2&C2) to A501

In C2 of mechanics I typed i typed =IFERROR(VLOOKUP(A2&B2,INDIRECT("'"&INDEX(ShtNames,MATCH(1,--(COUNTIF(INDIRECT("'"&ShtNames&"'!$A$2:$D$100"),A2)>0),0))&"'!$A$2:$D$100"),4,FALSE),"No match found") and then ctrl shift enter and saw } symbol and the text read No match found


I did this for the cell A2 of other sheets

Field Engineers

Sales Force Office 1

Sales Force Office 2

Delivery Service

Warahouse Supplie Chain


but still saw No Match Found.


What am i going wrong?
 
Hi nintynine,


Let me reiterate the steps again:


step1: Listing down the name of the sheets and give them a named range....which you have done correctly


Step2: From sheet 'mechanics' to 'Warahouse Supplie Chain' perform the same task as follows:

insert a column left to the Col A (FIRST). At A2 write the below formula in order to create the lookup value:


=IF(B2="","",B2&C2) and drag it down (say till 500 rows)


In mechanics sheet, no need to write the Vlookup formula as this is your reference sheet which you are using to fetch the data (feedback) and place it in master sheet.


Step3:Write the below vlookup formula at C2 of 'Central Office MasterSheet1' to fetch the data (feedback):


=IFERROR(VLOOKUP(A2&B2,INDIRECT("'"&INDEX(ShtNames,MATCH(1,--(COUNTIF(INDIRECT("'"&ShtNames&"'!$A$2:$D$100"),A2)>0),0))&"'!$A$2:$D$100"),4,FALSE),"No match found")


press CTRL+SHIFT+ENTER


You can repeat Step3 in C2 of 'Central Office MasterSheet 2' to get the feedback rating in this sheet as well.


Note:please follow the steps in orderly fashion as described. You should not write Vlookup formula first and then write IF formula to perform the merging operation(to create the lookup value) in other sheets


Please do as advised and let us now if this is fine.


Kaushik
 
I am very sorry sir, but i did as you told me, but no luck. Here is a link for the excel workbook with your formulas typed in

http://www.2shared.com/document/w0YcE7g3/Sort-local-lists-and-match-to-.html
 
You must log in or register to reply here.
Back
Top