Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function should I use to pull those records that match
I have a huge table and want to look at records where Column A = "No" and
Column D, E, and G = the same values as an identified record. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function should I use to pull those records that match
Hi,
Try this in H1 and drag down =AND(A1="No",D1<"",D1=E1,E1=G1) Mike "Helen" wrote: I have a huge table and want to look at records where Column A = "No" and Column D, E, and G = the same values as an identified record. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function should I use to pull those records that match
I don't understand your formula. It looks like you're comparing data in two
columns for the same record (D1=E1, etc.). Perhaps I didn't explain my problem adequately. I have a spreadsheet with over a thousand records. Each record has data in 50 columns. I want to send to a second spreadsheet every record (1 through 1000) that matches the data in Columns D, E, and G of record 1001. It seems reasonable to me that a function exists that will allow me to do this other than manually. "Mike H" wrote: Hi, Try this in H1 and drag down =AND(A1="No",D1<"",D1=E1,E1=G1) Mike "Helen" wrote: I have a huge table and want to look at records where Column A = "No" and Column D, E, and G = the same values as an identified record. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function should I use to pull those records that match
One way is a VBA loop that looks through column A and copies the
appropriate rows. Paste this code in a standard module (see http://www.rondebruin.nl/code.htm for placement assistance). This code assumes the identified record is in H1001. Replace "myBook" with the name of the actual workbook you are copying the rows to. Sub CopyRecords() Dim rng As Excel.Range Dim cell As Excel.Range Dim NewBook As Excel.Workbook Set NewBook = Workbooks("myBook") Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeCons tants, 2) 'text values only For Each cell In rng If cell.Value = "No" Then If (cell.Offset(0,3).Value = Cells(1001,8)) And _ (cell.Offset(0,4).Value = Cells(1001,8)) And _ (cell.Offset(0,6).Value = Cells(1001,8)) Then cell.EntireRow.Copy _ NewBook.Sheets(1).Range("A65536").End(xlUp).Offset (1,0) End If End If Next cell End Sub This is air code so please test it first. Even faster is a VBA loop that uses the .Find method, for example: http://www.ozgrid.com/VBA/VBALoops.htm HTH, JP On Apr 2, 6:32*pm, Helen wrote: I don't understand your formula. *It looks like you're comparing data in two columns for the same record (D1=E1, etc.). Perhaps I didn't explain my problem adequately. *I have a spreadsheet with over a thousand records. *Each record has data in 50 columns. *I want to send to a second spreadsheet every record (1 through 1000) that matches the data in Columns D, E, and G of record 1001. It seems reasonable to me that a function exists that will allow me to do this other than manually. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
to pull from multiple sheets-index,match,vlookup,if,and,or??? | Excel Worksheet Functions | |||
match and pull together | Excel Worksheet Functions | |||
Can Index/Match pull lookup_value from a combo box? | Excel Discussion (Misc queries) | |||
How do I pull a particular set of records from a larger file? | Excel Discussion (Misc queries) | |||
Can you match records from two different worksheets | Excel Worksheet Functions |