Arranging rows function wanted
 

Im having about 100 rows in the €œA€�-column where the user can enter names.
The names will be entered randomly; one name can be at A1 the next at A23
and so on.

In column €œB€� I want the names from the A column to be arranged without
blank rows starting from B1. It should not matter if there is 80 names in the
A column or 20 names

I want this to be made by a function, it doesnt matter if it will be a long
one. I dont want to use an €œon action€� macro.

Thanks!

Tim

Arranging rows function wanted
 

Hi Tim,

in column B shall be all the names or unique only?

Regards,
Ivan

Arranging rows function wanted
 

Try something like this ARRAY FORMULA*:

With an input list in A1:A100
B1: ItemList

B2:
=IF(SUMPRODUCT(($A$1:$A$100<"")*ISERROR(MATCH($A$ 1:$A$100,$B$1:B1,0)))<0,INDEX($A$1:$A$100,MATCH(T RUE,ISERROR(IF(ISBLANK($A$1:$A$100),FALSE,MATCH($A $1:$A$100,$B$1:$B1,0))),0),1),"")

Copy from B2 and Paste into B3 and down as far as needed

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Tim" wrote:

Im having about 100 rows in the €œA€�-column where the user can enter names.
The names will be entered randomly; one name can be at A1 the next at A23
and so on.

In column €œB€� I want the names from the A column to be arranged without
blank rows starting from B1. It should not matter if there is 80 names in the
A column or 20 names

I want this to be made by a function, it doesnt matter if it will be a long
one. I dont want to use an €œon action€� macro.

Thanks!

Tim

Arranging rows function wanted
 

all names

"Ivan Raiminius" wrote:

Hi Tim,

in column B shall be all the names or unique only?

Regards,
Ivan

Arranging rows function wanted
 

Hi Tim,

Ron already gave you the formula.

Regards,
Ivan

Arranging rows function wanted
 

Thank you for your help.
I cant get it to work because I dont know what you mean by €œItemList€� in B1
Am I supposed to name the range A1:A100 €œItemList€�?


"Ron Coderre" wrote:

Try something like this ARRAY FORMULA*:

With an input list in A1:A100
B1: ItemList

B2:
=IF(SUMPRODUCT(($A$1:$A$100<"")*ISERROR(MATCH($A$ 1:$A$100,$B$1:B1,0)))<0,INDEX($A$1:$A$100,MATCH(T RUE,ISERROR(IF(ISBLANK($A$1:$A$100),FALSE,MATCH($A $1:$A$100,$B$1:$B1,0))),0),1),"")

Copy from B2 and Paste into B3 and down as far as needed

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Tim" wrote:

Im having about 100 rows in the €œA€�-column where the user can enter names.
The names will be entered randomly; one name can be at A1 the next at A23
and so on.

In column €œB€� I want the names from the A column to be arranged without
blank rows starting from B1. It should not matter if there is 80 names in the
A column or 20 names

I want this to be made by a function, it doesnt matter if it will be a long
one. I dont want to use an €œon action€� macro.

Thanks!

Tim

Arranging rows function wanted
 

Sorry for the confusion....

B1 is just a column title. you can put anything you like in that cell.

Also, remember: you need to commit the formula with cltrl+shift+enter, NOT
just enter.

Does that help?
***********
Regards,
Ron

XL2002, WinXP-Pro


"Tim" wrote:

Thank you for your help.
I cant get it to work because I dont know what you mean by €œItemList€� in B1
Am I supposed to name the range A1:A100 €œItemList€�?


"Ron Coderre" wrote:

Try something like this ARRAY FORMULA*:

With an input list in A1:A100
B1: ItemList

B2:
=IF(SUMPRODUCT(($A$1:$A$100<"")*ISERROR(MATCH($A$ 1:$A$100,$B$1:B1,0)))<0,INDEX($A$1:$A$100,MATCH(T RUE,ISERROR(IF(ISBLANK($A$1:$A$100),FALSE,MATCH($A $1:$A$100,$B$1:$B1,0))),0),1),"")

Copy from B2 and Paste into B3 and down as far as needed

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Tim" wrote:

Im having about 100 rows in the €œA€�-column where the user can enter names.
The names will be entered randomly; one name can be at A1 the next at A23
and so on.

In column €œB€� I want the names from the A column to be arranged without
blank rows starting from B1. It should not matter if there is 80 names in the
A column or 20 names

I want this to be made by a function, it doesnt matter if it will be a long
one. I dont want to use an €œon action€� macro.

Thanks!

Tim

Arranging rows function wanted
 

Enter this in B1,
=IF(ROW()<=COUNTA($A:$A),INDEX($A$1:$A$500,SMALL(I F($A$1:$A$500<"",ROW($A$1:$A$500)),ROW()),1),"")

enter with Ctrl+Shift+Enter rather than enter since this is an array
formula, then drag down for a many names as you ever expect to display.
Increase the 500 to include the last row which the user may enter names - but
remember that the more you include, the slower the function will operate.
Also, you can't use a reference like A:A or A1:A65536 in the array formula
part. (you could use A1:A65535, but as I said, the more you consider the
slower the formula.

--
Regards,
Tom Ogilvy




"Tim" wrote:

all names

"Ivan Raiminius" wrote:

Hi Tim,

in column B shall be all the names or unique only?

Regards,
Ivan

Arranging rows function wanted
 

Hi, Tom

The OP didn't mention if the same name could be repeated in the input column.

I may have incorrectly assumed that:
1)there may be duplicates and
2)duplicates should be ignored.

If that's the case, your much shorter formula is the way to go.

***********
Best Regards,
Ron

XL2002, WinXP-Pro


"Tom Ogilvy" wrote:

Enter this in B1,
=IF(ROW()<=COUNTA($A:$A),INDEX($A$1:$A$500,SMALL(I F($A$1:$A$500<"",ROW($A$1:$A$500)),ROW()),1),"")

enter with Ctrl+Shift+Enter rather than enter since this is an array
formula, then drag down for a many names as you ever expect to display.
Increase the 500 to include the last row which the user may enter names - but
remember that the more you include, the slower the function will operate.
Also, you can't use a reference like A:A or A1:A65536 in the array formula
part. (you could use A1:A65535, but as I said, the more you consider the
slower the formula.

--
Regards,
Tom Ogilvy




"Tim" wrote:

all names

"Ivan Raiminius" wrote:

Hi Tim,

in column B shall be all the names or unique only?

Regards,
Ivan

Arranging rows function wanted
 

It works perfectly now,

Thank you Ron and Tom ! :-)

"Ron Coderre" wrote:

Sorry for the confusion....

B1 is just a column title. you can put anything you like in that cell.

Also, remember: you need to commit the formula with cltrl+shift+enter, NOT
just enter.

Does that help?
***********
Regards,
Ron

XL2002, WinXP-Pro


"Tim" wrote:

Thank you for your help.
I cant get it to work because I dont know what you mean by €œItemList€� in B1
Am I supposed to name the range A1:A100 €œItemList€�?


"Ron Coderre" wrote:

Try something like this ARRAY FORMULA*:

With an input list in A1:A100
B1: ItemList

B2:
=IF(SUMPRODUCT(($A$1:$A$100<"")*ISERROR(MATCH($A$ 1:$A$100,$B$1:B1,0)))<0,INDEX($A$1:$A$100,MATCH(T RUE,ISERROR(IF(ISBLANK($A$1:$A$100),FALSE,MATCH($A $1:$A$100,$B$1:$B1,0))),0),1),"")

Copy from B2 and Paste into B3 and down as far as needed

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Tim" wrote:

Im having about 100 rows in the €œA€�-column where the user can enter names.
The names will be entered randomly; one name can be at A1 the next at A23
and so on.

In column €œB€� I want the names from the A column to be arranged without
blank rows starting from B1. It should not matter if there is 80 names in the
A column or 20 names

I want this to be made by a function, it doesnt matter if it will be a long
one. I dont want to use an €œon action€� macro.

Thanks!

Tim

Arranging rows function wanted
 

Ivan asked that question and the response was:

all names


--
Regards,
Tom Ogilvy


"Ron Coderre" wrote:

Hi, Tom

The OP didn't mention if the same name could be repeated in the input column.

I may have incorrectly assumed that:
1)there may be duplicates and
2)duplicates should be ignored.

If that's the case, your much shorter formula is the way to go.

***********
Best Regards,
Ron

XL2002, WinXP-Pro


"Tom Ogilvy" wrote:

Enter this in B1,
=IF(ROW()<=COUNTA($A:$A),INDEX($A$1:$A$500,SMALL(I F($A$1:$A$500<"",ROW($A$1:$A$500)),ROW()),1),"")

enter with Ctrl+Shift+Enter rather than enter since this is an array
formula, then drag down for a many names as you ever expect to display.
Increase the 500 to include the last row which the user may enter names - but
remember that the more you include, the slower the function will operate.
Also, you can't use a reference like A:A or A1:A65536 in the array formula
part. (you could use A1:A65535, but as I said, the more you consider the
slower the formula.

--
Regards,
Tom Ogilvy




"Tim" wrote:

all names

"Ivan Raiminius" wrote:

Hi Tim,

in column B shall be all the names or unique only?

Regards,
Ivan