Pivot Table to condense large data set of Coordinate + Depths

Hello All,

I have a list of almost 600000 data points in 5 separate csv files. This data have three columns: Latitude, Longitude, and Depth.

For many coordinates, the depths vary slightly but have about 10-30 entries for a single coordinate pair. I wish to average the depth values of each cell that belongs to an identical coordinate pair automatically with the purpose of condensing my large dataset.

For example, the pairs (42.507636, -74.978933) is listed in cells A10 and B10 through A20 and B20, but the depth values in cells C10 through C20 are all different. It is these C cells that I wish to average together so the set can be condensed to a single A10, B10, C10 entry.

With about 70,000 total latitudes, longitudes, and depths, I wish to automate this process. A pivot table seems like it would help, but I have been unable to accomplish my task.

Hi,

Welcome to the Forum.

As mentioned by you, this can be accomplished with the help of a Pivot Table.

Can you post a small portion of the data with enough examples but without any sensitive info in excel format please?


See the following URL for help on how to upload a file.
http://www.excelforum.com/members/da...ch-a-file.html

I think I have attached a sample dataset, though am having some trouble with the upload tool. Hopefully it makes it out!

This is a .csv file with about 3 unique longitudes and about 6 unique latitudes. There are 110 rows total, each with it's own value for depth.

I wish to create a 3 column table that condenses the repeating coordinate pairs into a single unique pair entry with a value for depth that is the average of depth values from the repeating pairs.

Hi,

Something like this?

See the attached file.

Yes! This looks like what I would like to do.

Is there a way to automatically populate the "longitude" cells so each row has a entry for longitude? I will be exporting this table to ArcGIS and cells cannot be blank.

Do you mean to repeat all label items? If so, this can be found on the Design tab, under Report Layout >>>Repeat All Item Labels