Grouping rows with the same values into columns "B" and "C"

H

harzer

Board Regular
Joined
Dec 15, 2021
Messages
159
Office Version
  1. 2016
Platform
  1. Windows
Hello all,
As I can't find a solution to my problem, I post my request hoping that an expert among you will help me solve my problem.

Presentation of my worksheet: The worksheet is named: "Issus", it has 11 columns and many (many) rows whose length varies each month, I have reduced the length of the worksheet for the needs of submitting my application.

Here is an overview of the "Issues" sheet:

Classeur1
ABCDEFGHIJK
1JeunePèreMèreEleveurAgeVolièreCageNé(e)ToursInformationElevage
2856-022/2011 M0811-021/2008 M435-007/2009 FAE --> CF10a 0m 0j1B716-04-044TFemelle x
3876-025/2006 F0811-021/2008 M435-007/2009 FAE --> CF10a 0m 0j1B715-04-064TFemelle x
4876-054/2004 F0811-021/2008 M856-047/2002 FAE --> CF10a 10m 10j1B815-04-064TFemelle x
5AE-003/2011 M235-096/2005 M856-078/2010 FAE --> CF10a 10m 10j1H815-04-074TFemelle x
6AE-014/2011 M25-004/2009 M856-117/2009 FAE --> CF10a 10m 10j1H815-04-074TFemelle x
7AE-015/2011 F838-010/2008 M856-146/2009 FAE --> CF10a 10m 15j1H1020-03-104TFemelle x
8AE-035/2013 F838-010/2008 M856-146/2009 FAT10a 10m 15j1H1215-04-104TFemelle x
9AE-035R/2012 F838-010/2008 M856-146/2009 FAT10a 10m 15j1H1215-04-104TFemelle x
10AE-036/2013 M838-010/2008 M856-146/2009 FAT10a 10m 15j1H1215-04-104TFemelle x
11AE-037/2013 M856-004/2010 M856-146/2009 FAT10a 10m 18j2B1215-04-104TFemelle x
12AE-039/2012 M856-061/2002 M856-186/2007 FAT10a 10m 18j2B1215-04-104TFemelle x
13AE-040/2012 F856-093/2010 M876-027/2005 FAT10a 11m 24j2B1215-04-104TFemelle x
14AE-041/2012 F856-093/2010 M876-053/2009 FDB10a 11m 24j2B1215-04-104TFemelle x
15AE-042/2012 F856-111/2010 M876-053/2009 FDB10a 11m 24j2B1215-04-104TFemelle x
16AE-042R-12/2013 F856-111/2010 M876-053/2009 FDB10a 11m 24j2B1215-04-104TFemelle x
17AE-046/2012 M856-111/2010 MAE-044/2011 FEA10a 11m 24j2B1215-04-104TFemelle x
18AE-047/2012 F856-111/2010 MAE-056/2012 FEA10a 11m 24j2B1216-04-104TFemelle x
19AE-048/2012 F856-111/2010 MAE-056/2012 FEA11a 0m 1j2B1216-04-104TFemelle x
20AE-049/2012 M856-111/2010 MAE-056/2012 FEA11a 0m 1j2B1716-04-104TFemelle x
21AE-050/2012 M856-111/2010 MAE-056/2012 FEA11a 0m 1j2H1731-03-114TFemelle x
22AE-051/2012 M856-111/2010 MAE-056/2012 FEA11a 0m 29j3H1704-04-114TFemelle x
23AE-052/2012 M856-111/2010 MAE-056/2012 FEA11a 0m 2j3H1715-04-114TFemelle x
24AE-053/2012 F856-115/2009 MAE-060/2010 FEA11a 0m 2j3H1715-04-114TFemelle x
25AE-067/2012 M856-117/2009 MAE-062/2010 FEA11a 0m 2j4B1715-04-114TFemelle x
26AE-083R-12/2013 F856-117/2009 MAE-133/2010 FEA11a 0m 2j4B1715-04-114TFemelle x
27AE-091/2011 M856-117/2009 MAE-140/2011 FEA11a 0m 2j4B1715-04-114TFemelle x
28AE-092/2011 M856-117/2009 MAE-140/2011 FEA12a 0m 11j4B1715-04-114TFemelle x
29AT-064/2010 M856-117/2009 MAE-140/2011 FEA12a 0m 17j4B1715-04-114TFemelle x
30AT-066/2010 F856-117/2009 MAE-140/2011 FEA12a 0m 17j4B3015-04-114TFemelle x
31AT-067/2010 F876-037/2009 MAE-140/2011 FEA12a 0m 18j4B3015-04-114TFemelle x
32AT-126/2012 M876-037/2009 MAE-140/2011 FEA12a 0m 18j4B3015-04-114TFemelle x
33AT-128/2011 F876-037/2009 MAT-123/2011 FEA12a 0m 18j4B3016-04-114TFemelle x
34AT-150/2012 F876-049/2009 MAT-136/2011 FEA12a 0m 18j4B3016-04-114TFemelle x
35CF*-131/2012 M876-049/2009 MAT-136/2011 FEA12a 0m 18j4B3022-04-114TFemelle x
36CF*-132/2012 M876-050/2009 MAT-136/2011 FEA12a 0m 18j4B3004-04-124TFemelle
37CF*-133/2012 FAC10-008/2010 MAT-136/2011 FEA12a 0m 18j4B3001-05-125TFemelle
38CF*-134/2012 FAE-014/2011 MMM-034/2011 FEA12a 0m 18j4B3001-05-125TFemelle
39CF*-135/2012 MAE-014/2011 MMM-034/2011 FEA12a 0m 18j4B3001-05-125TMâle
40CF*-136/2012 FAE-014/2011 MMM-034/2011 FEA12a 0m 18j4B3001-05-125TMâle
41HCH57-018/2010 FAE-014/2011 MMM-034/2011 FMM12a 0m 29j4B3001-05-125TMâle
42HCH57-019/2010 FHCH55-027/2011 MMM-034/2011 FMM12a 1m 3j4B3002-05-125TMâle
43MM-009/2011 FMA-009/2009 MMM-034/2011 FMM13a 0m 17j4B3002-05-125TMâle
44MM-013/2011 MMA-009/2009 MMM-034/2011 FMM13a 0m 17j4B3002-05-125TMâle
45MM-020/2011 MMA-017/2008 MMM-034/2011 FMM13a 0m 17j4B3009-05-125TMâle
46MM-047/2010 FMA-017/2008 MMM-034/2011 FMM13a 0m 18j4H3009-05-125TMâle
47MM-048/2010 MMM-020/2011 MMM-034/2011 FMM13a 0m 18j4H3009-05-125TMâle
48PG-004/2011 MMM-020/2011 MMM-047/2010 FMY13a 0m 18j4H3009-05-125TMâle
49PG-009/2011 MMM-020/2011 MPG-005/2004 FPG13a 0m 18j4H3009-05-125TMâle
50PG-024/2011 MMM-020/2011 MPG-012/2008 FPG13a 0m 18j4H3009-05-125TMâle
51PG-025/2011 FMM-020/2011 MPG-012/2008 FPG13a 0m 18j5B3015-06-125TMâle
52PG-026/2011 FMM-020/2011 MPG-012/2008 FPG13a 0m 18j5B3015-06-125TMâle
53PG-028/2006 FMM-020/2011 MPG-012/2008 FPG13a 0m 18j5H3018-06-125TMâle
54PG-038/2012 FMM-020/2011 MPG-012/2008 FPG13a 0m 18j5H3018-06-125TMâle
55PG-039/2010 MMM-020/2011 MPG-012/2008 FPG13a 0m 18j5H3018-06-125TMâle
56PG-041/2010 FMM-020/2011 MPG-017/2009 FPG13a 1m 13j5H3018-06-125TMâle
57PG-041/2012 FPG-009/2011 MPG-017/2009 FPG16a 0m 18j5H3023-06-125TMâle
58PG-042/2012 MPG-009/2011 MPG-017/2009 FPG16a 0m 18j5H3023-06-125TMâle
59PG-044/2010 FPG-024/2011 MPG-019/2006 FPG17a 0m 18j5H3023-06-125TMâle
60PG-045/2010 FPG-039/2010 MPG-019/2006 FPG17a 0m 18j5H3003-05-135TMâle
61PG-051/2010 FPG-039/2010 MPG-024/2009 FPG19a 0m 17j5H3003-05-135TMâle
62PG-066/2010 FPG-039/2010 MPG-024/2009 FPG9a 10m 19j5H3004-05-135TMâle
63PG-067/2007 FPG-068/2006 MPG-076/2009 FPG9a 10m 23j5H3018-05-135TMâle
64PG-068/2007 FPG-068/2006 MPG-076/2009 FPG9a 11m 15j5H3010-06-135TMâle
65SC57-018/2010 FPG-075/2010 MPG-076/2009 FPG9a 11m 29j5H3014-06-135TMâle
Issus


The Macros request that I present to you will be made in 3 different macros, these 3 Macros are similar because they do almost the same job:

First Macro: In the "Issue" sheet, I want to group all the rows including the content of the cell of the 2nd column (Column "B") and at the same time that of the cell of the 3rd column (Column "C") are identical with the cells of the following rows to the same columns.

Unless I am mistaken, here is the desired result:

Classeur1
ABCDEFGHIJK
1JeunePèreMèreEleveurAgeVolièreCageNé(e)ToursInformationElevage
2856-022/2011 M0811-021/2008 M435-007/2009 FAE --> CF10a 0m 0j1B716-04-044TFemelle x
3876-025/2006 F0811-021/2008 M435-007/2009 FAE --> CF10a 0m 0j1B715-04-064TFemelle x
4
5876-054/2004 F0811-021/2008 M856-047/2002 FAE --> CF10a 10m 10j1B815-04-064TFemelle x
6
7AE-003/2011 M235-096/2005 M856-078/2010 FAE --> CF10a 10m 10j1H815-04-074TFemelle x
8
9AE-014/2011 M25-004/2009 M856-117/2009 FAE --> CF10a 10m 10j1H815-04-074TFemelle x
10
11AE-015/2011 F838-010/2008 M856-146/2009 FAE --> CF10a 10m 15j1H1020-03-104TFemelle x
12AE-035/2013 F838-010/2008 M856-146/2009 FAT10a 10m 15j1H1215-04-104TFemelle x
13AE-035R/2012 F838-010/2008 M856-146/2009 FAT10a 10m 15j1H1215-04-104TFemelle x
14AE-036/2013 M838-010/2008 M856-146/2009 FAT10a 10m 15j1H1215-04-104TFemelle x
15
16AE-037/2013 M856-004/2010 M856-146/2009 FAT10a 10m 18j2B1215-04-104TFemelle x
17
18AE-039/2012 M856-061/2002 M856-186/2007 FAT10a 10m 18j2B1215-04-104TFemelle x
19
20AE-040/2012 F856-093/2010 M876-027/2005 FAT10a 11m 24j2B1215-04-104TFemelle x
21
22AE-041/2012 F856-093/2010 M876-053/2009 FDB10a 11m 24j2B1215-04-104TFemelle x
23
24AE-042/2012 F856-111/2010 M876-053/2009 FDB10a 11m 24j2B1215-04-104TFemelle x
25AE-042R-12/2013 F856-111/2010 M876-053/2009 FDB10a 11m 24j2B1215-04-104TFemelle x
26
27AE-046/2012 M856-111/2010 MAE-044/2011 FEA10a 11m 24j2B1215-04-104TFemelle x
28
29AE-047/2012 F856-111/2010 MAE-056/2012 FEA10a 11m 24j2B1216-04-104TFemelle x
30AE-048/2012 F856-111/2010 MAE-056/2012 FEA11a 0m 1j2B1216-04-104TFemelle x
31AE-049/2012 M856-111/2010 MAE-056/2012 FEA11a 0m 1j2B1716-04-104TFemelle x
32AE-050/2012 M856-111/2010 MAE-056/2012 FEA11a 0m 1j2H1731-03-114TFemelle x
33AE-051/2012 M856-111/2010 MAE-056/2012 FEA11a 0m 29j3H1704-04-114TFemelle x
34AE-052/2012 M856-111/2010 MAE-056/2012 FEA11a 0m 2j3H1715-04-114TFemelle x
35
36AE-053/2012 F856-115/2009 MAE-060/2010 FEA11a 0m 2j3H1715-04-114TFemelle x
37
38AE-067/2012 M856-117/2009 MAE-062/2010 FEA11a 0m 2j4B1715-04-114TFemelle x
39
40AE-083R-12/2013 F856-117/2009 MAE-133/2010 FEA11a 0m 2j4B1715-04-114TFemelle x
41
42AE-091/2011 M856-117/2009 MAE-140/2011 FEA11a 0m 2j4B1715-04-114TFemelle x
43AE-092/2011 M856-117/2009 MAE-140/2011 FEA12a 0m 11j4B1715-04-114TFemelle x
44AT-064/2010 M856-117/2009 MAE-140/2011 FEA12a 0m 17j4B1715-04-114TFemelle x
45AT-066/2010 F856-117/2009 MAE-140/2011 FEA12a 0m 17j4B3015-04-114TFemelle x
46
47AT-067/2010 F876-037/2009 MAE-140/2011 FEA12a 0m 18j4B3015-04-114TFemelle x
48AT-126/2012 M876-037/2009 MAE-140/2011 FEA12a 0m 18j4B3015-04-114TFemelle x
49
50AT-128/2011 F876-037/2009 MAT-123/2011 FEA12a 0m 18j4B3016-04-114TFemelle x
51
52AT-150/2012 F876-049/2009 MAT-136/2011 FEA12a 0m 18j4B3016-04-114TFemelle x
53CF*-131/2012 M876-049/2009 MAT-136/2011 FEA12a 0m 18j4B3022-04-114TFemelle x
54
55CF*-132/2012 M876-050/2009 MAT-136/2011 FEA12a 0m 18j4B3004-04-124TFemelle
56
57CF*-133/2012 FAC10-008/2010 MAT-136/2011 FEA12a 0m 18j4B3001-05-125TFemelle
58
59CF*-134/2012 FAE-014/2011 MMM-034/2011 FEA12a 0m 18j4B3001-05-125TFemelle
60CF*-135/2012 MAE-014/2011 MMM-034/2011 FEA12a 0m 18j4B3001-05-125TMâle
61CF*-136/2012 FAE-014/2011 MMM-034/2011 FEA12a 0m 18j4B3001-05-125TMâle
62HCH57-018/2010 FAE-014/2011 MMM-034/2011 FMM12a 0m 29j4B3001-05-125TMâle
63
64HCH57-019/2010 FHCH55-027/2011 MMM-034/2011 FMM12a 1m 3j4B3002-05-125TMâle
65
66MM-009/2011 FMA-009/2009 MMM-034/2011 FMM13a 0m 17j4B3002-05-125TMâle
67MM-013/2011 MMA-009/2009 MMM-034/2011 FMM13a 0m 17j4B3002-05-125TMâle
68
69MM-020/2011 MMA-017/2008 MMM-034/2011 FMM13a 0m 17j4B3009-05-125TMâle
70MM-047/2010 FMA-017/2008 MMM-034/2011 FMM13a 0m 18j4H3009-05-125TMâle
71
72MM-048/2010 MMM-020/2011 MMM-034/2011 FMM13a 0m 18j4H3009-05-125TMâle
73
74PG-004/2011 MMM-020/2011 MMM-047/2010 FMY13a 0m 18j4H3009-05-125TMâle
75
76PG-009/2011 MMM-020/2011 MPG-005/2004 FPG13a 0m 18j4H3009-05-125TMâle
77
78PG-024/2011 MMM-020/2011 MPG-012/2008 FPG13a 0m 18j4H3009-05-125TMâle
79PG-025/2011 FMM-020/2011 MPG-012/2008 FPG13a 0m 18j5B3015-06-125TMâle
80PG-026/2011 FMM-020/2011 MPG-012/2008 FPG13a 0m 18j5B3015-06-125TMâle
81PG-028/2006 FMM-020/2011 MPG-012/2008 FPG13a 0m 18j5H3018-06-125TMâle
82PG-038/2012 FMM-020/2011 MPG-012/2008 FPG13a 0m 18j5H3018-06-125TMâle
83PG-039/2010 MMM-020/2011 MPG-012/2008 FPG13a 0m 18j5H3018-06-125TMâle
84
85PG-041/2010 FMM-020/2011 MPG-017/2009 FPG13a 1m 13j5H3018-06-125TMâle
86
87PG-041/2012 FPG-009/2011 MPG-017/2009 FPG16a 0m 18j5H3023-06-125TMâle
88PG-042/2012 MPG-009/2011 MPG-017/2009 FPG16a 0m 18j5H3023-06-125TMâle
89
90PG-044/2010 FPG-024/2011 MPG-019/2006 FPG17a 0m 18j5H3023-06-125TMâle
91
92PG-045/2010 FPG-039/2010 MPG-019/2006 FPG17a 0m 18j5H3003-05-135TMâle
93
94PG-051/2010 FPG-039/2010 MPG-024/2009 FPG19a 0m 17j5H3003-05-135TMâle
95PG-066/2010 FPG-039/2010 MPG-024/2009 FPG9a 10m 19j5H3004-05-135TMâle
96
97PG-067/2007 FPG-068/2006 MPG-076/2009 FPG9a 10m 23j5H3018-05-135TMâle
98PG-068/2007 FPG-068/2006 MPG-076/2009 FPG9a 11m 15j5H3010-06-135TMâle
99
100SC57-018/2010 FPG-075/2010 MPG-076/2009 FPG9a 11m 29j5H3014-06-135TMâle
Issus


Second Macros: In the same sheet ("Issue"), I want to group all the rows whose contents of the cells of the 2nd column (Column "B") are identical with the cells of the following rows to the same column.

Third Macros: Still in the same "Issue" sheet, I want to group all the rows whose contents of the cells of the third column (Column "C") are identical with the cells of the following rows in the same column.

As my "Issus" sheet is large (Length large) and so that the search does not take long, can you (Please) use (if possible) a dictionary combined with tables to speed up the processing , gender :
For i = 2 TB UBound(tablo, 1).

I add that it would also be desirable (for more visibility) to insert an empty line between each group of data (See the presentation of the desired result.

I remain at your disposal for any additional information.
Cheers.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Hi @harzer, thanks for posting on the forum.

harzer said:
First Macro: In the "Issue" sheet

Second Macros: In the same sheet ("Issue")

Third Macros: Still in the same "Issue" sheet
Click to expand...
You want the macro to run on the same sheet, I guess you want view 1, 2 or 3 with just running a macro.

So if you want First Macro run Macro1, if you want Second macro run Macro2 and if you want Third macro run Macro3.
The 3 macros execute the MacroAll that receives the view number you want to see.

Then put all of the following code in a module:
VBA Code: 
Sub Macro1()      'columns B and C
  Call MacroAll(1)
End Sub

Sub Macro2()      'columns B
  Call MacroAll(2)
End Sub

Sub Macro3()      'columns C
  Call MacroAll(3)
End Sub

Sub MacroAll(n)
  Dim sh As Worksheet
  Dim dic As Object
  Dim a As Variant, b As Variant, ky As Variant
  Dim i As Long, j As Long, k As Long, y As Long
  Dim nRow As Long, nCol As Long, filA As Long
  Dim kya As String
 
  Set sh = Sheets("Issus")
  Set dic = CreateObject("Scripting.Dictionary")
  a = sh.Range("A2:K" & sh.Range("B" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1) * 2, 1 To UBound(a, 1))
  ReDim c(1 To UBound(a, 1) * 2, 1 To UBound(a, 2))
 
  For i = 1 To UBound(a, 1)
    If a(i, 2) <> "" Then
      Select Case n
        Case 1: kya = a(i, 2) & "|" & a(i, 3)
        Case 2: kya = a(i, 2)
        Case 3: kya = a(i, 3)
      End Select
      If Not dic.exists(kya) Then
        y = y + 1
        b(y, 1) = i
        dic(kya) = y & "|" & 1
      Else
        nRow = Split(dic(kya), "|")(0)
        nCol = Split(dic(kya), "|")(1)
        nCol = nCol + 1
        b(nRow, nCol) = i
        dic(kya) = nRow & "|" & nCol
      End If
    End If
  Next
 
  For Each ky In dic.keys
    nRow = Split(dic(ky), "|")(0)
    nCol = Split(dic(ky), "|")(1)
    For i = 1 To nCol
      k = k + 1
      filA = b(nRow, i)
      For j = 1 To UBound(a, 2)
        c(k, j) = a(filA, j)
      Next
    Next
    k = k + 1
  Next
 
  sh.Range("A2").Resize(UBound(c, 1), UBound(c, 2)).Value = c
  Set dic = Nothing
  Set sh = Nothing
  Erase a, b, c
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------
 
Upvote 0
Solution
Hello DanteAmor,
Thank you for your proposal, it is exactly what I expected, the code is perfect and gives me the desired result.
Thanks again for sharing your knowledge. Congratulations to you.
 
Upvote 1
Hello Dante Amor,
Can you show me the code that adds a blank line between each group.
I tried to decipher your code but given my level, I have difficulties to do so, unless you will be kind enough to comment on your code. Big THANKS to you.
 
Upvote 0
Hello Hazer.
With pleasure I explain the operation of the macro.​


harzer said:
Can you show me the code that adds a blank line between each group.
Click to expand...
In this line:​
Rich (BB code): 
    Next
    'This increment in counter k is to leave an empty row.
    k = k + 1
  Next



harzer said:
unless you will be kind enough to comment on your code
Click to expand...
I tried to explain each line with an example. I hope it is useful to you.​

VBA Code: 
Sub MacroAll(n)
  Dim sh As Worksheet
  Dim dic As Object
  Dim a As Variant, b As Variant, ky As Variant
  Dim i As Long, j As Long, k As Long, y As Long
  Dim nRow As Long, nCol As Long, filA As Long
  Dim kya As String
 
  Set sh = Sheets("Issus")
  Set dic = CreateObject("Scripting.Dictionary")
 
  'Load data into array 'a'
  a = sh.Range("A2:K" & sh.Range("B" & Rows.Count).End(3).Row).Value
 
  'Dimension the array 'b' that will contain the references to the rows of the array 'a'
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 1))
  'This is important, array 'b' must be sized to store all 2 possible situations:
  '(1) The number of rows in matrix 'b' will be equal to the number of rows in matrix 'a'. It means that each row can be a group.
  '(2) The number of columns in matrix 'b' will be equal to the number of rows in matrix 'a'. It means that all the rows can belong to the same group.
 
  'Dimension the array 'c' that will contain the output
  ReDim c(1 To UBound(a, 1) * 2, 1 To UBound(a, 2))
  'If all the rows of the matrix 'a' are different groups, it means that we need twice as many rows in the output,
  'one row for the data and another blank row, so the number of rows of the matrix 'c' is equal to the number of rows in matrix 'a' by 2.
  'The number of columns in matrix 'c' is equal to the number of columns in matrix 'a' since the column output will be the same.
 
  'Read array 'a'
  For i = 1 To UBound(a, 1)
    If a(i, 2) <> "" Then
      Select Case n
        Case 1: kya = a(i, 2) & "|" & a(i, 3)   'columns B and C
        Case 2: kya = a(i, 2)                   'columns B
        Case 3: kya = a(i, 3)                   'columns C
      End Select
     
      If Not dic.exists(kya) Then
        'Store the key in the dictionary. Increase the 'y' counter
        'Store the counter 'y' as item in the dictionary, which will indicate in which row of matrix 'b' we will store the row numbers of matrix 'a' .
        'In the matrix 'b' in the coordinate (counter, column 1) the row number of the matrix 'a' is stored.
        y = y + 1
        b(y, 1) = i
        dic(kya) = y & "|" & 1
      Else
        'If the key already exists in the dictionary, then retrieve the counter and column number of that key.
        'Increments the column, in coordinate(count,newcolumn) stores the row number of matrix 'a'.
        '
        'I will try to explain this with an example, we have the data "0811-021" in row 1 of array 'a'.
        'So in array 'b' in coordinate (1,1) I store the number 1 (this is the row number of 'a').
        'Records below we have the same data "0811-021" in row 8 of matrix 'a', then in 'b' in row 1 but now in column 2 I store the number 8.
        'Later on we have the same data "0811-021" in row 23 of matrix 'a', so in b(1,3) we store 23.
       
        'In the dictionary we have as key "0811-021" and as item we have 1 "|" 3, and this means that we have the data "0811-021" located in row 1 of matrix 'b' and that it is found 3 times.
        'Representation of matrix 'b'
        'b(1, 1) = 1
        'b(1, 2) = 8
        'b(1, 3) = 23

        nRow = Split(dic(kya), "|")(0)
        nCol = Split(dic(kya), "|")(1)
        nCol = nCol + 1
        b(nRow, nCol) = i
        dic(kya) = nRow & "|" & nCol
      End If
    End If
  Next
 
  'We now have all the unique keys stored in the dictionary.
  For Each ky In dic.keys
    'We read each of those keys.
    'We get the row number of 'b' and the number of items corresponding to the key.
    'In the example above, for the key "0811-021" we get 1 "|" 3.
    nRow = Split(dic(ky), "|")(0)
    nCol = Split(dic(ky), "|")(1)
   
    For i = 1 To nCol
      'We make a loop to read row 1 of matrix 'b' from column 1 to column 3 and get the row numbers of matrix 'a',
      'in the example we get:1, 8 and 23
      k = k + 1
      filA = b(nRow, i)
      For j = 1 To UBound(a, 2)
        'We store in matrix 'c' the output of the 3 records of matrix 'a' 1, 8 and 23
        'In this way we already have in a group the records to the key "0811-021"
        c(k, j) = a(filA, j)
      Next
    Next
    'This increment in counter k is to leave an empty row.
    k = k + 1
  Next
 
  'Unloading array 'c' into cells
  sh.Range("A2").Resize(UBound(c, 1), UBound(c, 2)).Value = c
  Set dic = Nothing
  Set sh = Nothing
  Erase a, b, c
End Sub


Cordially​
Dante Amor
--------------
 
Upvote 0
Hello Dante Amor,
Thank you for the comments, the code is beautifully written, the comments help me a lot to understand, especially they show me all the steps to solve the problem, without these comments, impossible for me to see the progress of the code. Thanks again.
Harzer.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Copy rows from one sheet to another sheet whose name is given by the user.
Replies
4
Views
490
harzer
H
H
  • Solved
Modify content of cells in column "F" according to the content of cells in column "A"
Replies
5
Views
520
igold
igold
H
  • Solved
Count nonblank lines (between blank lines)
2
Replies
15
Views
1K
Alex Blakenburg
A
H
  • Question Question
copy some columns based on the value in column "A".
Replies
9
Views
365
Cubist
Cubist
H
  • Solved
Copy rows if cells in column "K" end with either "X" or "x".
Replies
3
Views
235
rollis13
rollis13

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top