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!!
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 ;
Please use comments to tell simple ways to solve this problem
