Excel 2002 how to search a sheet & copy to another

  InXP 20:55 06 Apr 06
Locked

Anybody good with Microsoft Excel or know of a forum for Excel users?

I've got a list of staff who do overtime. They pick their names, dates, sessions etc from pick lists which I've set up (one row per event). At the end of the month I sort into name order. In a second worksheet I have the same information, but split up into individual staff, including payroll number and payments, which I print out for Payroll Dept.

To get information from the 1st worksheet to the 2nd, I currently highight all the rows relating to each member of staff, copy and then paste into the 2nd worksheet. Without using macros (which is diasbled at work), is there a way to get Excel to look up each member of staff on the 1st worksheet and to automatically copy each row that matches into the 2nd worksheet? I've tired VLOOKUP but it keeps picking up the same (first matched "record") each time.

It's a long shot, but I hope that someone will be able to help.

  VoG II 20:57 06 Apr 06

You need to include the 4th parameter in the VLOOKUP as FALSE and it will then look for an exact match.

  VoG II 21:00 06 Apr 06

Um, maybe not. Are you saying that the same name can occur multiple times on the 1st worksheet?

Why are macros disabled?

  InXP 21:11 06 Apr 06

The IT Dept won't allow macros. Yes, the samename can occur more than once, for example Wallace might do 5 sessions a month and Arnold 6 sessions etc. At the end of the month I sort in name order. I need to get Wallace's 5 entries into the 2nd worksheet etc. It's just that copying and pasting is tideous! VLOOKUP will pick up for example Wallace's first entry each time, whereas I need it to pick up the first entry, then move on and search for the next etc - a bit like the Find next option in Excel.

  VoG II 21:18 06 Apr 06

This is relatively simple to code in VBA but if I were to code it for you it would be a waste of your time and mine due to your IT department's rediculous attitude. Below is an example of a macro that does something similar. Perhaps you would care to show this to your IT department and ask them what harm they think it can do:

Sub Murge()
Dim FromSht As Worksheet, ToSht As Worksheet, LastRow
Dim iRow, jRow, REF, Found As Range
Dim Matched As Integer, Unmatched As Integer, NoREF As Integer, Addy As String
Matched = 0
Unmatched = 0
NoREF = 0
Application.Cursor = xlWait
Application.ScreenUpdating = False
Set ToSht = Sheets("Combined Data")
LastRow = ToSht.Cells(Cells.Rows.Count, 1).End(xlUp).Row
Set FromSht = Sheets("Biology Data")
For iRow = 3 To LastRow
Application.StatusBar = "Record " & iRow - 2 & " ..."
REF = ToSht.Cells(iRow, 1).Value
If REF <> "" Then
Set Found = FromSht.Columns(1).Find(REF)
If Found Is Nothing Then
Unmatched = Unmatched + 1
ToSht.Cells(iRow, 41).Value = "REF No not found in Biology Data"
Else
Matched = Matched + 1
jRow = Found.Row
Addy = "B" & jRow & ":Q" & jRow
FromSht.Range(Addy).Copy
Addy = "AO" & iRow
ToSht.Activate
Range(Addy).Select
ToSht.Paste
End If
Else
NoREF = NoREF + 1
ToSht.Cells(iRow, 41).Value = "REF No missing"
End If
Next iRow
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.StatusBar = False
Application.Cursor = xlDefault
MsgBox ("Databases merged" & Chr(13) & Matched & " records matched" _
& Chr(13) & Unmatched & " records not matched" & Chr(13) & NoREF _
& " no REF number")
End Sub

  InXP 21:26 06 Apr 06

Thanks VoG.

I'll try convincing the powers that be, in the IT Dept. The alternative would be for me to use the work laptop which is not networked and to which I've got administrator access, to run such a macro.

I'll let you know the outcome.

This thread is now locked and can not be replied to.

Surface Pro 5 News - release date, UK price, features, specs

Gear VR vs. Daydream: Which delivers the best VR experience?

iPad Pro 12.9 vs Surface Pro 5