Automatically create ranges and then name them

[Sarah Cichos]

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

 

[lifescholar]

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.

 

[Sarah Cichos]

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!

 

[Sarah Cichos]

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

 

[Sarah Cichos]

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!

 

[Sarah Cichos]

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

 

[lifescholar]

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.