Categorizing group of numbers which depicts an area coverage on a grid map

[Chlwls808]

Hi,

I think this is more of a VBA question than a function.

Imagine a grid system that is numbered based on rows and columns. The numbers are rows, and letters are column - sort of like an excel sheet except rows increase from bottom to top.

4A 4B 4C 4D
3A 3B 3C 3D
2A 2B 2C 2D
1A 1B 1C 1D

Now, imagine again that each grid has 9 boxes numbered from top to bottom - 3 numbers per row (something like your telephone keypad).

The following list are coordinates which covers the grid. The numbers following the letter represent the area of each grid it covers (keypad format).

4A89
4B789
4C78
3A2356
3B123456
3C1245
2C9
2D78
1C3
1D12

If the list were randomized, is there a programmatical way to distinguish the list and categorize them by their areas? Whether if it's in two separate columns or text, doesn't matter; As long as they are separated.

 

[tonyyy]

Could it be you're missing the named ranges \4A and \4B?

 

[Chlwls808]

Could it be you're missing the named ranges \4A and \4B?

View attachment 45812

Hey Tonyyy, I wiped out everything and redid the entire sheet and it's working like it supposed to! You are amazing!
How long have you been doing this if I may ask?

 

[tonyyy]

Glad it's working for you.
I started teaching myself vba around 2015, mostly for my own projects and as a hobby. There's a lot you can do with even a basic understanding.

 

[Chlwls808]

@tonyyy , one question:
If I want to expand the grid from 4x4 9digit grid to something larger, I can see that I need to expand the first block of code to coordinate the new grids..

  If Left(r1.Value, 2) = "4A" Then Set r2 = Range("A1")
    If Left(r1.Value, 2) = "4B" Then Set r2 = Range("D1")
    If Left(r1.Value, 2) = "4C" Then Set r2 = Range("G1")
    If Left(r1.Value, 2) = "4D" Then Set r2 = Range("J1")
    If Left(r1.Value, 2) = "3A" Then Set r2 = Range("A4")
    If Left(r1.Value, 2) = "3B" Then Set r2 = Range("D4")
    If Left(r1.Value, 2) = "3C" Then Set r2 = Range("G4")
    If Left(r1.Value, 2) = "3D" Then Set r2 = Range("J4")
    If Left(r1.Value, 2) = "2A" Then Set r2 = Range("A7")
    If Left(r1.Value, 2) = "2B" Then Set r2 = Range("D7")
    If Left(r1.Value, 2) = "2C" Then Set r2 = Range("G7")
    If Left(r1.Value, 2) = "2D" Then Set r2 = Range("J7")
    If Left(r1.Value, 2) = "1A" Then Set r2 = Range("A10")
    If Left(r1.Value, 2) = "1B" Then Set r2 = Range("D10")
    If Left(r1.Value, 2) = "1C" Then Set r2 = Range("G10")
    If Left(r1.Value, 2) = "1D" Then Set r2 = Range("J10")

But do I need to adjust the second block of code a certain way, too? If so, how must I change it accordingly?

        If Mid((r1), i, 1) = 1 Then r2.Offset(0, 0) = 1
        If Mid((r1), i, 1) = 2 Then r2.Offset(0, 1) = 2
        If Mid((r1), i, 1) = 3 Then r2.Offset(0, 2) = 3
        If Mid((r1), i, 1) = 4 Then r2.Offset(1, 0) = 4
        If Mid((r1), i, 1) = 5 Then r2.Offset(1, 1) = 5
        If Mid((r1), i, 1) = 6 Then r2.Offset(1, 2) = 6
        If Mid((r1), i, 1) = 7 Then r2.Offset(2, 0) = 7
        If Mid((r1), i, 1) = 8 Then r2.Offset(2, 1) = 8
        If Mid((r1), i, 1) = 9 Then r2.Offset(2, 2) = 9

Thanks again.

 

[tonyyy]

If you stay with a 9 digit grid pattern, the second block of code does not need to change.

 

[Chlwls808]

If you stay with a 9 digit grid pattern, the second block of code does not need to change.

OK, got that.

How about if my alpha-numeric system goes by 2 letter system..
so instead of [4A 4B 4C 4D], it's [4AA 4AB 4AC 4AD]. Also, the number can go as high as two-digit, too.

Does anything change if this was the case?

Thanks

 

[Peter_SSs]

I'm going back to the original question - which I could not have figured out if @tonyyy had not done all the hard work.
However, having now worked through those codes, I think the results can be achieved with much less code and without the need for any named ranges - but there is a possible catch, see below.

Sub FillAndMap()
  Dim r As Range, rA As Range
  Dim i As Long
  
  'Fill
  For Each r In Range("N1", Range("N" & Rows.Count).End(xlUp))
    With Cells(13 - 3 * Left(r.Value, 1), 3 * InStr(1, "ABCD", Mid(r.Value, 2, 1)) - 2).Resize(3, 3)
      For i = 3 To Len(r.Value)
        .Cells(Mid(r.Value, i, 1)) = Mid(r.Value, i, 1)
      Next i
    End With
  Next r
  
  'Map
  i = 0
  For Each rA In Range("A1:L12").SpecialCells(xlConstants).Areas
    i = i + 1
    Cells(1, 16 + i).Value = "Group " & i
    For Each r In Range("N1", Range("N" & Rows.Count).End(xlUp))
      If Not Intersect(rA, Cells(13 - 3 * Left(r.Value, 1), 3 * InStr(1, "ABCD", Mid(r.Value, 2, 1)) - 2).Resize(3, 3)) Is Nothing Then
        Cells(Rows.Count, 16 + i).End(xlUp).Offset(1).Value = r.Value
      End If
    Next r
  Next rA
End Sub

Here is my sample data and results of the above code (code puts the results in col Q onwards).

Chlwls808_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1														4C78			Group 1	Group 2
2														4B789			4C78	2D78
3		8	9	7	8	9	7	8						4A89			4B789	2C9
4		2	3	1	2	3	1	2						3C1245			4A89	1D12
5		5	6	4	5	6	4	5						3B123456			3C1245	1C3
6														3A2356			3B123456
7														2D78			3A2356
8														2C9
9									9	7	8			1D12
10									3	1	2			1C3
11
12
13
Sheet1

The possible problem I see is if the filled ranges actually join. First, here is my second sample and results of the above code.

Chlwls808_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1														4C78			Group 1	Group 2
2														4B789			4B789	4C78
3		8	9	7	8	9	7	8						4A89			4A89	4B789
4		2	3	1	2	3	1	2						3C1245			3B123456	3C1245
5		5	6	4	5	6	4	5						3B123456			3A2356	3B123456
6						9	7	8						3A2356			3B99	3C78
7						3	1	2						3C78				3B99
8														3B99				2C12
9														2C12				2B3
10														2B3
11
12
13
Sample

How do we decide if the groups are B3:H5 and F6:H7 or B3:E5 and F3:H7 or is it just one single group or is it something else?
You will see that my code has used the second of those grouping options which has meant some of the original codes appear in both groups - probably no what is wanted?

 

[Chlwls808]

I'm going back to the original question - which I could not have figured out if @tonyyy had not done all the hard work.
However, having now worked through those codes, I think the results can be achieved with much less code and without the need for any named ranges - but there is a possible catch, see below.

Sub FillAndMap()
  Dim r As Range, rA As Range
  Dim i As Long
 
  'Fill
  For Each r In Range("N1", Range("N" & Rows.Count).End(xlUp))
    With Cells(13 - 3 * Left(r.Value, 1), 3 * InStr(1, "ABCD", Mid(r.Value, 2, 1)) - 2).Resize(3, 3)
      For i = 3 To Len(r.Value)
        .Cells(Mid(r.Value, i, 1)) = Mid(r.Value, i, 1)
      Next i
    End With
  Next r
 
  'Map
  i = 0
  For Each rA In Range("A1:L12").SpecialCells(xlConstants).Areas
    i = i + 1
    Cells(1, 16 + i).Value = "Group " & i
    For Each r In Range("N1", Range("N" & Rows.Count).End(xlUp))
      If Not Intersect(rA, Cells(13 - 3 * Left(r.Value, 1), 3 * InStr(1, "ABCD", Mid(r.Value, 2, 1)) - 2).Resize(3, 3)) Is Nothing Then
        Cells(Rows.Count, 16 + i).End(xlUp).Offset(1).Value = r.Value
      End If
    Next r
  Next rA
End Sub

Here is my sample data and results of the above code (code puts the results in col Q onwards).

Chlwls808_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1														4C78			Group 1	Group 2
2														4B789			4C78	2D78
3		8	9	7	8	9	7	8						4A89			4B789	2C9
4		2	3	1	2	3	1	2						3C1245			4A89	1D12
5		5	6	4	5	6	4	5						3B123456			3C1245	1C3
6														3A2356			3B123456
7														2D78			3A2356
8														2C9
9									9	7	8			1D12
10									3	1	2			1C3
11
12
13
Sheet1

The possible problem I see is if the filled ranges actually join. First, here is my second sample and results of the above code.

Chlwls808_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1														4C78			Group 1	Group 2
2														4B789			4B789	4C78
3		8	9	7	8	9	7	8						4A89			4A89	4B789
4		2	3	1	2	3	1	2						3C1245			3B123456	3C1245
5		5	6	4	5	6	4	5						3B123456			3A2356	3B123456
6						9	7	8						3A2356			3B99	3C78
7						3	1	2						3C78				3B99
8														3B99				2C12
9														2C12				2B3
10														2B3
11
12
13
Sample

How do we decide if the groups are B3:H5 and F6:H7 or B3:E5 and F3:H7 or is it just one single group or is it something else?
You will see that my code has used the second of those grouping options which has meant some of the original codes appear in both groups - probably no what is wanted?

Thanks as always @Peter_SSs. If the borders are connecting, we can assume they are one group.

Also, the figures used in the sample map is an example. In a real application, the grids may expand larger than the sample 4x4 grid and figures can look like 30AA1236, 30AB1245, etc. In such scenario, what changes would I need to make in the function in order to accommodate more expansive map.

 

[Peter_SSs]

If the borders are connecting, we can assume they are one group.

Hmm, I'm not sure how easy that will be. Depends on the answer to this. The method I thought might have worked would treat this as one group only since the borders "connect" at the corner of F6 and G7. How many groups would you want reported here?

Chlwls808_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3		8	9	7	8	9
4		2	3	1	2	3
5		5	6	4	5	6
6						9
7							1	2
8
9
10
11
12
Sample (2)

what changes would I need to make in the function in order to accommodate more expansive map.

I will consider that once we know what is required in the above situation.

How would we know whether it was a 5x5 grid or 10x10 grid or ...?
Will the internal grids always be 3x3 or do they increase in size too?

 

[Chlwls808]

Hmm, I'm not sure how easy that will be. Depends on the answer to this. The method I thought might have worked would treat this as one group only since the borders "connect" at the corner of F6 and G7. How many groups would you want reported here?

This would be considered two groups.

How would we know whether it was a 5x5 grid or 10x10 grid or ...?
Will the internal grids always be 3x3 or do they increase in size too?

My intention was to have 9x26 (26 being the total no. of the alphabet - AA through AZ)
The internal grid is always 3x3.