You are here:

Excel/MS Excel 2010 Keeping only first and last entry in columns

Question
Dear Tom,

I have this table, which shows a series of well borings (column A),the names of soils encountered by the boring (col. B), the depth of top of the soil (col.C) and the soil bottom depth (col. D).

A   B        C   D
1   soilA   60   75
1   soilA   75   80
1   soilA   80   103
1   soilA   103   120
--------------------------
1   soilB    73   80
1   soilB    80   96
1   soilB    96   100
---------------------
2   soilM   60   75
2   soilM   75   80
2   soilM   80   103
---------------------
2   soilX   21   40
2   soilX    40   53
2   soilX    53   64
2   soilX    64   88
-------------------------
...etc., goes on for some 17,000 rows for 360 borings and countless soil horizons.

I would like to eliminate all the splits in column B and, for each boring number in col A and soil horizon in col. B, show only the first entry in col. C (the very top of the soil) and last entry in col. D (the very bottom). For example, the finished table above would look like this:

A   B        C   D
1   soilA   60   120
1   soilB    73   100
2   soilM   60   103
2   soilX   21   88

I'd be very grateful for any help in making this operation less tedious.

Ron

Soil Boring
Ron,

I was able to reproduce your results (from the sample data shown) by creating a pivot table.

I put column A and B as row fields and columns C and D as column Fields.  I defined the aggregation for column C as MIN and for column D as MAX.

this produced the results you show.  I have attached a picture.

If you are not familiar with Pivot Tables I can provide more information.

If you want to send a sample file I can demonstrate.  You can send it to twogilvy@msn.com

I have attached a picture of what I did to illustrate.

--
Regards,
Tom Ogilvy

Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Tom, I think I've got it. Thanks so much for your lightning-fast response!

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.