Free Microsoft Excel 2013 Quick Reference

Filling in data gaps

I am trying to fill in gaps of data with linear averages.

So far instance I have 1 in E1 and 8 in L1. I would assume F1 to have 2 in it, G1 to have 3, H1 = 4, I1 = 5, J1 = 6 and K1 = 7.

Is there a certain analysis tool or formula/function to achieve this? Sorry for the terrible explanation, I'm having a hard time putting it into words.

Thanks.


Post your answer or comment

comments powered by Disqus
I have a list of data in which I would like to fill in the gaps. Is this
possiable?

I have age in years in A2 to A100 then data in colums in 5 year gaps (eg B2
has data then B7 then B12 etc) all incremental. I would like to fill in the
gaps belween giving me data B2, B3, B4... to B100.

How can I fill in the gaps whilst still making sure my 5 year results are
left.

Simple example below
age Result
45 20
46
47
48
49
50 30
51
52
53
54
55 50

Many Thanks for any help

Can anyone show me an example of a Userform that would fill in data based on equipment list.
Choose a manufacturer - have a product list that changes depending on the manufacturer - Add the model to a list and create another list you can choose multiple accesories from. Fill in the info and then add another.
I am attaching an example 2 manufacturers w/ models to choose from.
Thanks

Hopefully someone can help - I am new-ish to VBA...

I have created a UserForm to fill in data in a worksheet. I have a command button at the bottom and when I click on it, I would like it to take the data from the UserForm to populate the currently active worksheet. So clicking on the command button should do this:

- Find the last row with data in the currently active worksheet in Column L
- Go to the row below
- Insert the data from the first textbox in the UserForm
- Go to the next column (same row)
- Insert the data from the second textbox in the UserForm
- Go to the next column (same row)
- Insert the data from the third textbox in the UserForm
- Close the UserForm

Really hope that makes sense because I couldn't upload the file

Thanks in advance,

Natalie

I am attempting to fill in data on one tab that is based on a date (Month and Year). THe data is pulled in from the web and I believe a formatting issue is preventing a vlookup from working - any ideas? The Price for 12 mo period is where I want to enter the desired start date and the actual data is on the Price by date tab. I've added notations that help to explain on the attached, you can ignore the other tabs.

Hi!

I have a problem that i'm hoping someone of you smart excelpeople would be able to crack for me.

I'm trying to put in a dropdownlist that will, when I make a selection from this dropdownlist, excel automatically fills in two other boxes with data from the table on the other sheet.

Please see my attached file with a more precise explanation of my problem.

Thanks in advance!

//Nick

Hello,

I'm using Excel '03. Thanks for your help. I have a workbook that contains
two sheets - Main and Scan.

The Main sheet stores data on all employees (30,000 of them). Column A
contains ID#s for all employees. Column B labeled Batch # and C is labeled
Batch Date but these columns are blank. The rest of the columns contain
names/addresses, etc.

The Scan sheet contains 3 columns - ID#, Batch# and Batch Date to match the
first 3 columns in the Main sheet. We are using a bar code scanner to scan
in ID numbers from employee cards for the ID#, and the Batch # and Batch Date
data is already filled in (via another macro). Since there are 25 employee
cards scanned per batch, you end up with 25 rows of ID#, Batch # and Batch
Date. The ID#s which are being scanned match an employee record somewhere on
the Main sheet.

Here's what I have to do: Add the batch# and batch date to the Main sheet
for each ID number scanned in on the Scan sheet. So, I have to use the ID
number on the scan sheet, match it to the same ID on the Main sheet and then
fill in the batch # and batch date (which the user is prompted for).

I've tried to use the macro recorder to activate the Scan sheet, copy the ID
from the cell A2, pasted it into the Find box, then find the number on the
Main sheet which works, but then I once I find the matching ID#, I need to
enter the batch number and date to the two adjacent cells.

In the end my Main sheet will show the batch # and batch date for each
employee, but the preliminary step of having the Scan sheet accept the
scanned bar codes is necessary. It would be fine to have a variable hold the
batch # and batch date if that's a better way, this info does not need to be
part of the Scan sheet which is a temporary holding place which is purged
between batches.

Thanks for any ideas.

I'm working in a spreadsheet where there is various fields for multiple sites. (see attached)

Once I run the macros to manipulate the data, new sites are left with blank entries under the percentage tab which is meant to be 100% right through.

Usually I just copy the filled in data from another site and paste it into the new sites but would like to write a macro to do this automatically as it's pretty tedious.

Any help would be much appreciated.

I want to fill in data in this grid based on a data sheet that includes raw data.

Here's the grid....

SS-20120417131538.jpg

Here's the data sheet...

SS-20120417131548.jpg

For each cell in the grid, it will need to match based on the 'week ending' field and the 'sponsor' field. And I only want to list the product once in each cell in the grid regardless of the number of times that it's listed in the data sheet - for example, for week ending 3/11/12 Arby's Product1 is listed twice in the data sheet, but I only want it listed once in the grid.

Is there a formula that I could use in each cell in the grid that would produce this result?

So I've developing an SSQ questionnaire for a piece of research I'm doing. When it's all done the participant will be able to enter in their data and in theory the choices selected using the option buttons should drop the data directly into the appropriate cell. Now theres 40 participant and each does a total of 8 SSQs, so the wait I'd like to set this up is on the first sheet "SSQ Data Entry", have the experimenter be able to enter the appropriate matching values in Session #, SSQ, and Particpant #, and then have the button option data fill in the corresponding area in either sheet 1 or sheet 2. Any idea how to enable this in a macro?

I've provided an attachment with the excel file if anyone wants to look at it directly.

Thank you.

I realize that this is a trivial question, but at the moment I'm lacking the
necessary patience to sift through help to find out how to accomplish my
goal. So here goes: I am trying to set up a quote sheet in Excel in which I
enter a product ID# in one column, and the description, list price, and
invoice price all fill in based on a table or database that I build with the
necessary info.

I know I've done something similar to this before, but now I'm at a loss to
recall how I managed it.

Thanks.

Hi,

I'm trying to get Excel to save me some time filling in years between a start year and an end year.

Basically I have a column of start years, and a column of end years, and I need a column of all the years in-between seperated by pipes.
So, we should have something like:
Cell 1: 1975
Cell 2: 1980
Cell 3: 1975|1976|1977|1978|1979|1980

I have about 700 of these so any help would be much appreciated!

I've attached a document for your refernce.

I have a very long list of product numbers in column A and would like to fill in the gaps in the rows like I have in Column C.

Thanks

I have a spreadsheet with over 6,000 records. it is setup as follows:

A1:E1 are the column headings
A1= CustomerID; B1= Customer; C1=Item; D1= Item Description; E1= Price

Rows 1-5 have Customer ID & Customer informaion
Rows 6-15 is the order information for Customer in Row 5 but the column information for column A6:B15 are blank

I want to automatically fill in the values in row A6:B15 with the first non-blank information from above. In this case it would be A5:B5.

This needs to be done by finding the blank cells since there is no pattern to the information and there are too many blanks to go through and manually edit the information and some blanks are only one row and others are multiple rows.

Thanks for the help!
Steve M.

Okay, deep breath, first post...

I have imported loads of data into Excel, each data set on a different worksheet. I want to create a master sheet, with all the data in rows.

The problem I have is that the data, while sorted, has loads of gaps.
The data has a number then a value. The numbers are all in order but some datasets start at 20 some at 37, etc, etc. and has loads of holes in it. i.e. the data will go 20,21,34,35,36,38,40,etc.
What I am looking to do is have the data on the master sheet with the data name in col A then 1 to 300 in cols B onwards. So if a particular data set has a value at 27 it will place that in the column headed 27.

I guess it would be easiest to sort each dataset on its own sheet to fill in all the gaps then just copy and paste, or something, into the Master Sheet.

Does that make sense?
Hang on
Sorry, had to get it out of my system.

Hi all,

First post here so I will keep it brief. I need to fill in the gaps in my data by interpolation, I have 170 bond yields on the spreadsheet so a macro (maybe on the active cell) is preferable. Please see attached for the data, scrolling down shows the missing data.
bond yield example data.xlsx
Thanks

Hi,

Very basic but for people who cannot code its **** hard.

list of data up to 20,000 rows may be more or less with three columns of data only column c if entirly filled in. Randomly in column a and b you will get a name showing there is never just a first name or surname these always match.

example
a1 first name
b1 surname
c1 data(scores)

but this is where it gets interesting ----

how do i fill all the gaps where there are no names against scores so i have a complete a column and a complete b column last time i did this my fingers fell off. There must be a easy macro loop

Many thanks

Sorry, the title probably sounds a little confusing, but hopefully my
problem is a quick fix.

I have some charts, and in them about 6 different series. Along the x
axis, is a bunch of different values, and each series has some values
corresponding to some of the x axis values, but not all.

So basically right now, some series have gaps, and rather than connect
the gaps with a line, Excel seems to just leave a gap in the series,
and continue again at the next data point.

Hopefully you understand what I'm saying. Is there an easy way to just
fill in these series gaps with a straight line connect the two closest
data points to make the series continuous?

Thanks in advance for any help.

--
Boon8888
------------------------------------------------------------------------
Boon8888's Profile: http://www.excelforum.com/member.php...o&userid=30647
View this thread: http://www.excelforum.com/showthread...hreadid=526803

Sorry, the title probably sounds a little confusing, but hopefully my problem is a quick fix.

I have some charts, and in them about 6 different series. Along the x axis, is a bunch of different values, and each series has some values corresponding to some of the x axis values, but not all.

So basically right now, some series have gaps, and rather than connect the gaps with a line, Excel seems to just leave a gap in the series, and continue again at the next data point.

Hopefully you understand what I'm saying. Is there an easy way to just fill in these series gaps with a straight line connect the two closest data points to make the series continuous?

Thanks in advance for any help.

hiya, hope this hasn't been asked before, i couldn't seem to find anything like it!!!

i've got a load of measured data in 3 columns (initially polar and z co-ordinates, then converted to x,y, and z).

i'm trying to put the data into a matrix which is a data set of the z values, linearly mapped in x by columns and linearly mapped in y by rows.

so far i've put in the data i have by rounding my x and y values to the nearest integer, then using a nesting of the INDEX and MATCH commands to put the corresponding z value into the matrix on a second excel sheet.

however, this has left a lot of blank spaces (displaying #N/A) because of the lack of data. does excel have any functions to fill in these spaces with estimates??? (the expected overall shape of the data is cone shaped, but it could well contain multiple peaks).

any help would be much appreciated as this is doing my head in!!!

cheers,

tom
----

i Have a simple scenario where I'm tracking the days an athlete is injured with a 1. The day they return from injury I will mark as a 2. Athletes being lazy may mark the 1st day (in example C15 with 1 but then only every second until they mark 2(day returning to training) in C20) How do I get column D to place a 1 in every cell from D15 to D19 to mark the whole injury period?
Here is a sample of the spreadsheet. I have manually put what I want to appear in column D but if you can provide a formula that would be sensational.Training filling in gaps.xls

I have a sheet of over 40000 rows, I attach a sample. Column a is called dam and column b is called damsire. Each dam has only one 1 damsire. Both column a and column b are sorted ascending. Unfortunately there are big gaps in column b. some of these can be filled in as we have the information e.g. b38 and b39 should be Manila..as the row 37 tells you the correct damsire. Similiarly b49 could be filled in as shernazar. I want to create a new column which contains a formula to fill in these blanks. of course some of the blanks cant be filled in as the information is not there e.g. b23 to b28.

I am using a Pivot Table to sort, combine, and sum data. After this is complete I want to copy the pivot table and paste it (values only) to another worksheet and use the data again.

The problem is the pivot table does not fill in all the blanks and leaves empty rows.

The there a way to force the pivot table to fill in the labels?

Thanks in advance for any help !!

Steve

I am at wits end. I have tried several different VBA codes to no avail.

I have data in the following format:

10:00:00 data data data
10:00:01 data data data
10:00:02 data data data
10:00:04 data data data

I am missing every 3rd to 4th row of data for 5500 lines. Is there a way to find the missing seconds and insert a row and just copy the data above it to fill in?

Thank you in advance,

-two shoes

Suppose I have a column of data with various values and a bunch of blank spaces. Essentially I want to leave the values as is but fill in the blank cells with a number. I’ve written a loop to do this in VBA, which grabs the value in the cell above, but it’s somewhat slow. Is there a more efficient way to do this?

Sub Downfill(Max)
'
'This count variable is used to run the loop
Dim i As Long

i = 1
Do While i


No luck finding an answer? You could always try Google.