H
Helen
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.
Column D, E, and G = the same values as an identified record.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
Mike H
Hi,
Try this in H1 and drag down
=AND(A1="No",D1<>"",D1=E1,E1=G1)
Mike
Try this in H1 and drag down
=AND(A1="No",D1<>"",D1=E1,E1=G1)
Mike
H
Helen
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.
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.
J
JP
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(xlCellTypeConstants, 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
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(xlCellTypeConstants, 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