Rearranging data from 1 row several categories to columns

[capo888]

Hello,

Looking to find a solution that is repeatable for the data manipulation below in the screen print. My company will not allow me to add the XL2BB addin so this is the best I can do. The data in the "Currently" section is from a our master database, but no one likes the way it looks with the data going across. Sales wants it to go down like in the "To This" section.

As this master will change often I was hoping to do this in Power Query, but could not figure it out. Any help would be appreciated.

Regards,
George

 

[djclements]

Not sure what version of Excel you are using, but with modern Excel you could try using a formula. For example:

=WRAPROWS(TOCOL(HSTACK(EXPAND(A2:A3,,4,""),B2:M3)),4)

This assumes that each of the 3 areas (Base, Commission and Allowance) contain the same 4 columns (Desc, Jan, Feb, Mar), appearing in the same order. To make it more dynamic, as well as to include the column headers, a few extra steps can be added:

=LET(
   tbl, A1:M3,
   v, DROP(tbl,1,1),
   n, COLUMNS(v)/3,
   c, TAKE(tbl,1,-n),
   r, TAKE(DROP(tbl,1),,1),
   VSTACK(IF(ISBLANK(c),"",c),WRAPROWS(TOCOL(HSTACK(EXPAND(r,,n,""),v)),n))
)

Again, it's important to note, this method will only return the correct results with a consistent dataset (where each of the 3 areas all contain the same columns). Adjust the range references as needed.

 

[Peter_SSs]

Assuming that the data actually starts in row 1 as shown below, you could give this macro a try with a copy of your workbook.

Sub Rearrange()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, r As Long, BlockSize As Long, Blocks As Long
  
  With Range("A1").CurrentRegion
    a = .Value
    Blocks = .Rows(1).SpecialCells(xlBlanks).Count - 1
    BlockSize = (.Columns.Count - 1) / Blocks
    
    ReDim b(1 To (Blocks + 1) * (UBound(a) - 1) + 1, 1 To BlockSize)
    For j = 1 To BlockSize
      b(1, j) = a(1, j + 1)
    Next j
    r = 1
    For i = 2 To UBound(a)
      r = r + 1
      b(r, 1) = a(i, 1)
      For k = 1 To Blocks
        r = r + 1
        For j = 1 To BlockSize
          b(r, j) = a(i, (k - 1) * BlockSize + j + 1)
        Next j
      Next k
    Next i
    .Offset(UBound(a) + 2).Resize(UBound(b, 1), UBound(b, 2)).Value = b
  End With
End Sub

Before:

capo888.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			Jan	Feb	Mar		Jan	Feb	Mar		Jan	Feb	Mar
2	John	Base	79	60	49	Commission	36	38	51	Allowance	45	11	46
3	Jane	Base	71	61	39	Commission	65	43	10	Allowance	28	76	7
4
Sheet1

After:

capo888.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			Jan	Feb	Mar		Jan	Feb	Mar		Jan	Feb	Mar
2	John	Base	79	60	49	Commission	36	38	51	Allowance	45	11	46
3	Jane	Base	71	61	39	Commission	65	43	10	Allowance	28	76	7
4
5
6		Jan	Feb	Mar
7	John
8	Base	79	60	49
9	Commission	36	38	51
10	Allowance	45	11	46
11	Jane
12	Base	71	61	39
13	Commission	65	43	10
14	Allowance	28	76	7
Sheet1

 

[capo888]

Thank you. I modified the second macro just because I am little more familiar. I am going to play with the WRAPROWS formula once the deadline is met and I am going to try Power Query as this request is growing in complexity each meeting the sales team has.