PDA

View Full Version : Solved: Formula to use for picking out the first of a common reference.



Idiot
08-09-2010, 10:31 AM
I've never been very good with using formulas for filtering, but I was curious if I can ask for help again.

What I have is a list of data that contains a reference number and dates. For example:

233 2/1/2010
233 2/2/2010
233 2/3/2010
233 4/23/2010
435 5/23/2010
435 5/24/2010
532 6/12/2010
532 6/15/2010
532 7/23/2010

etc etc

These are of course fake references numbers and dates, but the data is similar; i just have thousands about thousands of rows of this :S . The reference numbers are in column A and dates are in column B for this example.

What I need is just an if-statement that I can put in the formula bar that will take the first date of every common reference number and put it in a column of my choosing. In other words I select an empty cell and drag down to get my results that should display (using the example above):

233 2/1/2010
435 5/23/2010
532 6/15/2010

Is this relatively easy to do?

Thank you!!

mbarron
08-09-2010, 12:56 PM
Assuming the A column contains numbers and the table is sorted ascending by A then B columns.

In C2
=Min(A:A)
in C3 through as many rows as you need.
=INDEX($A:$A,MATCH(C2,$A:$A,0)+COUNTIF($A:$A,C2))

in D2 down as far as needed.
=VLOOKUP(C2,$A:$B,2,0)

If data in A is non-numeric, use advanced filtering to obtain a list of unique items then use the same VLOOKUP formula.

Idiot
08-09-2010, 04:51 PM
Assuming the A column contains numbers and the table is sorted ascending by A then B columns.

In C2
=Min(A:A)
in C3 through as many rows as you need.
=INDEX($A:$A,MATCH(C2,$A:$A,0)+COUNTIF($A:$A,C2))

in D2 down as far as needed.
=VLOOKUP(C2,$A:$B,2,0)

If data in A is non-numeric, use advanced filtering to obtain a list of unique items then use the same VLOOKUP formula.

You are awesome, THANK YOU!!