PDA

View Full Version : [SOLVED] Loop in selection



Agnethe
06-22-2017, 02:11 AM
Hi
Could anyone please help me with this issue: I have sheet showing all of the treatments given to a group of animals.
Some of the animals have received several treatments. I only want to keep treatments if the animal in question has been given treatment A.
I would like to find out if there is a trend in the treatments eg. When an animal has been treated with A the animal has also been treated with D and treatment E.
Please see example attached.

How can I do this?



19567

mana
06-22-2017, 03:03 AM
C2:=IF(COUNTIFS(A:A,A2,B:B,"A"),"Save","Delete")

Agnethe
06-26-2017, 04:23 AM
Hi mana

Unfortunately this does not work, it comes with an error message invalid code.

offthelip
06-26-2017, 05:04 AM
thy this code,

I have assumed the entries are in columns A and B and the results is pasted into columns F and G


Sub movedata()
lastrow = Cells(Rows.Count, 1).End(xlUp).Row


inarr = Range(Cells(2, 1), Cells(lastrow, 2))
Range(Cells(2, 6), Cells(lastrow, 7)) = ""
outarr = Range(Cells(2, 6), Cells(lastrow, 7))
For i = 1 To lastrow - 1
If inarr(i, 2) = "A" Then
outarr(i, 1) = inarr(i, 1)
outarr(i, 2) = inarr(i, 2)
' find all the other entries of this cow
For j = 1 To lastrow - 1
If i <> j Then
If inarr(i, 1) = inarr(j, 1) Then
outarr(j, 1) = inarr(j, 1)
outarr(j, 2) = inarr(j, 2)
End If
End If
Next j
End If
Next i
Range(Cells(2, 6), Cells(lastrow, 7)) = outarr


End Sub

Agnethe
06-29-2017, 02:54 AM
Thank you very much - this worked!

mdmackillop
06-29-2017, 03:08 AM
Hi Agnethe
For the future, please post sample data in workbooks(Go Advanced/Manage Attachments), not as pictures
Regards
MD