Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and the Yahoo Answers website is now in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

Trending News

Promoted
Icon
Lv 7
Icon asked in Computers & InternetProgramming & Design · 8 years ago

Excel returning row number of set of data?

I am trying ti figure out the best way to use functions for the following situation.

I have a list of values (sorted) for example lets say:

Dog

Dog

Dog

Dog

Elephant

Elephant

Elephant

Zebra

Zebra

Zebra

Zebra

Zebra

Zebra

Now what I want to do is have a cell which tells me what row number starts and ends the list of each animal.

The end result would be

Dog 1 4

Elephant 5 8

Zebra 9 14

Now I would like to get the minimum row reference and the maximum row reference, but at the very least I could do the row reference for the first instance of said word and then use count of instances to figure the max.

I am looking though the functions now trying to figure out the best way to do this. Any help would be appreciated.

Update:

Oh and this is all done dynamically. I don't know the list of animals but I need to accommodation for any and all. I can easily hard code things, but i am trying to avoid it.

Update 2:

For Min =(MATCH(A1,Sheet1!A:A) - COUNTIF(Sheet1!A:A,A1)) +1

For Max =(MATCH(A1,Sheet1!A:A)

3 Answers

Relevance
  • mracquire
    Lv 5
    8 years ago
    Favorite Answer

    Let's say your list of animals is in column A and your unique list of animals is in column C. Enter the formulas as shown below.

    In D1: =MATCH(C1,$A:$A,0)

    In E1: =MATCH(C1,$A:$A,1)

    Then copy down through all rows of your unique list of animals.

    The result should look something like this.

    . . . . .A . . . . . . . . B . . . . . . C . . . . . . D . . .E

    .1 . . Dog . . . . . . . . . . . . .Dog . . . . . . .1 . . . 4

    .2 . . Dog . . . . . . . . . . . . .Elephant . . . 5 . . . 7

    .3 . . Dog . . . . . . . . . . . . .Zebra . . . . . .8 . . .13

    .4 . . Dog

    .5 . . Elephant

    .6 . . Elephant

    .7 . . Elephant

    .8 . . Zebra

    .9 . . Zebra

    10 . .Zebra

    11 . .Zebra

    12 . .Zebra

    13 . .Zebra

    This works only if column A is sorted.

  • mouzon
    Lv 4
    5 years ago

    Go to instruments/choices/edit/move choice after enter/ decide upon proper decide on column A and Column B Enter your information, it's going to robotically to the correct cell successively eg a1, b1, a2, b2

  • voyager
    Lv 6
    8 years ago

    Try this formula

    =MIN(IF(($A$1:$A$14=C1), ROW(INDIRECT("1:"&ROWS($A$1: $A$14)))))&" "&MAX(IF(($A$1:$A$14=C1), ROW(INDIRECT("1:"&ROWS($A$1: $A$14)))))

    where A1:A14 contain your list of values and C1 contains the lookup value (eg: Dog)

    Confirm with Ctrl+Shift+Enter instead of just Enter

Still have questions? Get your answers by asking now.