1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy data when cell is blank

Discussion in 'Ask an Excel Question' started by S. Das, Feb 10, 2018.

  1. S. Das

    S. Das Member

    Messages:
    88
    Hi all, I want a solution to my new problem.

    I have 5 columns, named Subject 1, Subject 2, ...., Subject 5. I want to copy the data under those columns to column G and H.

    Conditions:
    1) All the data of Subject 1 will copy to column G and all the data of Subject 3 will copy to column H.
    2) Where data of Subject 1 will end, Subject 2 will start in column G and for column H, after Subject 3, Subject 4 will start.
    3) After coping Subject 2 to column G and Subject 4 to column H, which will end first, Subject 5 will copy in that column.

    Challenges:
    1) I have 50 rows per room ( from row 3 to row 52, row 53 to row 102 like this) and 35 rooms.
    2) Conditions should fulfill per room basis.

    For better understand go through the attached file.

    Attached Files:

  2. vletm

    vletm Well-Known Member

    Messages:
    3,249
    S. Das
    If You're waiting 'Formula-Solution' then skip this ...
    otherwise You would check this ...
    Your 'rules' didn't give any clue, what to do if/then 'there is no space'.

    Attached Files:

  3. S. Das

    S. Das Member

    Messages:
    88
    Sir, your solution is good but need two changes.
    1) After pressing the button output should be the digits under column Subject 1 to Subject 5, not the text.

    2) Pressing "OK" button too much time is very much irritating. I want that after pressing the button "DO IT" it will be done automatically without pressing the "OK" button.
  4. S. Das

    S. Das Member

    Messages:
    88
    In this situation, no data will be there in consecutive columns i.e, if Subject 1 face this situation then, Subject 2 will be blank.
  5. vletm

    vletm Well-Known Member

    Messages:
    3,249
    S. Das
    1) Why Your sample results look like below... I can change.
    Screen Shot 2018-02-11 at 14.20.43.png
    case ) Your 'rules' didn't give any clue, what to do if/then 'there is no space'.
    I meant case like below!
    How to 'move' full 250cells to 100cells?

    Screen Shot 2018-02-11 at 14.21.03.png
  6. vletm

    vletm Well-Known Member

    Messages:
    3,249
    S. Das
    There are still challenges because no 'rules' ... not follow sample
    and that's much more than Your (2) ... want.

    Attached Files:

    S. Das likes this.
  7. S. Das

    S. Das Member

    Messages:
    88
    You are genius...., but I have two questions regarding your solution
    1) My final call row no. is 1752 but solution paste some value up to 1802. from where those come??
    2) NOT RELATED TO SOLUTION... How do you make first 2 rows fix when scrolling??
  8. vletm

    vletm Well-Known Member

    Messages:
    3,249
    hmm ... no answers!
    1) Maybe Your Sample File has some conflict - I don't have extra rows!
    2) Everything matters
    and there is scrolling only if conflict!
    3) "OK" was necessary ... or this!

    Attached Files:

    S. Das likes this.
  9. S. Das

    S. Das Member

    Messages:
    88
  10. vletm

    vletm Well-Known Member

    Messages:
    3,249
    S. Das
    1) It would be quicker to find ... Your problem ... if You would tell where/what kind ...!
    2) After compare with 'original file' ... I noticed that someone has made again own ... shortcut ... without figure affects!

    The Main Your Made Problem Is ...
    >> Why there are 8750 formulas?
    >> Your original sample has 'values' or blank!
    >> A cell which 'looks' empty isn't blank!
  11. S. Das

    S. Das Member

    Messages:
    88
    Can you tell me the solution of these problems...
  12. S. Das

    S. Das Member

    Messages:
    88
    Sir, can you provide a code that will delete the content of the cell if the cell content is " or any other special character??
  13. vletm

    vletm Well-Known Member

    Messages:
    3,249
    1) If You have ... problem ... You should tell where and what!
    2) Your sample file should be as close as file which You would use.
    3) If You get question, be ready to give answer.

    Solution cause 'formulas'
    1st) delete those 8750 formulas,
    that solves the main challenge.

    ... I can do many things ...
    but I should be sure 'what is that problem?'
  14. S. Das

    S. Das Member

    Messages:
    88
    Sir, if I delete those 8750 formulas, then how can I get the roll number of a particular subject for a particular room number??

    I think you already know the problem... the problem is it can't paste subject 2 after subject 1 like that and the cause is that.... there is no actual space in column B to F.
  15. vletm

    vletm Well-Known Member

    Messages:
    3,249
    S. Das
    3) If You get question, be ready to give answer.
    A question isn't an answer!

    The 'problem' ...
    You could name WHICH 'problem' do You mean! Okay!
    With Your Terms ... there are many many 'problems'!
    ... and I know why.
    I try to be patient and offer one solution for one 'problem'.
    Copy modified macro to suitable place.

    Attached Files:

    S. Das likes this.
  16. S. Das

    S. Das Member

    Messages:
    88
    Sir, the last solution is awesome. You are really a genius.

Share This Page