H

I have a spreadsheet of updated address details with 795 records of all clients we we deal with. Each client has a unique ID which is in column A. I have various other spreadsheets representing loans given to specific clients at a set month. For instance, I have spreadsheets for Feb, Apr, Jun, Aug, OCt & Dec. In those sheets, certain clients were entitled to a loan so February may have 350 of the 795 clients that were entitled to a loan. Again, the same ID for each client is in column A

What I need to do is to run a formula to highlight clients from the list of 795 records that had a loan in February or better still, I need to find a way to only show the clients who had loans in February and delete those clients who didn't if this is possible. And if the deleted client blank rows could be moved up, that would be an added bonus

Any help would be gratly appreciated

By the way, the sheets I will be working with are in separate workbooks so please let me know if they need to be copied into the same book or even the same sheet. I am not to clued up with Excel

Thanks in advanc

Malyco

Hi
one way:
- Add a helper column for each month to check (lets say column C for
February)
- Assumption: your data starts in row 2 (row 1 is a heading row)
- enter the following in C2
=IF(ISNA(VLOOKUP(A2,'C:\temp\[february_book.xls]feb_sheet'!$A$1:$A$1000
,1,0)),"no Feb entry","Feb entry exists")
copy down (of course you have to change your pathname, workbookname and
worksheet name)
- now you can filter for this column or if you like filter all 'no Feb
entry' and delete these rows
- I woudl suggest doing this for all of your other sheets so you can
easily filter for a specific month


--
Regards
Frank Kabel
Frankfurt, Germany

Malycom wrote:
Hi

I have a spreadsheet of updated address details with 795 records of
all clients we we deal with. Each client has a unique ID which is in
column A. I have various other spreadsheets representing loans given
to specific clients at a set month. For instance, I have spreadsheets
for Feb, Apr, Jun, Aug, OCt & Dec. In those sheets, certain clients
were entitled to a loan so February may have 350 of the 795 clients
that were entitled to a loan. Again, the same ID for each client is
in column A.

What I need to do is to run a formula to highlight clients from the
list of 795 records that had a loan in February or better still, I
need to find a way to only show the clients who had loans in February
and delete those clients who didn't if this is possible. And if the
deleted client blank rows could be moved up, that would be an added
bonus.

Any help would be gratly appreciated.

By the way, the sheets I will be working with are in separate
workbooks so please let me know if they need to be copied into the
same book or even the same sheet. I am not to clued up with Excel.

Thanks in advance

Malycom

Malycom,

I worry about this as onjce they are deleted, what happens in March. However
....

Sub DeleteRows()
Dim oTargetWs As Worksheet
Dim oFind As Range
Dim cLastRow As Long
Dim i As Long

On Error Resume Next
Set oTargetWs = Workbooks("Loans.xls").Worksheets("Feb")
If oTargetWs Is Nothing Then
MsgBox "Target workbook must be opened"
Else
cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = cLastRow To 1 Step -1
Set oFind = oTargetWs.Columns(1).Find(Cells(i, "A").Value)
If Not oFind Is Nothing Then
Cells(i, "A").EntireRow.Delete
Set oFind = Nothing
End If
Next i
End If
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Malycom" wrote in message
...
Hi

I have a spreadsheet of updated address details with 795 records of all
clients we we deal with. Each client has a unique ID which is in column A.
I have various other spreadsheets representing loans given to specific
clients at a set month. For instance, I have spreadsheets for Feb, Apr, Jun,
Aug, OCt & Dec. In those sheets, certain clients were entitled to a loan so
February may have 350 of the 795 clients that were entitled to a loan.
Again, the same ID for each client is in column A.

What I need to do is to run a formula to highlight clients from the list
of 795 records that had a loan in February or better still, I need to find a
way to only show the clients who had loans in February and delete those
clients who didn't if this is possible. And if the deleted client blank
rows could be moved up, that would be an added bonus.

Any help would be gratly appreciated.

By the way, the sheets I will be working with are in separate workbooks so
please let me know if they need to be copied into the same book or even the
same sheet. I am not to clued up with Excel.

Thanks in advance

Malycom