extracting multiple rows of data from another worksheet

I have one worksheet, full of data.  In another worksheet, I would like to search through the data individuals who belong to a certain group and then extract the entire row of information for that particular individual into a new worksheet.  How can I achieve this using formulas?

EggHeadCafe - Software Developer Portal of Choice 
Silverlight Modal Dialog With Custom User Controls
http://www.eggheadcafe.com/tutorials/aspnet/a01af6c8-c067-4fdd-bc01-d47cf3410feb/silverlight-modal-dialog.aspx
0
June
11/19/2009 2:30:03 AM
excel 39879 articles. 2 followers. Follow

1 Replies
2625 Views

Similar Articles

[PageSpeed] 16

Try something like this adapted to your data, column and rows.  This will 
return 12 columns of data on the row of the lookup value that matches A19.

=VLOOKUP(A19,Sheet2!$A$2:$M$7,{2,3,4,5,6,7,8,9,10,11,12,13},0)

Select 12 columns in the row you want the data to be returned to.  While 
still selected type in the formula above.  Commit with Ctrl+Shift+Enter. 
You should get {  } around the formula.


So...

A19 is the lookup value, probably a name.
Sheet2!$A$2:$M$7 is the Table_Array (names and data of the individuals).
{2,3,4,5,6,7,8,9,10,11,12,13} is the Col_index_num, 12 in this example.
,0)) is the Range_lookup, meaning an exact match. (you can use FALSE also)

In this example the Table_Array is on sheet 2 but it could be on the same 
sheet as the formula and would look like this

=VLOOKUP(A19,A2:M7,{2,3,4,5,6,7,8,9,10,11,12,13},0)

If you want to make changes to the formula later, say to adapt to new data 
in the table_array or change the size of the table, you will need to select 
all 12 of the cells in the row of the formula, make your changes in the 
formula and then again commit with Ctrl+Shift+Enter.

If you want to trap for an error of no match, try this entered the same way. 
All on one line in the formula bar unless the formula bar wraps it, which it 
does on my sheet.  If an error, then a blank return.  Or exchange the "" in 
the formula for "No match" for a more graphic description.

=IF(ISNA(VLOOKUP(A19,Sheet2!$A$2:$M$7,{2,3,4,5,6,7,8,9,10,11,12,13},0)),"",VLOOKUP(A19,Sheet2!$A$2:$M$7,{2,3,4,5,6,7,8,9,10,11,12,13},0))

A side note on the {  }'s.  {2,3,4,5,6,7,8,9,10,11,12,13}  in the formula 
you do type in the {  }.  With the array enter Excel puts them around the 
formula.

HTH
Regards,
Howard

<June Chin> wrote in message news:20091118212959june.cfs@gmail.com...
>I have one worksheet, full of data.  In another worksheet, I would like to 
>search through the data individuals who belong to a certain group and then 
>extract the entire row of information for that particular individual into a 
>new worksheet.  How can I achieve this using formulas?
>
> EggHeadCafe - Software Developer Portal of Choice
> Silverlight Modal Dialog With Custom User Controls
> http://www.eggheadcafe.com/tutorials/aspnet/a01af6c8-c067-4fdd-bc01-d47cf3410feb/silverlight-modal-dialog.aspx 


0
L
11/19/2009 3:47:42 AM
Reply:

Similar Artilces:

transferring data in rows of one table to columns of another table
Hi All, We are working with FCC station data that puts some simple numeric data in one file, arranged in a number of consecutive rows for each station. The next station's data follows consecutively. Each group of rows that are common to a station share an index number, while the next group uses its own separate number. A separate file contains the main information fields in a table of separate rows, or records, along with a matching index number. We'd like to move the numeric data in a group of rows that share the index # for a station, to a series of new fields add...

Matching call data based on date, time and number called to give c
I have the following two tables on different sheets, both containing other information within them that is irrelevant in my question. SHEET 1 Date - Time - Number Called Duration Cost 01/02/10 09:05:21 01234123456 02:16 0.50 SHEET 2 Date - Time Number Called - Duration Extension 01/02/10 09:07:56 01234123456 02:15 1234 01/02/10 15:30:45 01234123456 01:59 1234 Sheet 2 HAS A LOT MORE RECORDS THAN SHEET 1 AND THERE ARE A NUMBER OF CALLS TO THE SAME NUM...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

Page Break Separator Row
I have a page/section/detail layout with tables that may or may not repeating and continue across pages. I've followed Chris Hay's hack to get the "continued..." at the top of the next page but the report design requires a blank row immediately after the last detail row before it continues to the following page. The blank row will then close the table with a border. Is this possible? I have made countleess attempts. Thanks in advance ...

displaying multiple e-mail addresses
I am using Outlook 2000 with Windows XP and can not get my address book to display multiple e-mail addresses for my contacts. I would like to see all of the e-mail addresses which are loaded for a particular contact when I open the address book and/or when I select "To" or "Cc" to add an address to an e-mail. My work computer (Outlook 2000 and Windows 98) displays multiple entries for contacts with multiple e-mail addresses (E-mail, E-mail 2, E-mail 3). Can anyone tell me how to change this display setting? In what mail support mode are you using Outlook? If it's ...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

how do i form 2 lines in 1 row?
I'm guessing you're looking to force text in a cell to wrap into the next line of the same cell. If that's true...... Edit the text and move the insertion point to where you want to wrap the text. Hold down the [Alt] key and press [Enter] Does that help? ---------------------------------- Regards, Ron Microsoft MVP (Excel) "MSEXCELROOKIE" <MSEXCELROOKIE@discussions.microsoft.com> wrote in message news:951AA059-1613-4A62-AAA6-7890AF6F84A6@microsoft.com... > In one cell type this is line one then Alt + ENTER then type this is line two. The Alt ...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

Recovering Data from MDBDATA
My Server is now not up... As My Global Catalogue Server just crashed. I tried level best to make another but whole effort just was a waste.... Problem is now I have made another server with New Domain. (New One) . How can I retrive mails from MDBData File of the Previus Server. -- Zeshan ------------------------------------------------------------------------ Posted via http://www.webservertalk.com ------------------------------------------------------------------------ View this thread: http://www.webservertalk.com/message1020038.html www.ontrack.com/powercontrols -- John Oliver, Jr...

Export/Import Acces Table to another Acces table/file
Hi, Is anybody can help me how to export/import table to a new table/file only using 1 click?. I am sure, we'll use Visual Basic, but I don't know how. Thanks in advance, I am appreciated it. KT -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200707/1 I suspect you will need to look at the TransferDatabase method of the DoCmd object. See the Access help. Steve "KimTong via AccessMonster.com" wrote: > Hi, > > Is anybody can help me how to export/import table to a new table/file only > using 1 ...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

Pulling data from multiple worksheets
I have one worksheet with a listing of client numbers and names. I would like to be able to type in a client number on another worksheet and have the client name automatically populate. Look at the Vlookup function or the combination of Index / Match. Vlookup will be a little more straight forward for a first timer but Index / Match is generally speaking a more flexible and less error prone solution. -- HTH... Jim Thomlinson "parthur" wrote: > I have one worksheet with a listing of client numbers and names. I would > like to be able to type in a clie...

Multiple Signatures
I have my email set up to allow multiple signatures. My outlook is set up to use 2 POP3 accounts. In the signature settings I select the signature to use for each account. When I create a new message, the default account comes up with it's set signature. When I select the alternate Pop3 account, the signature remains the defaults and not the signature that was associated with that account. Any ideas. On my older version of Outlook express, it would automatically switch the signatures when I selected an alternate account to send from. Any ideas? I've tried deleting the ...

Subtotal data excluding negitive numbers.
I have a spreadsheet contains lots information, each one linked to some other spreadsheet. In column =93A=94 some numbers are positive and some are negative. What I want to do is to subtotal(9,A3:A100) to show the total numbers when I filter something out. While I don=92t want these negative numbers to be calculated. I knew if I add a new column can resolve this problem, but if I the spreadsheet does not allow me to do that change, how can I put kind of formula in one single cell to show the subtotal result without adding the negative numbers? I have tried sumproduct but result only ...

Saving Outlook Calendar Data
I am using Windows Vista with 2007 Outlook for my appointments. I would like to save all appointments for the year to a back- up file should it be necessary to do a disk recovery at a later date. Can someone tell me the easiest way to do this? Many thanks. Gene L. ...

multiple modal dialogs
Hi, Anybody an idea how to have multiple modal dialogs in an application? I have an application that should allow multiple frame windows in the same time. The problem is, that these frame windows can have there own modal dialogs / message boxes. If a modal dialog (or message box) is open in one frame, and I switch to an other frame, and open there an other modal dialog (or a message box comes as a response to some event), the application enters in the modal loop if the new modal dialog. The problem is, that if the first modal dialog is closed with it's frame window, the frame window...

Don't Plot Point but leave in Data Table
I don't want to plot a couple of points in my chart but like to keep them in the data table. How can I accomplish in MS 2007? EXCEL 2007 Try the following (I have a bar chart on the screen in front of me as I do this. It has 9 countries on the Y axis; I will remove 2 of those countries). 1. Click the graph to highlight it. 2. Select Data. Select Data Source window launches. 3. Hit the:- Switch Row / Column button 4. I now have my countries in a list on the left hand side. - click on France and hit Remove - click on Germany and hit Remove 5. Hit t...

How can I set up a student data base using word 2003?
I am a music teacher and would like to set up a data base for my students including information regarding - exams, competitions and results. Names, ages, birthday reminders, parents details and goods and services delivered. I use Microsoft Office 2003. Any help would be appreciated. Many thanks. Decide what you want to keep track of and put each piece of information into separate cells. And one row per entry--don't put the data for one person on multiple rows. Try to keep your data on a single worksheet. If you thought that you'd want multiple worksheets (one for each class),...

extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time. The sum totals to more than 24 hours. I have to a cell with a cost per hour and I need to caclculate the total cost. The problem is that the hour function returns values in the range of 0-24. My current sum is 25:30 and the hour function return 1 and not 25. Any ideas how to bypass it? It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will. To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and a day is s...

Combining Data
I am trying to combine data from adjacent cells using the & command, for example =D2 & E2, is there some way I can put a space between the data. thanks Daniel On Tue, 19 Oct 2004 13:40:01 +1000, "Daniel Louwrens" <flint_dlouwrens@nospambigpond.com> wrote: >I am trying to combine data from adjacent cells using the & command, >for example =D2 & E2, is there some way I can put a space between the data. > >thanks > >Daniel > =D2 & " " & E2 --ron Great, thanks Daniel "Ron Rosenfeld" <ronrosenfeld@nospam.o...

Woolwich
Anyone else constantly downloading the same transactions from the Woolwich? Even though I've previously dowloaded transactions, it seems that the next time I download, I get a few of the same transactions. However, they appear as new ones that then have to be matched manually to existing ones (wearing when around 30 transactions have to be linked to existing ones). Any help appreciated..... ...

Redeem Gift Certificates at multiple locations
Does anyone know how I could create a Gift Card that could be redeemed at any of our stores? We are using Headquarters. Currently, the store that sells the card is the only one that can redeem it. Thanks! There is a Product called GIVEX out there for this purpose "Paul" <pbunn26@hotmail.com> wrote in message news:%23ICPCYLrEHA.3116@TK2MSFTNGP10.phx.gbl... > Does anyone know how I could create a Gift Card that could be redeemed at > any of our stores? We are using Headquarters. > Currently, the store that sells the card is the only one that can redeem it. > ...

Extra Row in Defined Print Area
I am working with Excel 2000. I have a spreadsheet that has some 1000 rows in it. I want to set the print area for rows 850 - 950 and that works fine EXCEPT that row 504 also gets included in the print area definition. I have cleared the print area repeatedly, I have set the print area repeated and in every case regardless of what rows/columns I include in a newly defined print area, row 504 gets included as the very first row of the print. If I don't define a print area, row 504 does not get printed. But as soon as I set a print area regardless of where it is in the spreadsheet, row 504 ...