Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default (Please Help) Merge coloms based on equal rows with information

Hi
I have a sheet with this information

Card# Name Surname
1 A Alast
2 B Blast
3 C Clast
4 D Dlast

and another sheet with this information

Student# Name Surname
22 B Blast
11 A Alast
33 C Clast
55 E Elast

I need to extract Card# and Student# by comparing Name and Surname and
get this list

Card# Student# Name Surname
1 11 A Alast
2 22 B Blast
3 33 C Clast

many thanks in advance
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 25
Default (Please Help) Merge coloms based on equal rows with information

Add a formula to column D of Sheet1 and Sheet2 which concatenates the
names together, i.e. in D2 of both sheets:

=B2&C2

and copy this down as far as needed.

Then in Sheet3, assuming you have the names in columns C and D, put
this formula in A2:

=INDEX(Sheet1!A:A,MATCH(C2&D2,Sheet1!D:D,0))

and this in B2:

=INDEX(Sheet2!A:A,MATCH(C2&D2,Sheet2!D:D,0))

Then copy these down as far as required.

Hope this helps.

Pete

On Aug 22, 10:38*am, khers wrote:
Hi
I have a sheet with this information

Card# * Name * *Surname
1 * * * * * * * A * * * * * * * Alast
2 * * * * * * * B * * * * * * * Blast
3 * * * * * * * C * * * * * * * Clast
4 * * * * * * * D * * * * * * * Dlast

and another sheet with this information

Student# * * * *Name * *Surname
22 * * * * * * *B * * * * * * * Blast
11 * * * * * * *A * * * * * * * Alast
33 * * * * * * *C * * * * * * * Clast
55 * * * * * * *E * * * * * * * Elast

I need to extract Card# and Student# by comparing Name and Surname and
get this list

Card# * Student# * * * *Name * *Surname
1 * * * * * * * 11 * * * * * * *A * * * * * * * Alast
2 * * * * * * * 22 * * * * * * *B * * * * * * * Blast
3 * * * * * * * 33 * * * * * * *C * * * * * * * Clast

many thanks in advance


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I count MTD coloms? Manasvi Excel Worksheet Functions 3 September 12th 08 02:10 PM
Fixing the number of Coloms and rows. Hunter Excel Discussion (Misc queries) 2 January 7th 07 10:56 AM
Merge rows into one based on value dpb Excel Discussion (Misc queries) 0 September 21st 06 04:18 PM
Is there a way to hide worksheets and/or rows/columns based on information enter into a particular cell of range of cells? Marc New Users to Excel 1 March 10th 06 06:10 PM
Looking up information based on Columns and Rows Sally J Excel Worksheet Functions 6 December 16th 05 10:18 PM


All times are GMT +1. The time now is 03:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"