Creating a list

[TheBakerBoy]

I have a formula that works well in Excel 2010 (which I am using) but doesn't in 2003 and it does need to be compatible with the older version. The formula is this:=IF(ISERROR(INDEX(I$2:I$119,AGGREGATE(15,6,ROW(I$2:I$119)-ROW(I$2)+1/((I$2:I$119<>"")),ROWS(H$2:H2)))),"",(INDEX(I$2:I$119,AGGREGATE(15,6,ROW(I$2:I$119)-ROW(I$2)+1/((I$2:I$119<>"")),ROWS(H$2:H2))))). When I run the workbook on the 2003 version it looks like it doesn't recognize the AGGREGATE function.

What I want to do is it take the data in column I and remove all the spaces as this will be come part of a data validation list and it is very hard to use if there are a number of spaces in between. Any help that you can give would be much appreciated.

[Tony Valko]

What type of data is it? Is it text? Numeric? Could be both? Something else?

[FDibbins]

You could probably modify this to give you what you need...
=IF(ISERROR(INDEX($I2$2:$I$119,MATCH(0,INDEX(COUNTIF($I$2:I2,$I$2:$I$119),),0)),"")

[Tony Valko]

Here's what I think they need:

=INDEX(I:I,SMALL(IF(I$2:I$119<>"",ROW(I$2:I$119)),ROWS(H$2:H2)))

The error trap depends on the data type.

[FDibbins]

eek and I modified that to use ISERROR, and forgot to finish it...
=IF(ISERROR(INDEX($I2$2:$I$119,MATCH(0,INDEX(COUNTIF($I$2:I2,$I$2:$I$119),),0)),"",INDEX($I2$2:$I$119,MATCH(0,INDEX(COUNTIF($I$2:I2,$I$2:$I$119),),0))

[TheBakerBoy]

It will be text only. They are names of doughs so and example would be White 1 or WW 2 ect.

FDibbins, I tried it but it said that it had too many arguments for this function.
Tony, I tried your formula but it turned back a bunch of #NUM #REF.

The cell range that the names are in are I2 - I98

[Tony Valko]

Like this...

Data Range

	H	I
1	New List	Dough
2	White	White
3	White 1	White 1
4	Sour
5	Brown	Sour
6	WW 1	Brown
7	White 2
8		WW 1
9
10		White 2

This array formula** entered in H2:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(I:I,SMALL(IF(I$2:I$119<>"",ROW(I$2:I$119)),ROWS(H$2:H2)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[TheBakerBoy]

That is exactly what I am trying to do. When I entered the array formula in it didn't produce an error but it did produce a blank cell with nothing in it. Does the "zzzzz" need to be something? and if so what?

[Tony Valko]

Does the "zzzzz" need to be something?

No, that's what it's supposed to be.

Here's a small sample file that demonstrates this.

[TheBakerBoy]

When I tried this it just turned up empty cells.

[Tony Valko]

Can you post a SMALL file that demonstrates the problem you're having with the formula?

I don't want to have to wade through a lot of irrelevant data looking for the problem!

[TheBakerBoy]

The trouble is, when I try to copy the data over to another sheet that I can send to you, it works fine. Let me break down were and how I am getting the names in column I.
Column L - This is a reference back to a cell that give the name of the dough (white, ww, sour, brown, ect) This is a simple direct reference (=q12) Some of these are drawn from a data validation list.
Column K - This calculates how much dough is needed
Column J - These are present numbers so multiple runs of a dough can be made. This is done by the following formula =L2&" "&"1"
Column I - If there is a number in Column K then it will display the name in column J. This is done by a formula =IF(K2>0,J2,"")

As mentioned it seemed to work out fine when I copied it to a new sheet but it just doesn't seem to work in the place that I need it to.

[Tony Valko]

There is nothing obvious in your description that would cause a problem.

The only way I can troubleshoot it is to see it in context in a file.

[TheBakerBoy]

I believe that I figured out what was going wrong, it was two things I believe.

1. I had #ref and #n/a in column I. I cleaned these up and it looked like it was working
2. I noticed that the whole of column I was selected but I have other data further down the in the worksheet that might also so cause problems. I tweaked the formula to this:
{=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(I$1:I$119,SMALL(IF(I$2:I$119<>"",ROW(I$2:I$119)),ROWS(H$2:H2)))))}, Is there any possible issues from changing the I:I (right after Index) to I1:I119? I am unfamiliar with this function so I just want to make sure that nothing else might pop up.

[Tony Valko]

OK, both of those issues will cause problems!

You got rid of the errors so that's good.

WRT issue 2, the way the formula was written it doesn't matter if there is unrelated data further down the column because we used a specific test range IF(I$2:I$119<>"",ROW(I$2:I$119)).

Any data below I119 is not being evaluated.

So, use the formula as suggested:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(I:I,SMALL(IF(I$2:I$119<>"",ROW(I$2:I$119)),ROWS(H$2:H2)))))

Array entered!