Copy automatically columns if the criteria is met

Solved/Closed
kerwin - Jul 29, 2021 at 05:59 AM
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 - Jul 29, 2021 at 12:11 PM
Hello,

I have simple workbook with 2 sheets. Sheet1 contains columns "Risk ID", "Risk" and "Response". Data would be for example ID1, "Cyber attack", and "Mitigate". I want to copy this information into Sheet 2, but only when "Response" is "Mitigate". If there is something another, like "Accept" the whole data row should be jumped and not copied into Sheet2. Can can I do this without any VBA code?


System Configuration: Windows / Chrome 91.0.4472.114

1 reply

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Jul 29, 2021 at 12:11 PM
Hi Kerwin,

Why not use VBA? It would be the best and easiest way.

If you really want to use formula's I guess you could use:
Sheet2 A2: =IF(Sheet1!$C2="Mitigate",Sheet1!A2,"")

Drag the cell to C2, then select A2 to C2 and drag that selection down.

On sheet2 you can apply an autofilter (found on the data ribbon) to get the data next to each other, without empty rows in between.

Best regards,
Trowa
0