Excel: finding first and last unique record

February 4, 2008 12:10 PM

Finding and copying the first and last value, to the left of a range in excel.

First of all here is a screenshot of an example.

What I want to do is for each unique value in column B, copy the first and last value in column A, and put them in a single cell on column C.

So for the screen shot above, the following would be outputed:

C
------------------------
1 |2006Q3 - 2007Q4|
2 |2006Q4 - 2007Q4|
3 |2006Q4 - 2007Q4|

Is there any sort of macro that would do this?

posted by corpse to computers & internet (6 comments total)

In column C, try the following formula:

=IF(B1<>B2,OFFSET(A1,-COUNTIF(B:B,B1)+1,0) & " - " & A1,"")

Upon the last instance of an entry in the group of items, it will offset back to the first entry in that group, and concatenate the values from column A for the first and last entries...
posted by Chunder at 12:37 PM on February 4

That's good, except I want all of the entries of the C column to be together, like on rows 1, 2, and 3.
posted by corpse at 12:41 PM on February 4

Building off Chunder's answer, how about this:
=IF(B1<>B2,OFFSET(A1,-COUNTIF(B:B,B1)+1,0) & " - " & A1,C2)
posted by inigo2 at 12:49 PM on February 4

(Paste that into C1, and drag it down/copy paste it to the rest of the cells.)
posted by inigo2 at 12:50 PM on February 4

Sorry - got carried away :)

The only way that I can think of doing this without getting too overcomplicated is to use an autofilter - select the records where column C is not blank (i.e. use the sow nonblanks option in the autofilter dropdown list)... and then copy and paste the result. Not ideal, but you could get into the realm of having a big complicated macro to copy and paste data all over the place.
posted by Chunder at 12:56 PM on February 4

Ohhh, ok, sorry, I misunderstood your clarification...Let me think about it some more.
posted by inigo2 at 1:26 PM on February 4