Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-06-2022, 01:26 PM
laith93 laith93 is offline Match cells between workbooks Windows 10 Match cells between workbooks Office 2019
Competent Performer
Match cells between workbooks
 
Join Date: Jul 2021
Posts: 117
laith93 is on a distinguished road
Default 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
Attached Files
File Type: xlsx 1.xlsx (8.8 KB, 7 views)
File Type: xlsx 2.xlsx (8.1 KB, 7 views)
Reply With Quote
  #2  
Old 09-11-2022, 07:53 AM
fjns fjns is offline Match cells between workbooks Windows 10 Match cells between workbooks Office 2019
Novice
 
Join Date: Sep 2022
Location: Hungary, Szeged
Posts: 16
fjns is on a distinguished road
Default

Hi, see in attached file a possible solution...
Attached Files
File Type: xlsx 2new.xlsx (9.6 KB, 5 views)
Reply With Quote
  #3  
Old 09-11-2022, 11:10 PM
ArviLaanemets ArviLaanemets is offline Match cells between workbooks Windows 8 Match cells between workbooks Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Attached Files
File Type: xlsx TestScores.xlsx (13.6 KB, 6 views)
Reply With Quote
Reply

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
Match cells between workbooks Question about Index match formula to copy all of cells in row dmcg9760 Excel 1 11-08-2015 01:41 AM
Match cells between workbooks Match Multi Cells gdavey Excel Programming 1 09-06-2015 11:09 AM
Match cells between workbooks Index/match accross two workbooks Granpa49 Excel 1 06-25-2015 10:03 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:50 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft