Arranging rows function wanted

[Tim]

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

[Ron Coderre]

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


"Tim" wrote:

> 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
>

[Tim]

all names

"Ivan Raiminius" wrote:

> Hi Tim,
>
> in column B shall be all the names or unique only?
>
> Regards,
> Ivan
>
>

[Ivan Raiminius]

Hi Tim,

Ron already gave you the formula.

Regards,
Ivan

[Tim]

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”?


"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(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
>
>
> "Tim" wrote:
>
> > 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
> >

[Ron Coderre]

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 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”?
>
>
> "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(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
> >
> >
> > "Tim" wrote:
> >
> > > 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
> > >

[Tom Ogilvy]

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.

--
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
> >
> >

[Ron Coderre]

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(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.
>
> --
> 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
> > >
> > >

[Tim]

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 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”?
> >
> >
> > "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(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
> > >
> > >
> > > "Tim" wrote:
> > >
> > > > 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
> > > >

[Tom Ogilvy]

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(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.
> >
> > --
> > 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
> > > >
> > > >