HELP sort 2 worksheets

[Guest]

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

 

[Frank Kabel]

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

 

[Bob Phillips]

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)

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.