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

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" wrote:

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

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

Thank you.

Jayjay

"Toppers" wrote:

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" wrote:

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

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" wrote:

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

Thank you.

Jayjay

"Toppers" wrote:

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" wrote:

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

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

"Toppers" wrote:

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" wrote:

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

Thank you.

Jayjay

"Toppers" wrote:

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" wrote:

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