Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Import data from one excel worksheet to another

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 27-11-2010
Member
 
Join Date: Nov 2010
Posts: 101
Import data from one excel worksheet to another
  

I require to import data from one excel worksheet to one more to exhibit all employee names from a exacting company.
For example:
A - B
Amb - Kali
Mart - Fred
M-Mart - Abi
Amb - Kali
Mart - Fred
M-Mart - Abi
Amb - Kali
Mart - Fred
What formula can I make use of in another sheet that is in sheet2 to exhibit a list of all Amb employees from sheet1?


Last edited by arjun rantu : 27-11-2010 at 10:54 PM.
Reply With Quote
  #2  
Old 27-11-2010
Member
 
Join Date: Nov 2008
Posts: 1,192
Re: Import data from one excel worksheet to another

Attempt this ARRAY formula on sheet 2. Enter the formula and place your lookup value of Amb in E1. Drag the formula down until it start creating blanks.
=IFERROR(INDEX(Sheet1!$B$1:$B$30,LARGE((Sheet1!$A$1:$A$30=$E$1)*ROW(Sheet1!$A$1:$A$30),COUNTIF(Sheet 1!$A$1:$A$30,$E$1)+1-ROW(A1))),"")
This is an array method that must be entered by pressing CTRL+Shift+Enter and not only Enter. If you do it accurately in that case Excel will place curly brackets {} around the formula. You cannot type this manually. If you edit the formula you have to enter it once more with CTRL+Shift+Enter.
Reply With Quote
  #3  
Old 27-11-2010
Member
 
Join Date: Nov 2008
Posts: 996
Re: Import data from one excel worksheet to another

I also want to do the same and don’t get any formula. I just see your post but I am not extremely higher. Can you please put in plain words to me what the dissimilar parts of the formula are? Obviously the data I am using here is not genuine. While I attempted adjusting the sheet or cell references to make use of in my genuine workbook it did not work. I think I will have an enhanced possibility if I know what I am working with.
Reply With Quote
  #4  
Old 27-11-2010
Member
 
Join Date: May 2008
Posts: 979
Re: Import data from one excel worksheet to another

Just try the following formula.
=IFERROR (formula,"")
The external IFERROR merely makes it revisit blanks while it runs out of matches. Now in every case I will bold the bit of the formula. Here I am talking about the primary bold bit are the values we are annoying to depart. Sheet1 is the name of the sheet which is followed by a “!” and $B$1:$B$30 is the range.
=IFERROR(INDEX(Sheet1!$B$1:$B$30,LARGE((Sheet1!$A$1:$A$20=$E$1)*ROW(Sheet1!$A$1:$A$30),COUNTIF(Sheet1!$A$1:$A$30,$E$1)+1-ROW(A1))),"")
Now at this time 3 bits of the formula are underlined and this is the range which encloses the lookup values. This range A1:A30 can be any volume but have to be the similar size as the range B1:B30
Reply With Quote
  #5  
Old 27-11-2010
Member
 
Join Date: Nov 2008
Posts: 1,054
Re: Import data from one excel worksheet to another

I advise by means of an easy macro in its place of a few strange formulas, since the latter needs manual interference in any case that is, predetermining the number of consequences or deciding how much times to copy down the formula. One such macro:
Code:
Sub multiLookup()
Dim sLookupTable As Range, sResultTable As Range, s As Range
Dim l As Long
Set sLookupTable = Worksheets ("sheet1").Range ("a1:a100")
Set sResultTable = Worksheets ("sheet1").Range ("b1:b100")
lookupValue = UCase(Selection)
l = 0
For every s In sLookupTable
   If lookupValue = UCase(s) Then
      l = l + 1
      Selection.Offset(l) = sResultTable.Cells(s.Row)
   End If
Next r
End Sub
Place the names of the search for values keen on a row of cells in Sheet2, e.g. A1, B1, C1, and so on. Choose every lookup value, push alt+F8, and run the macro. The multiple results will come into view in the column beneath the lookup value.
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Import data from one excel worksheet to another"
Thread Thread Starter Forum Replies Last Post
Excel 2007 Data Import via Bloomberg YajasK MS Office Support 2 09-02-2012 06:24 PM
Clicking on Worksheet on multiple Worksheets in the single Excel file not allow viewing the worksheet in Microsoft Excel 2010 Dipanwita Windows Software 8 04-12-2011 10:24 AM
How to Import all text data in Excel spreadsheet racer Tips & Tweaks 1 06-01-2010 09:38 PM
How do i import Excel data from password protected site Axton Networking & Security 3 21-10-2009 05:10 PM
Import data from excel to MPP and set to outline levels manishdk Microsoft Project 4 15-09-2008 11:40 PM


All times are GMT +5.5. The time now is 11:48 AM.