|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to match one list with another and return certain values from corresponding cells in excel
Hello everyone.
Please i need help with my spreadsheet that has multiple information on it. What i want to do is to pick each years TOTAL SCORE from the yearly sheets and populate the same total scores into the appropriate columns in the cumulative sheet. And i am supposed to get these scores by matching 2 unique values which are the NAMES, AND ORACLE NO. And if these informations are present, then it will return the total score of that individual into its appropriate year. The name and oracle number is unique to individuals except where we probably have typographical errors else it's a unique value to all individuals. Please anyone help me with this task as i have tried severally but all to no avail. Thank you all. |
#2
|
||||
|
||||
Try:
=SUMIFS(INDIRECT("'year "&RIGHT(F$1,2)&"'!$G:$G"),INDIRECT("'year "&RIGHT(F$1,2)&"'!$B:$B"),$C2,INDIRECT("'year "&RIGHT(F$1,2)&"'!$D:$D"),$D2) copied across and down |
#3
|
|||
|
|||
Quote:
Thank you very much for your response. The formula you sent worked perfectly well in the test attachment I sent at the beginning of the thread however when I used it on the actual document, it did not work and I can't explain why. Please I need more assistance and I have also attached a copy of the actual document here |
#4
|
||||
|
||||
The matches must be exact matches. I noted many times you have Mr or Mrs in brackets following the names in the 2015, 2016 and 2017 but in the cumulative sheet the Mr and Mrs is in front of the names...
|
#5
|
||||
|
||||
Review the names in the cummulative sheet, they do not exactly match the names in the years sheet. You were not consistent on the placements of the "Mrs" and "Mr". Also, if you want to be alerted of duplicated Oracle Numbers, a simple conditional formatting will do that for you.
|
#6
|
|||
|
|||
You are very correct, the names are not consistent because I got them from different sources and I don't have control over it.
Can you please help me use oy the oracle numbers to update the cumulative sheet then? So if there are duplicate oracles, when can sort that update manually atlease it will reduce the stress of going through all the files. |
#7
|
||||
|
||||
Are you okay if I replace the registration number with the name?
|
#8
|
||||
|
||||
I'm sorry NBVC, I missed reading your second post. At any rate, Oluagbe, I removed the name in the criteria but it would be best if you add the registration number in the criteria. You change "$B:$B" to "$C:$C" (The "C" is the column number of the registration numbers in the year sheets) and the C2 to E2. C2 is the cell address of the name in the cummulative sheet while E2 is the registration number.
I've placed the conditional formatting in columns D and E. Try copying a number and pasting below it, the duplicate number should be colored. |
#9
|
|||
|
|||
Yes it's ok with me, in as much as it will make the job more easier.
|
#10
|
|||
|
|||
Quote:
Thank you very much. I was thinking what if we use just one criteria which is more stable that is the oracle numbers? This should even populate more cells with the actual scores from the yearly sheets into the cumulative sheet. |
#11
|
||||
|
||||
Quote:
I deleted the columns with no data and several rows at the bottom in order to meet the limit of not more than 500 kb. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Index Match Formula to return value from a list with criteria | Marcia | Excel | 4 | 10-26-2018 08:59 AM |
Loop through cells and return only unique values | trevorc | Excel Programming | 4 | 10-11-2018 03:49 PM |
Excel Match return different row number | kimak | Excel | 1 | 03-01-2017 09:39 AM |
Compare cells in 2 columns in separate WBs and return data in Col 38-40 if there is match | krispykreme | Excel Programming | 1 | 09-09-2016 11:25 AM |
Excel Formula: return a range of cells that match | tinfanide | Excel | 4 | 08-30-2014 07:03 AM |