moving data depending on another column

[neilp]

hi
i have 15 rows of data in sheet2 (A2:A16). In the column B2:b16 i have a data validation with a drop down giving the option Yes and No. they are all set as no to begin with.

i would like a way where by, i can change a few of these to yes, then click a button and the code would copy only the data that has a yes in the corresponding column.

this data would then be pasted into sheet1, cells C5:c19, and finally, if any cells remain blank, the whole row on sheet 1 should be deleted.

is this possible?

thanks in advance

neil

 

[maabadi]

Input this at C5 and Press CTRL+SHIFT+ENTER tnen Drag it down:

=INDEX(Sheet2!$A$2:$A$16,SMALL(IF("Yes"=Sheet2!$B$2:$B$16,ROW(Sheet2!$A$2:$A$16)-ROW(Sheet2!$A$2)+1),ROWS(Sheet1!$C$5:C5))

 

[neilp]

Thanks Maabadi. It works in copying the info across if the word "yes" is there, but returns an error if the word "No" is there, rather than deleting the rows.

 

[maabadi]

Try This:

=IFNA(INDEX(Sheet2!$A$2:$A$16,SMALL(IF("Yes"=Sheet2!$B$2:$B$16,ROW(Sheet2!$A$2:$A$16)-ROW(Sheet2!$A$2)+1),ROWS(Sheet1!$C$5:C5)),"")

 

[neilp]

Comes up with "the formula is missing an opening or closing parenthesis"

 

[maabadi]

Sorry.
Press CTRL+SHiFT+ENTER

=IFNA(INDEX(Sheet2!$A$2:$A$16,SMALL(IF("Yes"=Sheet2!$B$2:$B$16,ROW(Sheet2!$A$2:$A$16)-ROW(Sheet2!$A$2)+1),ROWS(Sheet1!$C$5:C5))),"")

 

[Fluff]

If you want a macro, how about

Sub neilp()
   Dim Lst As Variant
   
   With Sheets("Sheet2").Range("A2:A16")
      Lst = Filter(.Worksheet.Evaluate("transpose(if(" & .Offset(, 1).Address & "=""Yes""," & .Address & ",""|""))"), "|", False)
   End With
   With Sheets("Sheet1")
      .Range("C5:c19").ClearContents
      .Range("C5").Resize(UBound(Lst) + 1).Value = Application.Transpose(Lst)
   End With
End Sub

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[neilp]

Hi Fluff,

Purely for how it will be used, a macro is the preferable way, and sure enough, it copies the data perfect.

thanks

Neil

 

[Fluff]

Glad we could help & thanks for the feedback.