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.

 

[Ron Rosenfeld]

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!

I don't know why you need to define names to do that. The result can be achieved with a macro:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

The macro assumes your data is laid out as in your example. Changes to be made should be obvious, but post back if not.

=======================
Option Explicit
Sub ConcatNameData()
Dim cNames As Collection
Dim rSrc As Range, rdest As Range, c As Range
Dim rName As Range
Dim sRes As String
Dim i As Long
Dim sFirstAddress As String
Set rSrc = Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))
Set rdest = Range("F1")
rdest.EntireColumn.ClearContents
Set cNames = New Collection

'Get unique list of names
On Error Resume Next
For Each c In rSrc
cNames.Add Item:=c.Text, Key:=CStr(c.Text)
Next c

'Concatenate results
For i = 1 To cNames.Count
With rSrc.EntireColumn
Set c = .Find(cNames(i), LookIn:=xlValues, after:=Cells(Cells.Rows.Count, rSrc.Column))
If Not c Is Nothing Then
sFirstAddress = c.Address
sRes = c.Text & " " & c.Offset(columnoffset:=1).Text
Do
Set c = .FindNext(c)
If Not c Is Nothing And c.Address <> sFirstAddress Then
sRes = sRes & ", " & c.Offset(columnoffset:=1).Text
End If
Loop While Not c Is Nothing And c.Address <> sFirstAddress
End If
End With

rdest.Value = sRes
Set rdest = rdest(2, 1)

Next i
rdest.EntireColumn.AutoFit
End Sub
===========================