#1
|
|||
|
|||
Match cells between workbooks
Hi,
I have 2 excel files, 1 and 2 (see attached files) In 1, there are multiple sheets, named according to group names for students, and in each sheet, there are student names and their group names (two columns: Name and Group). In 2, there are student names and their scores in many subjects, but without their group name (two columns: Name and Scores). Now, I want to add a third column to file 2 for group name based on data in file 1, also the names in file 2 are random, so copy paste group names will not make sense So, I think it requires Match function or something else. Please any fix or any other idea to fix this issue? Thanks |
#2
|
|||
|
|||
Hi, see in attached file a possible solution...
|
#3
|
|||
|
|||
Here is another variant (based on fjns solution). I used defined Tables here, so all formulas will adjust automatically when new records are added, and used Data Validation to avoid any typing errors when new data are entered. And I renamed sheets on more logical way.
And my variant allows the same test to be run several times! The only case when the group isn't returned on Scores sheet, is when the group or test is not defined. Data Validation prevents this when data are entered manually, but this validation can be bypassed when user copy-pastes Data into Tables. Why using a single sheet do define tests is preferable? Because with your design, whenever you add a new group in future, you have to redesign the whole workbook (or all workbooks, when you have to go with several ones). With all tests defined in single Table, all you need is to add a new croup into Groups Table. |
Tags |
match, match/index, matching values |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula to copy specific cells if a match is met | applecust | Excel | 4 | 02-24-2021 08:03 AM |
Matching cells apparently don't match | Danny cool | Excel | 2 | 07-07-2020 07:31 AM |
Question about Index match formula to copy all of cells in row | dmcg9760 | Excel | 1 | 11-08-2015 01:41 AM |
Match Multi Cells | gdavey | Excel Programming | 1 | 09-06-2015 11:09 AM |
Index/match accross two workbooks | Granpa49 | Excel | 1 | 06-25-2015 10:03 PM |