Automatically extending a column data without adding zeros

%3CLINGO-SUB%20id%3D%22lingo-sub-2607953%22%20slang%3D%22en-US%22%3EAutomatically%20extending%20a%20column%20data%20without%20adding%20zeros%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2607953%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%20a%20workbook%20with%20two%20sheets.%26nbsp%3B%20My%20first%20sheet%20contains%20raw%20reporting%20data.%26nbsp%3B%20The%20second%20sheet%20is%20used%20to%20automatically%20filter%20the%20columns%20I%20care%20about.%26nbsp%3B%20On%20a%20daily%20basis%2C%20I%20will%20copy%20and%20paste%20new%20raw%20data%20into%20sheet%201%2C%20with%20sheet%202%20automatically%20filtering%20the%20raw%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20created%20the%20following%20simple%20function%20in%20the%20second%20sheet%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D('Raw%20Data'!C1%3AC100)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20it%20properly%20imports%20everything%20from%20C1%20and%20below%20until%20I%20get%20to%20no%20content%20and%20it%20automatically%20adds%20zeros%20at%20the%20end.%26nbsp%3B%20For%20example%2C%20if%20I%20only%20had%2045%20column%20data%20cells%20in%20column%20C%20then%2046%20to%20100%20will%20populate%20with%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20write%20this%20function%3CSPAN%3E%26nbsp%3Bto%20say%20populate%20C1%20to%20whenever%20the%20data%20stops%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2607953%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2607992%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20extending%20a%20column%20data%20without%20adding%20zeros%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2607992%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1119874%22%20target%3D%22_blank%22%3E%40jacobpeper%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20something%20similar%20with%20this%20function%20bringing%20in%20data%20from%20a%20separate%20workbook.%20The%20%3CSTRONG%3EFILTER%3C%2FSTRONG%3E%20function%20does%20require%20the%20most%20recent%20version%20of%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DFILTER(%5BSource.xls%5DWorksheet1!%24A%245%3A%24T%24100%2C%5BSource.xls%5DWorksheet1!%24A%245%3A%24A%24100%26lt%3B%26gt%3B%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20pulls%20in%20from%20Source.xls%20all%20rows%20and%20columns%20(up%20to%20column%20T)%20that%20have%20something%20in%20column%20A%2C%20stops%20when%20it%20reaches%20blank%20cells.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20good%20video%20that%20explains%20the%20FILTER%20function%20if%20you're%20not%20familiar%20with%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have created a workbook with two sheets.  My first sheet contains raw reporting data.  The second sheet is used to automatically filter the columns I care about.  On a daily basis, I will copy and paste new raw data into sheet 1, with sheet 2 automatically filtering the raw data. 

 

I created the following simple function in the second sheet:

 

=('Raw Data'!C1:C100)

 

So it properly imports everything from C1 and below until I get to no content and it automatically adds zeros at the end.  For example, if I only had 45 column data cells in column C then 46 to 100 will populate with 0.

 

How do I write this function to say populate C1 to whenever the data stops?

1 Reply

@jacobpeper 

 

I do something similar with this function bringing in data from a separate workbook. The FILTER function does require the most recent version of Excel.

 

=FILTER([Source.xls]Worksheet1!$A$5:$T$100,[Source.xls]Worksheet1!$A$5:$A$100<>"")

 

It pulls in from Source.xls all rows and columns (up to column T) that have something in column A, stops when it reaches blank cells. 

 

Here's a good video that explains the FILTER function if you're not familiar with it. 

https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...