find value corresponding to first across multiple groups

**surferq** · 02-13-2013, 04:02 PM

Hi,

please see my data file below: I am wanting to insert the baseline value (i.e. the first value in a group) for every entry of that group. E.g. AA would be 4.38, AE 3.61, AF 2.9, AG 4.27.

I've tried pivot tables, and can calculate the max/min or average values within a group, but not the first.

Any help most appreciated.

THanks

AA
AA 4.38
AA
AE
AE
AE 3.61
AE
AE 4.7
AE
AF
AF 2.9
AF
AF 2.69
AF
AF 2.53
AF
AF
AF
AG 4.27
AG
AG 5.85
AG
AG 2.04

**Moo the Dog** · 02-13-2013, 04:12 PM

Try these array formulas (Applied with Ctrl + Shift + Enter, instead of just Enter):

Assuming AA, AE, AF, Ag are in column A, and values are in column B...

To find AA: =INDEX($B$1:$B$23,MATCH(1,($A$1:$A$23="AA")*($B$1:$B$23>0),0))
To find AE: =INDEX($B$1:$B$23,MATCH(1,($A$1:$A$23="AE")*($B$1:$B$23>0),0))
To find AF: =INDEX($B$1:$B$23,MATCH(1,($A$1:$A$23="AF")*($B$1:$B$23>0),0))
To find AG: =INDEX($B$1:$B$23,MATCH(1,($A$1:$A$23="AG")*($B$1:$B$23>0),0))

- Moo

**surferq** · 02-13-2013, 04:21 PM

Thanks Moo,

I have several dozen groups (AA-MF). Is there some way to do this where I don't have to explicitly state the group IDs?

**Moo the Dog** · 02-13-2013, 04:30 PM

Sure, make a list of the ID's in a column (vertically), for instance, if the list is in cell F1 and down, use this formula in G1 then fill down:

=INDEX($B$1:$B$300,MATCH(1,($A$1:$A$300=F1)*($B$1:$B$300>0),0))

Remember to apply with Ctrl + Shift + Enter.

Be sure to change the 300 to however many rows of data are in your original data set.

Then fill that formula down to the end of your list.

- Moo