moving data depending on another column

neilp

Well-known Member
Joined
Jul 5, 2004
Messages
515
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Input this at C5 and Press CTRL+SHIFT+ENTER tnen Drag it down:
Excel Formula:
=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

Well-known Member
Joined
Jul 5, 2004
Messages
515
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try This:
Excel Formula:
=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

Well-known Member
Joined
Jul 5, 2004
Messages
515

ADVERTISEMENT

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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Sorry.
Press CTRL+SHiFT+ENTER
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,798
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you want a macro, how about
VBA Code:
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’)
 
Solution

neilp

Well-known Member
Joined
Jul 5, 2004
Messages
515
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,798
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,157
Messages
5,629,023
Members
416,359
Latest member
Juena

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top