Cleaning Data Automatically in Excel

Occasional Contributor

Hello, I am working on a project in excel and I need help to automate a few steps. I am trying to build something in excel that will automatically copy different cells in one sheet and paste it in another. I am not quite sure how to do this or what I need to do. Its almost like a loop. Also, there are cells in the first sheet that say either "Yes" or "No". If Yes, then there is more information that needs copied. If no, then the program should move to the next row. To protect project confidentiality, I created a new survey with dummy data to show what I mean by this. The first sheet is the raw data from the survey, and the second sheet is what I want it to be automated to. I am not shy to vba if there is a code or a function that will do this for me that would work. Thank you all for your time and help!  

3 Replies


Tables in both your sheets are returned by PowerQuery, why don't clean the data directly in it? That will be easier.

I only didn't catch the logic. First sheet is that Sheet0 or Sheet1? Yes and No are in many columns, which one to use? "If no, then the program should move to the next row" but you have No in some columns for both sheets.

I realize that it was confusing and difficult to catch the logic. I apologize for that and I thank you for trying to help. I posted a new discussion that explains my task a little better. If you would like to take a look at it, I'd greatly appreciate it. Thank you and have a great day!


If you mean this one it looks bit differently. Sorry, VBA is not my territory.