Adjusting Copy Pasted Rows1

Sometimes when we copy paste the data from the web our data gets distorted i.e. becomes a single columnar.. seriously I would just shoot my self if I have to cut and past each gender against the name (in the picture above) and more so if there are a thousand rows.. could there be an easy way to it, YES indeed 😎 ! and that too without VBA (macro)

The Logic to solve this problem

The logic is pretty straight. We just need to bring the gender (M/F) in front of the name and then delete the blank rows. For getting the gender in front of the name we will write a small formula

=IF(MOD(ROW(Current Row),2)=1,Gender,””)

What we are trying to do is if the current row is Odd (i.e. the reminder of Row number divided by 2 is 1) then you want to see the gender kept in the next row else give a blank cell. Just in case you need more clarity

  • The ROW function will give the row number of the cell selected
  • The MOD function will take that row number and divide it by 2 and give the reminder (and we are checking if the remainder is =1 then we want to see the Gender, else not)

Take a look how it works!!Adjusting Copy Pasted Rows2

Do a Paste Special (Values) and remove the empty rows

Simply Copy and Paste Special as Values for the Gender and then apply a filter on the Gender column to delete the blank cells. When you apply a filter and want to select only the visible rows then select that column/area and use the shortcut ALT ;

Adjusting Copy Pasted Rows3

Please use comments to tell simple ways to solve this problem

 

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI