P

#### Pirate

the first sheet in a row.

Example:

Sheet 1

A1 B1 C1

D1

20003399 Result(sheet2 B1) Result(sheet2 B2) Result(sheet2 B3)

Sheet 2

A1 B1

20003399 Dog

A2

20003399 Cat

A3

20003399 Horse

Hope this makes sense.

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

P

the first sheet in a row.

Example:

Sheet 1

A1 B1 C1

D1

20003399 Result(sheet2 B1) Result(sheet2 B2) Result(sheet2 B3)

Sheet 2

A1 B1

20003399 Dog

A2

20003399 Cat

A3

20003399 Horse

Hope this makes sense.

Ad

D

B1:

=COUNTIF(Sheet2!A1:A100,A1)

C1, confirmed with CONTROL+SHIFT+ENTER, and copied across:

=IF(COLUMNS($C1:C1)<=$B1,INDEX(Sheet2!$B$1:$B$100,SMALL(IF(Sheet2!$A$1:$A

$100=$A1,ROW(Sheet2!$A$1:$A$100)-ROW(Sheet2!$A$1)+1),COLUMNS($C1:C1))),""

)

Adjust the ranges, accordingly.

T

it slightly more efficient by putting the INDEX offset outside of the SMALL

function:

SMALL(...,COLUMNS($C1:C1))-ROW(Sheet2!$A$1)+1

This way you're only calculationg a single offset instead of an array of

offsets.

D

T. Valko said:

it slightly more efficient by putting the INDEX offset outside of the SMALL

function:

SMALL(...,COLUMNS($C1:C1))-ROW(Sheet2!$A$1)+1

This way you're only calculationg a single offset instead of an array of

offsets.

Unless I misunderstood, I don't think it will return the desired

results...

T

A5 = A

A6 = B

A7 = X

A8 = C

=SMALL(IF(A5:A8="x",ROW(A5:A8)-MIN(ROW(A5:A8))+1),ROWS(A$1:A1))

Result = 3

=SMALL(IF(A5:A8="x",ROW(A5:A8)),ROWS(A$1:A1))-MIN(ROW(A5:A8))+1

Result = 3

Those do the exact same thing, just differently.

In the first example you're calculating an *array* of offsets to match the

positions of the indexed range. In the second example you're calculating a

single offset from the result of the SMALL function.

Sample file:

xSample.xls 21kb

http://cjoint.com/?fiiwCVMXaC

D

T. Valko said:

A5 = A

A6 = B

A7 = X

A8 = C

=SMALL(IF(A5:A8="x",ROW(A5:A8)-MIN(ROW(A5:A8))+1),ROWS(A$1:A1))

Result = 3

=SMALL(IF(A5:A8="x",ROW(A5:A8)),ROWS(A$1:A1))-MIN(ROW(A5:A8))+1

Result = 3

Those do the exact same thing, just differently.

In the first example you're calculating an *array* of offsets to match the

positions of the indexed range. In the second example you're calculating a

single offset from the result of the SMALL function.

Sample file:

xSample.xls 21kb

http://cjoint.com/?fiiwCVMXaC

I haven't tested it... However, I suspect any difference in efficiency

is likely negligible.

Ad

T

On a small range, probably, but with these types of formulas evey little bit

helps.

D

be better to use a different approach.

By the way, it would probably be a bit more efficient to enter COUNTIF

in a separate cell and then have the formula reference that cell.

Otherwise the calculation will take place numerous times instead of just

once.

I suspect any difference in efficiency is likely negligible.

On a small range, probably, but with these types of formulas evey little bit

helps.

--

Biff

Microsoft Excel MVP

[/QUOTE]Domenic said:I haven't tested it... However, I suspect any difference in efficiency

is likely negligible.

T

Average of 5 calculation times.

=SMALL(IF(B$1:B$500="x",ROW(B$1:B$500)-MIN(ROW(B$1:B$500))+1),ROWS(D$11))

Average calc time: 0.00084

=SMALL(IF(B$1:B$500="x",ROW(B$1:B$500)),ROWS(D$11))-MIN(ROW(B$1:B$500))+1

Average calc time: 0.00066

By the way, it would probably be a bit more efficient

to enter COUNTIF in a separate cell and then have

the formula reference that cell. Otherwise the calculation

will take place numerous times instead of just once.

I suspect any difference in efficiency is likely negligible. <grin>

So, we're even!

--

Biff

Microsoft Excel MVP

[/QUOTE]Domenic said:If the range is large, and the expected results are numerous, it might

be better to use a different approach.

By the way, it would probably be a bit more efficient to enter COUNTIF

in a separate cell and then have the formula reference that cell.

Otherwise the calculation will take place numerous times instead of just

once.

On a small range, probably, but with these types of formulas evey little

bit

helps.

D

T. Valko said:500 rows of data...

Average of 5 calculation times.

=SMALL(IF(B$1:B$500="x",ROW(B$1:B$500)-MIN(ROW(B$1:B$500))+1),ROWS(D$11))

Average calc time: 0.00084

=SMALL(IF(B$1:B$500="x",ROW(B$1:B$500)),ROWS(D$11))-MIN(ROW(B$1:B$500))+1

Average calc time: 0.00066

So this basically proves my point. Here we have a relatively small

range with the difference in speed being somewhat insignificant.

I tried to do some informal testing under different circumstances. I

entered data from Row 5 through to Row 65000, ensuring that the criteria

is met at least 1000 times. Then I adjusted the ranges for both

formulas, entered the formula in a cell on the second row, and copied it

down to Row 1000.

The first formula took about 44 seconds to calculate. The second

formula took about 34 seconds. So, yes, the second formula is more

efficient but at this point is 34 seconds really acceptable?

I suspect any difference in efficiency is likely negligible. <grin>

So, we're even!

Are you going to keep score or shall I?

T

We both can. And then we'll compare the formulas we used to see which is

more efficient only to be admonished by H. G. for being so inefficient!

You can't win for losing in this business!

Ad

T

It depends on the application.

I have files that take 10's of minutes to calculate. They're doing really

complex stuff, calculating linear regressions for calibrating xray

spectrometers.

Ad

D

Are you going to keep score or shall I?

We both can. And then we'll compare the formulas we used to see which is

more efficient only to be admonished by H. G. for being so inefficient!

You can't win for losing in this business![/QUOTE]

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.