Array/Lookups, etc...

[Jayjay]

Hi,

I'm trying to create a formula for the following senario...

I have a table of people (by row) who are progressing through the first part
of some training (worksheet 1) and I have a second table (worksheet 2) with
further training for those people who pass the first part.

I want the details of the people who pass to automatically populate the
second sheet but without blank rows, i.e. if numbers 1, 2, 4 & 8 pass from
sheet one (the column J cells show "Yes") I need them to appear in rows 1, 2,
3 & 4 on sheet 2.

However, there is a further ‘issue’...

I have found a formula that moves the names nicely but if, for example,
learner number 5 passes their test, they're details are moved over to the
second sheet and further information is recorded in the columns alongside
them.

However, if learner 4 then passes, their details are 'inserted' into the
second sheet above 5, so 5's 'further details' become 4's...

Any thoughts or ideas?...

Thank you!

Jayjay

 

[Toppers]

I have some VBA code which will help BUT it requires a field (numeric ID?) in
Sheet1 which is unique to each person and that the sheet is sorted on this
field.

Let me know is this would be useful.

 

[Jayjay]

This would be brilliant - all of the Learners already have individual
(unique) 'Learner Numbers'.

Thank you.

Jayjay

 

[Toppers]

Try this:

Match is on an "ID" in column A and MUST be in ascending order.

If there are any problems, post sample w/book to:

toppers at REMOVETHISjohntopley.fsnet.co.uk


Sub Transfer1_2()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow1 As Long, lastrow2 As Long
Dim inrow2 As Long
Dim rng2 As Range

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")


With ws2
lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
Set rng2 = .Range("a1:A" & lastrow2)
End With

irow2 = 1
With ws1
lastrow1 = .Cells(Rows.Count, "A").End(xlUp).Row
For irow1 = 2 To lastrow1
If .Cells(irow1, 10) = "Yes" Then
res = Application.Match(.Cells(irow1, 1), rng2, 0) '<=== matches on
ID in column A
If IsError(res) Then
res = Application.Match(.Cells(irow1, 1), rng2, 1)
If IsError(res) Then
irow2 = irow2 + 1
.Cells(irow1, "A").EntireRow.Copy ws2.Cells(irow2, "A")
Else
res = res + 1
ws2.Cells(res, "A").EntireRow.Insert Shift:=xlDown
.Cells(irow1, "A").EntireRow.Copy ws2.Cells(res, "A")
End If
End If
End If
Next irow1
End With
End Sub

 

[Jayjay]

Hi Toppers,

Thank you again, this is great.

Unfortunately, I'm obviously doing something wrong as I keep getting a
Syntax Error at the 'ID' line.

I didn't understand where I can post a sample w/book to though - What do I
need to do?

Thank you,

Jayjay