Transpose group of numbers from a column with guided logic by a second column

[RIC63]

I need to transpose 3 sets of numbers (col.A, C and E) in groups according to a filter column where groups of equal numbers determine precisely which set of numbers to copy each time.

For example... first must be analyzed col B then copy group of number of col.A every 3 columns starting from I1
then will be analyzed colum D and groups of numbers of col.C will be copied starting from J1..every 3 colums
the last check will be done on col.F and groups of numbers of col.E will be copied starting from K1..every 3 colums

i mean, pratically every change in value in the guide column determines the start / end of a set of values to be copied


thanks for any kind of support

[Aussiebear]

Can you explain the logic behind the transposing of numbers.

[RIC63]

Hi Aussiebear

I try to explain it with a small diagram attached here


thanks for any help you can give meExplain.jpg

[Aussiebear]

AS I now understand it, you would like to:

Within the table A1:F45, split the table into Groups of two columns A1:B45, C145, and E1:F45, then

Using A1:B45, Define by ranking from Highest to Lowest values in column B to create sub groups, then
Copy & Paste each sub groups offset value in Column A, starting at Column I for the Highest within Columns A & B, then offsetting 3 columns to the right for the next highest ranking, until there are no ranking values left.

Next we repeat the process

Using C145, Define by ranking from Highest to Lowest values in column D to create sub groups, then
Copy & Paste each sub groups offset value in Column C, starting at Column J for the Highest within Columns C & D, then offsetting 3 columns to the right for the next highest ranking, until there are no ranking values left.

Next we repeat the process

Using E1:F45, Define by ranking from Highest to Lowest values in column F to create sub groups, then
Copy & Paste each sub groups offset value in Column E, starting at Column K for the Highest within Columns E & F, then offsetting 3 columns to the right for the next highest ranking, until there are no ranking values left.

Is this correct? BTW, an image to "analyse" define the rules is painfully poor to any one wanting to assist you.

[RIC63]

the groups of 15 numbers have no relevance but the set of 45 numbers which tomorrow could be 50 or 90.... with the change in value along each of the three columns (B, D and F) which precisely determines the start / end of the set of numbers to be taken from the columns (A, C and E)


thanks again for your help

[snb]

This will do:

Sub M_snb()
  sn = Cells(1).CurrentRegion
  ReDim sp(UBound(sn), 3 * UBound(sn))
  
  y = sn(1, 2)
  y1 = sn(1, 4)
  y2 = sn(1, 6)
  n = -1
  n1 = -1
  n2 = -1
  For j = 1 To UBound(sn)
    If sn(j, 2) <> y Then
      y = sn(j, 2)
      n = 0
      q = q + 1
    Else
      n = n + 1
    End If
    If sn(j, 4) <> y1 Then
      y1 = sn(j, 4)
      n1 = 0
      q1 = q1 + 1
    Else
      n1 = n1 + 1
    End If
    If sn(j, 6) <> y2 Then
      y2 = sn(j, 6)
      n2 = 0
      q2 = q2 + 1
    Else
      n2 = n2 + 1
    End If

    sp(n, 3 * q) = sn(j, 1)
    sp(n1, 3 * q1 + 1) = sn(j, 3)
    sp(n2, 3 * q2 + 2) = sn(j, 5)
  Next

  Cells(10, 9).Resize(UBound(sp), UBound(sp, 2)) = sp
End Sub

[RIC63]

hello Aussiebear


I thought that the image would give quickly what I wanted, however, what you write is correct except that the data table is already sorted there is only to copy the data with the logic you described


Thanks again

[Aussiebear]

@R1C63, I understand that but there are people who hate seeing lots of colours in sets of data, so. my attempt to define the rules for the transposition is based on a non colour data table

[RIC63]

Hello snb
thanks for your help


I tested your code and it does exactly what I wanted, I do some tests with a wider range of data (column length equal to 80 rows) and let you know


thank you

[snb]

In the meantime an improved VBA version has arisen:

Sub M_snb()
  sn = Cells(1).CurrentRegion
  ReDim sp(UBound(sn), 3 * UBound(sn))

  For jj = 0 To 2
    q = 0
    sp(0, jj) = sn(1, 1 + 2 * jj)

    For j = 2 To UBound(sn)
      n = n + 1
      If sn(j, 2 + 2 * jj) <> sn(j - 1, 2 + 2 * jj) Then
        n = 0
        q = q + 1
      End If

      sp(n, 3 * q + jj) = sn(j, 1 + 2 * jj)
    Next
  Next
  
  Cells(10, 9).Resize(UBound(sp), UBound(sp, 2)) = sp
End Sub