Hi,

I have the following problem... for roughly 12K rows & 2 columns, I
want to define ranges & names without manually going through.
How do I do that??

Example (only taking 10 rows here for simplicity):

A B
1 John 5
2 John 6
3 John 7
4 Max 3
5 Max 4
6 Lucy 52
7 Lucy 53
8 Lucy 54
9 Lucy 55
10 Theo 13

I'll want to name range B1:B3 "John", range B4:B5 "Max", etc.

Thanks in advance!
Sarah

On Sep 12, 9:34*pm, Sarah Cichos <sarah.cic...@gmail.com> wrote:
> Hi,
>
> I have the following problem... for roughly 12K rows & 2 columns, I
> want to define ranges & names without manually going through.
> How do I do that??
>
> Example (only taking 10 rows here for simplicity):
>
> * * * *A * * * *B
> 1 * John * * * * 5
> 2 * John * * * * 6
> 3 * John * * * * 7
> 4 * Max *3
> 5 * Max *4
> 6 * Lucy * * * * 52
> 7 * Lucy * * * * 53
> 8 * Lucy * * * * 54
> 9 * Lucy * * * * 55
> 10 Theo *13
>
> I'll want to name range B1:B3 "John", range B4:B5 "Max", etc.
>
> Thanks in advance!
> Sarah

This requires a macro. However, before writing one, it would be useful
what you are planning to do with these ranges afterwards. Are you
trying to look-up in them, or calculate totals, etc? If so, then you
don't need to define names, there are other ways to do this.

Perhaps you could post a little more info.

On Sep 12, 1:58*pm, lifescholar <steve.ja...@lifescholar.net> wrote:
> On Sep 12, 9:34*pm, Sarah Cichos <sarah.cic...@gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> > Hi,
>
> > I have the following problem... for roughly 12K rows & 2 columns, I
> > want to define ranges & names without manually going through.
> > How do I do that??
>
> > Example (only taking 10 rows here for simplicity):
>
> > * * * *A * * * *B
> > 1 * John * * * * 5
> > 2 * John * * * * 6
> > 3 * John * * * * 7
> > 4 * Max *3
> > 5 * Max *4
> > 6 * Lucy * * * * 52
> > 7 * Lucy * * * * 53
> > 8 * Lucy * * * * 54
> > 9 * Lucy * * * * 55
> > 10 Theo *13
>
> > I'll want to name range B1:B3 "John", range B4:B5 "Max", etc.
>
> > Thanks in advance!
> > Sarah
>
> This requires a macro. However, before writing one, it would be useful
> what you are planning to do with these ranges afterwards. Are you
> trying to look-up in them, or calculate totals, etc? If so, then you
> don't need to define names, there are other ways to do this.
>
> Perhaps you could post a little more info.

Thanks for looking into it. I was planning to lookup the range names
(with an index function) and then concatenate the information from
column B into one cell, separated by comma. My results should look
like this (based on the example above):
John 5, 6, 7
Max 3, 4
Lucy 52, 53, 54, 55
Theo 13

I have all that already, I only need to find a way to name the ranges
to pull this data.
If there's an easier way to do that, I'll gladly take it!

On Sep 12, 2:33*pm, Sarah Cichos <sarah.cic...@gmail.com> wrote:
> On Sep 12, 1:58*pm, lifescholar <steve.ja...@lifescholar.net> wrote:
>
>
>
>
>
>
>
>
>
> > On Sep 12, 9:34*pm, Sarah Cichos <sarah.cic...@gmail.com> wrote:
>
> > > Hi,
>
> > > I have the following problem... for roughly 12K rows & 2 columns, I
> > > want to define ranges & names without manually going through.
> > > How do I do that??
>
> > > Example (only taking 10 rows here for simplicity):
>
> > > * * * *A * * * *B
> > > 1 * John * * * * 5
> > > 2 * John * * * * 6
> > > 3 * John * * * * 7
> > > 4 * Max *3
> > > 5 * Max *4
> > > 6 * Lucy * * * * 52
> > > 7 * Lucy * * * * 53
> > > 8 * Lucy * * * * 54
> > > 9 * Lucy * * * * 55
> > > 10 Theo *13
>
> > > I'll want to name range B1:B3 "John", range B4:B5 "Max", etc.
>
> > > Thanks in advance!
> > > Sarah
>
> > This requires a macro. However, before writing one, it would be useful
> > what you are planning to do with these ranges afterwards. Are you
> > trying to look-up in them, or calculate totals, etc? If so, then you
> > don't need to define names, there are other ways to do this.
>
> > Perhaps you could post a little more info.
>
> Thanks for looking into it. I was planning to lookup the range names
> (with an index function) and then concatenate the information from
> column B into one cell, separated by comma. My results should look
> like this (based on the example above):
> John 5, 6, 7
> Max 3, 4
> Lucy 52, 53, 54, 55
> Theo 13
>
> I have all that already, I only need to find a way to name the ranges
> to pull this data.
> If there's an easier way to do that, I'll gladly take it!

One more thing: I'm not looking to sum these numbers up. So, sumif
won't do

On Sep 12, 2:33*pm, Sarah Cichos <sarah.cic...@gmail.com> wrote:
> On Sep 12, 1:58*pm, lifescholar <steve.ja...@lifescholar.net> wrote:
>
>
>
>
>
>
>
>
>
> > On Sep 12, 9:34*pm, Sarah Cichos <sarah.cic...@gmail.com> wrote:
>
> > > Hi,
>
> > > I have the following problem... for roughly 12K rows & 2 columns, I
> > > want to define ranges & names without manually going through.
> > > How do I do that??
>
> > > Example (only taking 10 rows here for simplicity):
>
> > > * * * *A * * * *B
> > > 1 * John * * * * 5
> > > 2 * John * * * * 6
> > > 3 * John * * * * 7
> > > 4 * Max *3
> > > 5 * Max *4
> > > 6 * Lucy * * * * 52
> > > 7 * Lucy * * * * 53
> > > 8 * Lucy * * * * 54
> > > 9 * Lucy * * * * 55
> > > 10 Theo *13
>
> > > I'll want to name range B1:B3 "John", range B4:B5 "Max", etc.
>
> > > Thanks in advance!
> > > Sarah
>
> > This requires a macro. However, before writing one, it would be useful
> > what you are planning to do with these ranges afterwards. Are you
> > trying to look-up in them, or calculate totals, etc? If so, then you
> > don't need to define names, there are other ways to do this.
>
> > Perhaps you could post a little more info.
>
> Thanks for looking into it. I was planning to lookup the range names
> (with an index function) and then concatenate the information from
> column B into one cell, separated by comma. My results should look
> like this (based on the example above):
> John 5, 6, 7
> Max 3, 4
> Lucy 52, 53, 54, 55
> Theo 13
>
> I have all that already, I only need to find a way to name the ranges
> to pull this data.
> If there's an easier way to do that, I'll gladly take it!

Never mind - I found a way! It includes match to look up the position
of the name and then indirect(address)... to locate the lookup. Thank
you anyway!

On Sep 12, 2:35*pm, Sarah Cichos <sarah.cic...@gmail.com> wrote:
> On Sep 12, 2:33*pm, Sarah Cichos <sarah.cic...@gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> > On Sep 12, 1:58*pm, lifescholar <steve.ja...@lifescholar.net> wrote:
>
> > > On Sep 12, 9:34*pm, Sarah Cichos <sarah.cic...@gmail.com> wrote:
>
> > > > Hi,
>
> > > > I have the following problem... for roughly 12K rows & 2 columns, I
> > > > want to define ranges & names without manually going through.
> > > > How do I do that??
>
> > > > Example (only taking 10 rows here for simplicity):
>
> > > > * * * *A * * * *B
> > > > 1 * John * * * * 5
> > > > 2 * John * * * * 6
> > > > 3 * John * * * * 7
> > > > 4 * Max *3
> > > > 5 * Max *4
> > > > 6 * Lucy * * * * 52
> > > > 7 * Lucy * * * * 53
> > > > 8 * Lucy * * * * 54
> > > > 9 * Lucy * * * * 55
> > > > 10 Theo *13
>
> > > > I'll want to name range B1:B3 "John", range B4:B5 "Max", etc.
>
> > > > Thanks in advance!
> > > > Sarah
>
> > > This requires a macro. However, before writing one, it would be useful
> > > what you are planning to do with these ranges afterwards. Are you
> > > trying to look-up in them, or calculate totals, etc? If so, then you
> > > don't need to define names, there are other ways to do this.
>
> > > Perhaps you could post a little more info.
>
> > Thanks for looking into it. I was planning to lookup the range names
> > (with an index function) and then concatenate the information from
> > column B into one cell, separated by comma. My results should look
> > like this (based on the example above):
> > John 5, 6, 7
> > Max 3, 4
> > Lucy 52, 53, 54, 55
> > Theo 13
>
> > I have all that already, I only need to find a way to name the ranges
> > to pull this data.
> > If there's an easier way to do that, I'll gladly take it!
>
> One more thing: I'm not looking to sum these numbers up. So, sumif
> won't do

On Sep 12, 10:42*pm, Sarah Cichos <sarah.cic...@gmail.com> wrote:
> On Sep 12, 2:33*pm, Sarah Cichos <sarah.cic...@gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> > On Sep 12, 1:58*pm, lifescholar <steve.ja...@lifescholar.net> wrote:
>
> > > On Sep 12, 9:34*pm, Sarah Cichos <sarah.cic...@gmail.com> wrote:
>
> > > > Hi,
>
> > > > I have the following problem... for roughly 12K rows & 2 columns, I
> > > > want to define ranges & names without manually going through.
> > > > How do I do that??
>
> > > > Example (only taking 10 rows here for simplicity):
>
> > > > * * * *A * * * *B
> > > > 1 * John * * * * 5
> > > > 2 * John * * * * 6
> > > > 3 * John * * * * 7
> > > > 4 * Max *3
> > > > 5 * Max *4
> > > > 6 * Lucy * * * * 52
> > > > 7 * Lucy * * * * 53
> > > > 8 * Lucy * * * * 54
> > > > 9 * Lucy * * * * 55
> > > > 10 Theo *13
>
> > > > I'll want to name range B1:B3 "John", range B4:B5 "Max", etc.
>
> > > > Thanks in advance!
> > > > Sarah
>
> > > This requires a macro. However, before writing one, it would be useful
> > > what you are planning to do with these ranges afterwards. Are you
> > > trying to look-up in them, or calculate totals, etc? If so, then you
> > > don't need to define names, there are other ways to do this.
>
> > > Perhaps you could post a little more info.
>
> > Thanks for looking into it. I was planning to lookup the range names
> > (with an index function) and then concatenate the information from
> > column B into one cell, separated by comma. My results should look
> > like this (based on the example above):
> > John 5, 6, 7
> > Max 3, 4
> > Lucy 52, 53, 54, 55
> > Theo 13
>
> > I have all that already, I only need to find a way to name the ranges
> > to pull this data.
> > If there's an easier way to do that, I'll gladly take it!
>
> Never mind - I found a way! It includes match to look up the position
> of the name and then indirect(address)... to locate the lookup. Thank
> you anyway!

No problem.