View Full Version : [SOLVED:] Transpose group of numbers from a column with guided logic by a second column
RIC63
04-09-2022, 08:47 AM
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
04-09-2022, 04:17 PM
Can you explain the logic behind the transposing of numbers.
RIC63
04-10-2022, 01:45 AM
Hi Aussiebear
I try to explain it with a small diagram attached here
thanks for any help you can give me29605
Aussiebear
04-10-2022, 02:24 AM
AS I now understand it, you would like to:
Within the table A1:F45, split the table into Groups of two columns A1:B45, C1:D45, 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 C1:D45, 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
04-10-2022, 02:49 AM
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
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
04-10-2022, 03:33 AM
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
04-10-2022, 03:36 AM
@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
04-10-2022, 03:44 AM
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.