How to Combine Data in 2 Table of different Worksheets?

[ameenakbar]

I have two worksheet in excel file. in Sheet 1, there are Identity in Column A and Names on Column B. In Sheet 2 there is Identity in Column A and Score in Column B. There are 45000+ records (rows) in Sheet 1 and 22000+ records (rows) in Sheet 2. Mean Sheet 2 is half from Sheet 1. I want to put SCORE in Column C of Sheet 1. This column will half blank after completion, as its source is not complete. I dont want to use VLookup formula. That give nearest answer, but i want exact. I want that Identity which match from both sheets show in Column C of Sheet A. The Identity which are not in Sheet 2, show blank in Column C of Sheet A.

 

[hiker95]

ameenakbar,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

Are you using a PC or a Mac?

Can you post a screenshot of each of the actual raw data worksheets?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel.com | Excel Resources | Excel Seminars | Excel Products

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[ameenakbar]

Hi Dear,
Thank for quick reply. I am using windows Xp SP3, MS Office 2010.
Excel File Link is : https://app.box.com/s/89tra7gqmtxfqrqc6kot

Screen shot is at :https://app.box.com/s/ynso2sls9npenp96rq6x

 

[hiker95]

ameenakbar,

Sample raw data in worksheet Sheet2:

Excel 2007
	A	B
1	Identity	Score
2	2	6
3	3	7
4	5	8
5	7	5
6	9	7
7	11	4
8	12	56
9	15	16
10	16	12
11	18	11
12	20	10
13	22	9
14	23	6
15	26	8
16	30	12
17	31	11
18	33	21
19	35	24
20	41	23
21	43	15
22	45	12
23
Sheet2

And, in worksheet Sheet1:

Excel 2007
	A	B	C
1	Identity	Name	Score
2	1	Mr. Name 1
3	2	Mr. Name 2	6
4	3	Mr. Name 3	7
5	4	Mr. Name 4
6	5	Mr. Name 5	8
7	6	Mr. Name 6
8	7	Mr. Name 7	5
9	8	Mr. Name 8
10	9	Mr. Name 9	7
11	10	Mr. Name 10
12	11	Mr. Name 11	4
13	12	Mr. Name 12	56
14	13	Mr. Name 13
15	14	Mr. Name 14
16	15	Mr. Name 15	16
17	16	Mr. Name 16	12
18	17	Mr. Name 17
19	18	Mr. Name 18	11
20	19	Mr. Name 19
21	20	Mr. Name 20	10
Sheet1

Cell Formulas
Range	Formula
C2	=IF(ISERROR(VLOOKUP(A2,Sheet2!A:B,2,)),"",VLOOKUP(A2,Sheet2!A:B,2,))

The following VLOOKUP formula in cell C2, copied down:
=IF(ISERROR(VLOOKUP(A2,Sheet2!A:B,2,)),"",VLOOKUP(A2,Sheet2!A:B,2,))

 

[ameenakbar]

Its works, greatttttttt. Thank you, thank you, thank You, sooooooooooooooo much, Thank dear hiker 95, Mr. Excel and all other members, Its really a great forum for excel. I will keep in touch with this. Thanks again.

 

[hiker95]

ameenakbar,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[ameenakbar]

Thanks alot, By the way, what is the formula if i want text "Not Available" instead of blank cell in C2 of Sheet 1?

 

[azumi]

Change part "" with "Not Available"

 

[ameenakbar]

Great, and what is the formula if i want in D2 of Sheet1 that, If C2 is blank then D2 is Not Available, if not blank then put value of C2.

 

[hiker95]

ameenakbar,

Something like this?

Excel 2007
	A	B	C	D
1	Identity	Name	Score
2	1	Mr. Name 1	Not Available	Not Available
3	2	Mr. Name 2	6	6
4	3	Mr. Name 3	7	7
5	4	Mr. Name 4	Not Available	Not Available
6	5	Mr. Name 5	8	8
7	6	Mr. Name 6	Not Available	Not Available
8	7	Mr. Name 7	5	5
9	8	Mr. Name 8	Not Available	Not Available
10	9	Mr. Name 9	7	7
11	10	Mr. Name 10	Not Available	Not Available
12	11	Mr. Name 11	4	4
13	12	Mr. Name 12	56	56
14	13	Mr. Name 13	Not Available	Not Available
15	14	Mr. Name 14	Not Available	Not Available
16	15	Mr. Name 15	16	16
17	16	Mr. Name 16	12	12
18	17	Mr. Name 17	Not Available	Not Available
19	18	Mr. Name 18	11	11
20	19	Mr. Name 19	Not Available	Not Available
21	20	Mr. Name 20	10	10
22
Sheet1

Cell Formulas
Range	Formula
C2	=IF(ISERROR(VLOOKUP(A2,Sheet2!A:B,2,)),"Not Available",VLOOKUP(A2,Sheet2!A:B,2,))
D2	=IF(C2="","Not Available",IF(C2<>"",C2))