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 Excel Ninja

    Messages:
    4,131
    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 Excel Ninja

    Messages:
    4,131
    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 Excel Ninja

    Messages:
    4,131
    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 Excel Ninja

    Messages:
    4,131
    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 Excel Ninja

    Messages:
    4,131
    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 Excel Ninja

    Messages:
    4,131
    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 Excel Ninja

    Messages:
    4,131
    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:

    Thomas Kuriakose and S. Das like this.
  16. S. Das

    S. Das Member

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

    Syndp New Member

    Messages:
    8
    Hi, after searching a lot, finally I got a kind of solution to my requirement but changes are there.........

    hi, @veltm I required something like your solution but with some changes.

    In my case, 10 subjects will be present. The sequence of output will be Subject1->Subject2->Subject5->Subject6 for one column and
    Subject3->Subject4->Subject7->Subject8 for other column. Which column will stop early, Subject9->Subject10 will copy to that column.

    Here is my sample file (@S. Das, I have just modified your sample file as per my need).


    Thanks in advance.....

    Attached Files:

  18. vletm

    vletm Excel Ninja

    Messages:
    4,131
    Syndp ...
    where are data for 'subjects 6,7,8,9,10'?
    ... or where is data?
  19. vletm

    vletm Excel Ninja

    Messages:
    4,131
    S. Das
    ... I noticed that ...
    if any of those Your Subject 1...5 's data is empty,
    then there would be a challenge.
    You can avoid that by modification ...
    add next TWO green lines as below to original code
    If yy-1>0
    .Range(.Cells(y,x), .Cells(y+yy-1,x)).Copy
    .Cells(y1,xx).PasteSpecial ... and so on
    End If
    Next x
    y=y+50
  20. Syndp

    Syndp New Member

    Messages:
    8
    Apologies.....forget to write the data...

    @veltm one thing, this is not mandatory subject 1 to subject 10 always contain data.... some subject may be blank i.e, a room can contain a single subject to 10 different subjects but total no of student per room will maximum 50.

    Attached Files:

  21. vletm

    vletm Excel Ninja

    Messages:
    4,131
    Syndp
    My question was: where is data?
    How do You understand word 'Where'?
    For me ... it's a 'place'.
    or
    do You write one-by-one or even manually copy
    those
    ten Subject-column values (max 17500 cells)?
  22. Syndp

    Syndp New Member

    Messages:
    8
    Data will come from another sheet named "Sheet5' (actually Sheet19)...
    Subject 1 data will come from column K
    Subject 2
    data will come from column V.
    Subject 3 data will come from column AG.
    Subject 4 data will come from column AR
    Subject 5 data will come from column BC
    Subject 6 data will come from column BN
    Subject 7 data will come from column BY
    Subject 8 data will come from column CJ
    Subject 9 data will come from column CU
    Subject 10 data will come from column DF and range is 3 to 52 for room 1, 53 to 102, for room 2 and so on for all those 10 subjects.

    As the data is so much confidential, so I also don't have more information about data

    I think this will helpful for you.
    Last edited: Feb 19, 2018
  23. vletm

    vletm Excel Ninja

    Messages:
    4,131
    Syndp
    Hmm ...
    It seems that this version's data has different pattern than original version.
    Of course, it's possible despite 'the real files are basic same' ... okay?
    Is that data something 'much c...' ... anyway there are a lot of formulas.
    > Your sample has more columns ... takes longer time ...
    this would be more visual ... not only waiting with 'like frozen screen'.
    >>> This version don't match/work with Your Co-Workers version! <<<

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page