1

I have a table with 1000+ rows. One column is the Item Name. Each Item Name is unique, but will appear in 8 to 15 rows contiguous of data (before going to the next item name), the number of rows in each dataset can vary.

I need to apply a formula (LINEST, etc) to that only references each Item's set of data. So I need some formula help to make a cell reference that starts at the beginning and ends at the end of the Item's set of data.

I think the solution will be like the helper column created in the post Colour coding blocks of rows using conditional formatting

Item Name   Data 1  Data 2
10L-101-1   20.0    1.75
10L-101-1   17.5    1.49
10L-101-1   15.0    1.24
10L-101-1   12.5    0.92
10L-101-1   10.0    0.63
10L-101-1   7.5     0.41
10L-101-1   5.0     0.24
10L-101-1   2.5     0.11
10L-101-1   0.0     0.00
10L-102-1   20.0    0.35
10L-102-1   17.5    0.30
10L-102-1   15.0    0.25
10L-102-1   12.5    0.23
10L-102-1   10.0    0.11
10L-102-1   7.5     0.07
10L-102-1   5.0     0.04
10L-102-1   2.5     0.01
10L-103-1   0.0     0.00
10L-103-1   20.0    1.75
10L-103-1   17.5    1.50
10L-103-1   15.0    1.27
10L-103-1   12.5    0.92
10L-103-1   10.0    0.62
10L-103-1   7.5     0.40
10L-103-1   5.0     0.23
10L-103-1   2.5     0.08
10L-103-1   0.0     0.00
1
  • Not sure if you need this on a one-time basis, ongoing basis, or if the part numbers are always the same. If one-time or the part numbers are always the same, you could do an advanced filter > unique values and copy this to another column, then do SUMIF in the adjacent columns. – BillDOe Feb 26 '15 at 1:32
0

You could use INDEX or OFFSET for this - INDEX is usually preferable because it's not a "volatile" function like OFFSET.

For a specific item name in E2 use this formula to get the column B range of data for that item number

=INDEX(B:B,MATCH(E2,A:A,0)):INDEX(B:B,MATCH(E2,A:A,0)+COUNTIF(A:A,E2)-1))

If you want column C obviously change both B:B refs to C:C and for both columns together change the second B:B only to C:C

The OFFSET version:

=OFFSET(B1,MATCH(E2,A:A,0)-1,0,COUNTIF(A:A,E2))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.