Solved

Filling all the non contiguous blank cells in a column with consecutive numbers

Posted on 2016-08-31
16
85 Views
Last Modified: 2016-09-01
Hi Everybody,

Its first time I am using this website. I have a specific problem I cannot find the solution to anywhere. The data I am trying to fix come from a psychology experiment.

So I have a column of data populated by numbers from 0 to 20. The column has unpredictable blank cells in between those numbers like so:
blank, blank,blank,4,blank,blank,3,blank, blank,blank,blank,5,blankx15, etc

i need to fill all the blanks starting from 0 and incrementing up in each adjacent empty cell up to the next non-empty cell, then move down to the next empty cell and do the same until the end.

This data comes from an application which measures behavioural responses. So each empty cell is a response which the participant was correct, and the cells with numbers are responses which the participant made a mistake, at which point, the total of correct responses is entered into the cell. So the non-empty cell is essentially the sum of all the empty cells above it plus itself.

Thanks a lot.
0
Comment
Question by:Dritan Nikolla
  • 8
  • 6
  • 2
16 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41779108
Can you please post an example with before and after data?
0
 

Author Comment

by:Dritan Nikolla
ID: 41779121
Before                    After
3                             3
                                 0
                                 1
                                  2
                                  3
                                  4
5                              5          
                                  0
                                  1
2                               2
0                               0
                                   0
                                   1
                                   2
3                                3

so the numbers in bold are the original numbers, the empty space in the column Before are the empty cells I want to fill :).
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41779144
Thanks.

Assuming your list is in column A and starting at row 2, you can use this formula in an adjacent column...

Cell B2 Formula -    =IF(A2<>"", A2, IF(AND(A1<>"", A2=""), 0, D1+1))

Copy the formula down as required. You can then copy that column and Paste Values over the original data in column A.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:Dritan Nikolla
ID: 41779149
Thank you so much mr Taylor. I will try this first thing in the morning. I was hoping for a vba solution though - I have some other things I need doing. But if this works, this is still great :).
0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41779209
Since you didn't mention the column where you have those numbers with blanks, one way to achieve this is select the range with numbers and blanks in them and run the following code and it will fill the blanks with the desired output.

Sub FillNumbers()
Dim rng As Range
On Error Resume Next
For Each rng In Selection.SpecialCells(xlCellTypeBlanks).Areas
   rng.Formula = "=ROWS(A$1:A1)-1"
   rng.Value = rng.Value
Next rng
End Sub

Open in new window

0
 

Author Comment

by:Dritan Nikolla
ID: 41779545
Hi mr Tiwari,

The column is colum N and the data starts from row 2. If I understand correctly, I need to put a line of code before the For Next loop to select the correct range? I know how to use the xlDown formula but I think the empty cells will block the selection to the end. In any case I am sure I can find the solution for this. Thanks so much. This can automate a very complicated data file.
0
 
LVL 29

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41779563
You have two options given below
1) Select the desired range and rung the code FillNumbersInSelectedRange
2) Assuming your numbers are in column N starting from row2, run the code FillNumbersWithoutSelectingRange

Please refer to the attached for more details.
Sub FillNumbersInSelectedRange()
Dim rng As Range
On Error Resume Next
For Each rng In Selection.SpecialCells(xlCellTypeBlanks).Areas
   rng.Formula = "=ROWS(A$1:A1)-1"
   rng.Value = rng.Value
Next rng
End Sub

Open in new window


Sub FillNumbersWithoutSelectingRange()
Dim lr As Long
Dim rng As Range
lr = Cells(Rows.Count, "N").End(xlUp).Row
Set rng = Range("N2:N" & lr)
On Error Resume Next
For Each rng In rng.SpecialCells(xlCellTypeBlanks).Areas
   rng.Formula = "=ROWS(A$1:A1)-1"
   rng.Value = rng.Value
Next rng
End Sub

Open in new window

Fill-Numbers.xlsm
1
 

Author Comment

by:Dritan Nikolla
ID: 41779807
Mr Tiwari,

Thank you so much. Your solution worked fantastic :). Thanks a lot.
0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41779812
You're welcome Dritan! Glad to help.
Thanks for the feedback.
0
 

Author Comment

by:Dritan Nikolla
ID: 41779820
Can I ask another question please? I am now copying that column into a row in a different sheet. I have the following code, see below. But it runs only the first If statement. Once the loop exits from the first if statement, I want it to go to the next if statement, but it exits. I dont' know why!

Sub testmethod()
    
     Dim count1 As Long
    Dim count2 As Long
    Dim count3 As Long
    Dim count4 As Long
    Dim count5 As Long
    Dim count6 As Long
    Dim myValue As Long
    
    count1 = 2
    count2 = 14
    count3 = 2
    count4 = 1
    count5 = 2
    count6 = 2
    
    rawData.Activate ' activating the relevant worksheet
    Range("A2").Select ' selecting the reference point
                               
                    If ActiveCell.Value = "106" Then ' these are codes, different number refers to different person
                               
                                        Do
                                                myValue = ActiveCell.Offset(0, 13).Value ' assign the value 13 cells to the right of the active cell to a long variable
                                                myData.Activate                                     ' the next sheet where I want to paste the data
                                                Cells(count5, count6).Value = myValue   ' pasting the data in the relevant cell
                                                count6 = count6 + 1                             'moving one column to the right on the same row
                                                rawData.Activate                                    'back to the worksheet to copy the data
                                                ActiveCell.Offset(1, 0).Select                    ' move one row down
                                            
                                        Loop While ActiveCell.Value = "106"                 'loop while that condition holds
                            
                                        
                                        
                    ElseIf ActiveCell.Value = "108" Then
                    
                                                count5 = count5 + 1 ' moves the counter so that in the copying to sheet i start copying one row down for the next participant.
                                        Do
                                                myValue = ActiveCell.Offset(0, 13).Value
                                                myData.Activate
                                                Cells(count5, count6).Value = myValue
                                                count6 = count6 + 1
                                                rawData.Activate
                                                ActiveCell.Offset(1, 0).Select
                                        Loop While ActiveCell.Value = "108"
                                        
                      End If
                                        
    
    
End Sub

Open in new window

0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41779894
Dritan,

Please open a New Question for your other requirement. :)
0
 

Author Comment

by:Dritan Nikolla
ID: 41779911
Ok, will do, but I don't want to lose you there :)
0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41779932
Post the link of your new question here. I will follow you up there if I am available. :)
0
 

Author Comment

by:Dritan Nikolla
ID: 41779946
0
 

Author Closing Comment

by:Dritan Nikolla
ID: 41780236
Thank you sir Tiwari, you are the best :).
0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41780242
You're welcome Dritan! Glad to help.
Thanks for the compliment. :)
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How do I get SLA using Excel Raw Data 12 32
Make a Cell act like a Date 7 38
Excel calculate based on 'x' in column 2 22
Clear Filter 8 29
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now