1

The table that I am using looks like this

X    x1    x1    x1    x1    x1    x1    x1    x1    x2    x2    x2    x2   
Y    y1    y1    y1    y1    y2    y2    y2    y2    y1    y1    y1    y1
Z    z1    z2    z3    z4    z1    z2    z3    z4    z1    z2    z3    z4
30%  23    22    25    19    24    27    22    32    21    19    31    29 
31%  25    23    27    22    26    29    24    33    24    22    33    31
32%  29    26    31    25    28    33    27    36    27    25    35    33
.    .     .     .     .     .     .     .     .     .     .     .     .
.    .     .     .     .     .     .     .     .     .     .     .     .
.    .     .     .     .     .     .     .     .     .     .     .     .
.    .     .     .     .     .     .     .     .     .     .     .     .
300% 500   520   613   476   512   312   324   754   432   345   421   444

What I am trying to achieve is something like this

X   Y   Z   Per. Value
x1  y1  z1  30%  23
x1  y1  z1  31%  25
x1  y1  z1  32%  29
.   .   .   .    .
.   .   .   .    .
x1  y1  z1  300% 500
x1  y1  z2  30%  22
x1  y1  z2  31%  23
x1  y1  z2  32%  26
.   .   .   .    .
.   .   .   .    .
x1  y2  z1  30%  24
x1  y2  z1  31%  26
.   .   .   .    .
.   .   .   .    .
x1  y2  z4  30%  32
x1  y2  z4  31%  33  
.   .   .   .    .
.   .   .   .    .
.   .   .   .    .
x10 y3  z7  300% 431

I tried using the macro given in this thread How to "flatten" or "collapse" a 2D Excel table into 1D?, but couldn't get it to work, probably because my table is more than 2D, more like 4D. I am not a macro expert.

Is there any way to convert the first table to look like second table, macros or otherwise. I realize that it dramatically increases the number of rows, but that is not a problem.

Community
  • 1
  • 1
Amit Singh Parihar
  • 527
  • 3
  • 14
  • 23
  • You will need a 2-D variant array and nested For ... Next loops. However, it is unclear from your example just what you want to go where. You should have posted your current effort (working or not). Someone with experience can look at the code and determine your intent and correct it. I mean, what happened to *22, 23* & *26* ? –  Apr 03 '17 at 17:04
  • I have added more info to the table wanted for clarification. Hope it helps to explain what happened to 22,23 & 26. Also, I have a link in the explanation which talks about a VBA script that I used. Didn't put it in here so as to not make it large and complicated. – Amit Singh Parihar Apr 03 '17 at 17:54
  • Does this have to be done with VBA? It isn't terribly hard with cell formulas. – Adam Apr 03 '17 at 18:03
  • I am just looking for a solution, doesn't have to be macros, anything helps. – Amit Singh Parihar Apr 03 '17 at 18:26
  • [Is it possible to “unpivot” or “reverse pivot” in Excel?](https://superuser.com/q/78439/52365); [Learn how to unpivot static tables in Excel 2016](https://blogs.office.com/2015/12/15/learn-how-to-unpivot-static-tables-in-excel-2016/); [Unpivot columns (Power Query)](https://support.office.com/en-us/article/Unpivot-columns-Power-Query-0f7bad4b-9ea1-49c1-9d95-f588221c7098) – GSerg Apr 03 '17 at 20:26

2 Answers2

3

If this doesn't have to be done with VBA, here's a solution:

We will use index and match with a custom built table of keys for the match lookups.

1. ROW 1

I inserted a row across the top of your data set to match against when I do the column lookup: The formula is showing as text in B1, but is applied in rest of row 1.

enter image description here

2. ROW 20

Across row 20, I have the following:

A20: =B20 & C20 & D20

B20: x1

C20:y1

D20: z1

E20: 0.3

F20: =INDEX($C$5:$N$12,MATCH(E20,$B$5:$B$12),MATCH(A20,$C$1:$N$1,0))

Column F uses index(array, rowValue, columnValue

array is the location of your data table. rowValue is a match of the percent column with the left hand header column of your data table. columnValue uses the concatenated xyz column in a match against the header row I inserted back at the first step.

enter image description here

3. ROW 21

Now all you have to do is build out your table. Here's Row 21, from right to left.

Column E: You want to increment Column E by 1% every row, unless you make it to 301%, in which case you start over:

=IF(E20+0.01 < 3.001 , E20 + 0.01 , 0.3)

Actually - when we add that many 0.01's (270 of them, we get a little floating point drift, so I will round off othe nearest 0.01 every time:

=MROUND(IF(E20+0.01<3.001,E20+0.01,0.3),0.01)

Column D: You want the value of Column D to change only if Column E just reset to 0.3. Otherwise, stay the same:

IF(E21=0.3,IF(D20="z4","z1","z"&RIGHT(D20,1)+1),D20)

Column C: Similarly, Column D changes only when E AND D just reset.

=IF(AND(D21="z1",E21=0.3),IF(C20="y1","y2","y1"),C20)

Column B: Similarly, this column only changes when all three of the others reset:

=IF(AND(C21="y1",D21="z1",E21=0.3),"x"&RIGHT(B20,1)+1,B20)

(Note that if your data went past x!0, you'd have to tinker with the Right( formula a little.)

Now just fill down from row 21 to row 21,701. Should be good

RESULT

I don't have a complete data table to feed it, so there are lots of holes. Looks like it is matching your intent pretty well where there is data (Note that I have hidden some row because they were (expected) N/A's.

enter image description here

Adam
  • 840
  • 6
  • 24
1

You can read the table in an array, build another array and copy that to your target destination like this:

Option Explicit
Option Base 1

Sub Flatten()
Dim inArr As Variant, col As Long, rw As Long
Dim outArr() As String, rwcount As Long, outRng As Range
inArr = Range("A1:M7").Value    'change to correct range
Set outRng = Range("A12")       'change to correct range
ReDim outArr(1 To (UBound(inArr, 2) - 1) * (UBound(inArr, 1) - 3) + 1, 5)
rwcount = 1

    outArr(1, 1) = "X": outArr(1, 2) = "Y": outArr(1, 3) = "Z"
    outArr(1, 4) = "Per.": outArr(1, 5) = "Value"
    For col = 2 To UBound(inArr, 2)
        For rw = 4 To UBound(inArr, 1)
            rwcount = rwcount + 1
            outArr(rwcount, 1) = inArr(1, col)
            outArr(rwcount, 2) = inArr(2, col)
            outArr(rwcount, 3) = inArr(3, col)
            outArr(rwcount, 4) = inArr(rw, 1)
            outArr(rwcount, 5) = inArr(rw, col)
        Next rw
    Next col
    outRng.Resize(UBound(outArr, 1), UBound(outArr, 2)).Value = outArr
End Sub
Jochen
  • 1,254
  • 1
  • 7
  • 9