How to extract multiple rows based on data in one column

I work at an insurance brokerage where we can download payment
statements in an Excel format. We would like to search a list of
policy numbers (as a group vs. individually) and then extract the
entire row the policy number falls in into another spreadsheet. Is
there a way to do this?
0
8/21/2008 9:52:13 PM
excel 39880 articles. 2 followers. Follow

7 Replies
431 Views

Similar Articles

[PageSpeed] 36

You would normally use VLOOKUP to do this, assuming the policy number
is in the first column of your data - if not, then you would use and
INDEX/MATCH combination. However, both VLOOKUP and MATCH will only
find the first match - I'm a bit confused as your heading talks about
extracting multiple rows, so do you have more than one row per policy
number?

Hope this helps.

Pete

On Aug 21, 10:52=A0pm, jmlincd...@gmail.com wrote:
> I work at an insurance brokerage where we can download payment
> statements in an Excel format. We would like to search a list of
> policy numbers (as a group vs. individually) and then extract the
> entire row the policy number falls in into another spreadsheet. Is
> there a way to do this?

0
pashurst (2576)
8/21/2008 10:19:53 PM
Thanks for the reply. Sorry for the confusing heading........Noob!
Anyway, each row lists a policy with names and commission. Is there a
way to use VLOOKUP or MATCH to search a group of values- the policy
numbers that are desired to be separated onto another spread sheet-
and then once found extract the entire row? Does this make any
sense???? Thanks again.
0
8/21/2008 10:28:47 PM
Can you tell me what columns you have in your existing data, and how
many rows of data does it occupy?

I assume that you will list the policy numbers of interest in column A
of Sheet2 starting with A2 (to allow for a header row), and I'll be
able to give you a formula (either VLOOKUP of INDEX/MATCH) to put in
B2 which can be copied across and down to extract all the data for
those policy numbers.

Pete

On Aug 21, 11:28=A0pm, jmlincd...@gmail.com wrote:
> Thanks for the reply. Sorry for the confusing heading........Noob!
> Anyway, each row lists a policy with names and commission. Is there a
> way to use VLOOKUP or MATCH to search a group of values- the policy
> numbers that are desired to be separated onto another spread sheet-
> and then once found extract the entire row? Does this make any
> sense???? Thanks again.

0
pashurst (2576)
8/21/2008 10:59:45 PM
That would be sweet!!!!! Ok, the commission sheet has columns 'A-K'
filled with information. Column 'C' contains the policy numbers. THANK
YOU again for your help!

0
8/22/2008 4:06:39 PM
Okay, with your list of policy numbers in column A of Sheet2, put this
formula in B2:

=3DIF(A2=3D"","",INDEX(Sheet1:A:A,MATCH(A2,Sheet1!C:C,0)))

and this one in C2:

=3DIF(A2=3D"","",INDEX(Sheet1:B:B,MATCH(A2,Sheet1!C:C,0)))

These will bring the corresponding data from columns A and B of Sheet1
respectively. For the other columns we can use VLOOKUP - put this
formula in D2 of Sheet2:

=3DIF(A2=3D"","",VLOOKUP($A2,Sheet1!$C:$K,COLUMN(B1),0))

and copy this formula across into E2:K2. You will then have all the
data for that policy number copied across into sheet2. Then you can
copy B2:K2 down for as many rows as you think you need.

Hope this helps.

Pete

On Aug 22, 5:06=A0pm, jmlincd...@gmail.com wrote:
> That would be sweet!!!!! Ok, the commission sheet has columns 'A-K'
> filled with information. Column 'C' contains the policy numbers. THANK
> YOU again for your help!

0
pashurst (2576)
8/22/2008 11:57:10 PM
WoW!!!
I will try it out-Thank you!!! I really appreciate your time and help!

0
8/25/2008 4:24:22 PM
You're welcome - let's hope it works for you.

Pete

On Aug 25, 5:24=A0pm, jmlincd...@gmail.com wrote:
> WoW!!!
> I will try it out-Thank you!!! I really appreciate your time and help!

0
pashurst (2576)
8/25/2008 7:42:03 PM
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...

Column Charts for Pie Slices?
Hi. What a great forum! Hope someone can help me (went back about a month and didn't see this question). My pie chart has 5 slices (# of employees for each of 5 departments). Each slice consists of 2 values (male, female). I'd like each slice to "point" to its own stacked column chart that shows how the slice is distributed across the two values. So the pie chart itself shows the distribution of employees across depatments, and the 5 associated stacked column charts show the relative number of male and female employees for each dept. Any ideas? Thanks! You could ma...

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...

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. ...

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),...

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...

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...

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 ...