large data set

Hi, I am trying to find the distance between zip codes in separate columns in a work book. To do that I need to convert a full column of text zip codes into the geography data type. The problem is that my dataset is large, 300k+ rows, and Excel cannot handle converting the entire column at...

Hi, Please can I get advice on whether it is possible to use VBA to create a summary table from a 2 column dataset as follows: The input data is a simple list of integer 'values' between 1 and 15. The output data should count the number of zero values in a row or non-zero values in a row, give...

Hello there! I have an existing code which creates a summary table from a dataset by counting the number of repeated consecutive values. If there is a new value in the dataset then a new row is created for it in the summary table. Please can you advise if it’s possible to edit this macro (or...

Hi, Please can I get advice on whether it is possible to use VBA to create a summary table from a 2 column dataset as follows: The input data is a simple list of integer 'values' between 1 and 15. The output data should count the number of identical values in a row, give the start and end...

Dear forum, I'm looking for a clean and light way to transform the way my data is stated. I succeeded in writing a code to stack several columns on top of another, running from the first row downwards to the first blank cell and jumping to the next column. As is shown in the table below, the...

I have been trying to figure this out for a few days and haven't been able to find anything online with this exact situation. I have a large set of data (~5000) with names of individuals, dates of completion, and scores assigned. I have figured out without too much difficulty how to average all...

Hello, I have a spreadsheet set up to take a cleansed report which I need to apply formula to. The report size could change (i.e. the number of rows used will not be fixed, but the columns will). The report is pasted into cell E1 and covers columns E to F. In columns A, B, C and D - I would...

Hello, I was wondering if I could get some help please. I am attempting to reformat a large set of data to load into qlikview. At the moment there are multiple columns with values on one line separated by a comma. I would like to find a way to separate the data for all of the columns. I have...

Hi! Please help me with my thesis research. I have downloaded huge data set to analyse. I heard it can be managed via Power pivot tool. But I do not have intensive knowledge on this. For the first step, I want to transform the data. I want dates to be in the rows, and company names to be in...

I am using VLOOKUP in a large data set, but in a simple VLOOKUP formula. =vlookup(L:L,U:V,2,false) L is a column of concatenated part numbers and units of measure: 100009EA, 100009BX, 100009CA for example U is the same as L and V is a number My results are like this: 100009EA 1...

I have a large inventory database (almost 8,000 rows by 38 columns) generated by querying many different spreadsheets. The queries are merged into one giant table that I would like to search from. Each row contains many columns of information about the piece of equipment that someone might...

I'm trying to reduce an Excel 2010 file in size by clearing the contents of cells where a formula has returned a zero value. I have found this VBA script which does the job but not very well for the data set I'm working with: Sub delzero() For Each Cell In [W12:BF24459] If Cell.Value = "0" Then...

Hi Everyone, I have a large set of data in my excel spreadsheet with 9800 rows. I want use RANKIF function by comparing two columns. when I am trying to solve the problem using the below function =SUMPRODUCT(--(H2=$H$2:$H$9692),--(AZ2<$AZ$2:$AZ$9692))+1 it shows me a value error. I think due...

Hi I have multiple datasets each of which has close to 1200 Worksheets. In each of these worksheets there is data spanning from 1000 rows to 5000 Rows.(Mostly at the lower end of the spectrum) The problem is quite simple. I have obtained this data from an online database and since it is based on...

Hi, I have 190,000 rows of data and I want to run one formula down to the bottom and run a pivot table off the frequency of the outcomes. The formula below is put into column H. IFERROR(IF(B31=$I$1,MAX(D2:D31),"")-IF(B31=$I$1,MIN(E2:E31),""),"") Column B is Time formatted to text. Column D...