• 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...

  • 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

S. Das

Active Member
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.
 

Attachments

  • sample.xlsx
    70.9 KB · Views: 3
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'.
 

Attachments

  • sample.xlsb
    83.1 KB · Views: 6
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'.
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.
 
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
 
S. Das
There are still challenges because no 'rules' ... not follow sample
and that's much more than Your (2) ... want.
 

Attachments

  • sample.xlsb
    85.4 KB · Views: 6
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??
 
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!
 

Attachments

  • sample.xlsb
    92.5 KB · Views: 3
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!
 
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!

Can you tell me the solution of these problems...
 
Sir, can you provide a code that will delete the content of the cell if the cell content is " or any other special character??
 
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?'
 
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.
 
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.
 

Attachments

  • sample.xlsb
    109.3 KB · Views: 8
Hi, after searching a lot, finally I got a kind of solution to my requirement but changes are there.........

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.

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.....
 

Attachments

  • sample.xlsb
    59.3 KB · Views: 1
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
 
Syndp ...
where are data for 'subjects 6,7,8,9,10'?
... or where is data?

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.
 

Attachments

  • sample.xlsb
    60.8 KB · Views: 1
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)?
 
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)?

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:
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! <<<
 

Attachments

  • sample.xlsb
    113.9 KB · Views: 9
Back
Top