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.

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.

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.

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.