Excel: finding first and last unique record
February 4, 2008 12:10 PM   Subscribe

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 answers 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, 2008


Response by poster: 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, 2008


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, 2008


(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, 2008


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, 2008


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


« Older help my diary become dear to me   |   What are the best fullsized headphones for an ipod... Newer »
This thread is closed to new comments.