combining data from 2 sheets

[Sri]

Hi..

I have data in 2 sheets which i want it to be displayed in the third sheet.
Please suggest me how to proceed with this...

------**------

Example :
Sheet 1: Sheet 2 :

No Name1 No Name2
1 abc 1 efg
2 eds 2 xyz
4 def 3 aaa
5 dss 6 ddd

The Result should be :
Sheet 3 :
No Name1 Name2
1 abc efg
2 eds xyz
3 aaa
4 def
5 dss
6 ddd

-------**--------

 

[KePaHa]

Under Name1 on sheet 3, something like this should work:

=IF(Sheet1!A1<>"", Sheet1!A1, "")

If Sheet1 A1 is empty, then do nothing, otherwise put in here what is in
cell A1.

 

[Sri]

Hi ..Thank you very much for responding .... Is there any way to pull the two
columns into 3rd sheet "NO" and "NAME1" ...resulting "NO" , "NAME1","NAME2"
in the third sheet ... Also all the serial numbers may not be same in the
first two sheets .... I am new to excel.... sorry to bother you .. Thank
you...

 

[Ron@Buy]

Sri

Paste this into cell B2 of Sheet3
=IF(ISNA(VLOOKUP(A2,Sheet1!$A$2:$B$100,2,FALSE)),"",VLOOKUP(A2,Sheet1!$A$2:$B$100,2,FALSE))
and copy down ( note I have assumed 99 rows of data - change the 100 to suit
your number of rows)
Copy and paste formula into cell C2 of Sheet3, change Sheet1 to Sheet2 and
copy down to suit your number of rows.
Hope this helps

 

[KePaHa]

Nice! And the only other thing to mention - I don't think Sri had the
numbers out in column A of sheet 3 to reference. So you can do that by
combining the two lists of numbers into one, then do a Data, Filter, Advanced
Filter, Copy to Another Location and extract 'Unique Values Only'. This can
then be copied into column A to serve the LOOKUPs that Ron posted.

 

[Sri]

Thank you very much Ron...

Sri

Paste this into cell B2 of Sheet3:
=IF(ISNA(VLOOKUP(A2,Sheet1!$A$2:$B$100,2,FALSE)),"",VLOOKUP(A2,Sheet1!$A$2:$B$100,2,FALSE))
and copy down ( note I have assumed 99 rows of data - change the 100 to suit
your number of rows)
Copy and paste formula into cell C2 of Sheet3, change Sheet1 to Sheet2 and
copy down to suit your number of rows.
Hope this helps

 

[Sri]

Ron..

I have 25 columns in each sheet and there are many sheets too . So is there
any better way to proceed or use the same query in all the columns.
Sri

 

[Ron@Buy]

Sri
Yes you should be able to adapt the formula.
If all your worksheets are set out in the same way and you have a column on
Sheet 3 for each of your worksheets. You should expand the $A$2:$B$100 in the
formulae to encompass your data (e.g $A$2:$Y$100) and amend Sheet(number) in
the formulae to the name of each worksheet.
Come back if this doesn't work

 

[Sri]

Sorry to bother you again Ron.... still it is giving data into only one
column. i have taken 4 columns now in my example and need to pull 3 columns
into master sheet
=IF(ISERROR(VLOOKUP($A2,Sheet1!$A$2:$D$11,2,FALSE)),"",VLOOKUP($A2,Sheet1!$A$2:$D$11,2,FALSE))
Please suggest

 

[Ron@Buy]

Sri
Sorry forgot to mention last time, change the number preceeding FALSE i.e.
currently the number 2 makes the vlookup column 2 (B) so change number
accordingly, 3 for column C, 4 for column D and so on.

 

[Sri]

Thank you...

Sri
Sorry forgot to mention last time, change the number preceeding FALSE i.e.
currently the number 2 makes the vlookup column 2 (B) so change number
accordingly, 3 for column C, 4 for column D and so on.

 

[Ron@Buy]

Glad I could help

Thank you...