Arranging rows function wanted

[Guest]

I’m 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 doesn’t matter if it will be a long
one. I don’t want to use an “on action†macro.

Thanks!

Tim

 

[Ivan Raiminius]

Hi Tim,

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

Regards,
Ivan

 

[Guest]

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(TRUE,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

 

[Guest]

all names

 

[Ivan Raiminius]

Hi Tim,

Ron already gave you the formula.

Regards,
Ivan

 

[Guest]

Thank you for your help.
I can’t get it to work because I don’t know what you mean by “ItemList†in B1
Am I supposed to name the range A1:A100 “ItemList�

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(TRUE,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

I’m 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 doesn’t matter if it will be a long
one. I don’t want to use an “on action†macro.

Thanks!

Tim

 

[Guest]

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

Thank you for your help.
I can’t get it to work because I don’t know what you mean by “ItemList†in B1
Am I supposed to name the range A1:A100 “ItemList�

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(TRUE,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

I’m 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 doesn’t matter if it will be a long
one. I don’t want to use an “on action†macro.

Thanks!

Tim

 

[Guest]

Enter this in B1,
=IF(ROW()<=COUNTA($A:$A),INDEX($A$1:$A$500,SMALL(IF($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.

 

[Guest]

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

 

[Guest]

It works perfectly now,

Thank you Ron and Tom !

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

Thank you for your help.
I can’t get it to work because I don’t know what you mean by “ItemList†in B1
Am I supposed to name the range A1:A100 “ItemList�

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(TRUE,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


:

I’m 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 doesn’t matter if it will be a long
one. I don’t want to use an “on action†macro.

Thanks!

Tim

 

[Guest]

Ivan asked that question and the response was:

all names