Replicating rows in a table by the columns of a different table

Question

These are my values:

Service_order   PENDING_DAYS    SERVICE_TYPE    ASC code    INOUTWTY    Part_code1  Part_code2  Part_code3  Part_code4  Part_code5

    4182864919        18              CI         3440690        LP      GH82-11218A GH96-09406A GH81-13594A GH02-11552A GH02-11553A
    4182868153        18              CI         4285812        LP      GH97-17670B             
    4182929636        17              CI         4276987        LP      GH97-17260C GH02-10203A         
    4182953067        16              CI         3440690        LP      GH97-17940C             
    4182954688        16              CI         6195657        LP      GH82-10555A GH97-17852A GH81-13071A     
    4182955036        16              PS         6195657        LP      GH97-17940C

I'm trying to write code to show the row values five times for each part.

This is the formula that I'm using:

=INDEX(A:I;(ROW(1:1)-1)/5+1;IF(COLUMN(A:A)=1;1;MOD(ROW(1:1)-1;5)+1))

and the result that it gave me is like this:

Service_order   PENDING_DAYS    SERVICE_TYPE    ASC code    INOUTWTY    Part_code

4182864919            18              CI         3440690       LP       GH82-11218A
4182864919            CI           3440690          LP      GH82-11218A GH96-09406A
4182864919          3440690           LP       GH82-11218A  GH96-09406A GH81-13594A
4182864919            LP         GH82-11218A   GH96-09406A  GH81-13594A GH02-11552A
4182864919        GH82-11218A    GH96-09406A   GH81-13594A  GH02-11552A GH02-11553A
4182868153            18              CI         4285812       LP       GH97-17670B
4182868153            CI           4285812         LP       GH97-17670B 0
4182868153          4285812           LP       GH97-17670B      0       0
4182868153            LP         GH97-17670B        0           0       0
4182868153        GH97-17670B          0            0           0       0
4182929636             17             CI         4276987        LP      GH97-17260C
4182929636             CI          4276987          LP      GH97-17260C GH02-10203A
4182929636          4276987           LP       GH97-17260C  GH02-10203A 0
4182929636             LP        GH97-17260C   GH02-10203A       0      0
4182929636        GH97-17260C    GH02-10203A        0            0      0

which is wrong. As you can see the values start to show again at the second row.

The values that I want to show are (service_order, Pending_days, Service_type, ASC_code, InOutWty) and when it reaches the part_code, the values that I told be the same but the part_code starts to change (part_code1, part_code2, part_code3, part_code4, part_code5).

To show you what I want the result be like, I created this:

Service_order   PENDING_DAYS    SERVICE_TYPE    ASC code    INOUTWTY    Part_code1

4182864919            18              CI        3440690        LP       GH82-11218A
4182864919            18              CI        3440690        LP       GH96-09406A
4182864919            18              CI        3440690        LP       GH81-13594A
4182864919            18              CI        3440690        LP       GH02-11552A
4182864919            18              CI        3440690        LP       GH02-11553A
4182868153            18              CI        4285812        LP       GH97-17670B
4182868153            18              CI        4285812        LP       0
4182868153            18              CI        4285812        LP       0
4182868153            18              CI        4285812        LP       0
4182868153            18              CI        4285812        LP       0
4182929636            17              CI        4276987        LP       GH97-17260C
4182929636            17              CI        4276987        LP       GH02-10203A
4182929636            17              CI        4276987        LP       0
4182929636            17              CI        4276987        LP       0
4182929636            17              CI        4276987        LP       0

Where am I going wrong?

You are trying to what is sometimes referred to as "melting" the data. Maybe [this](http://stackoverflow.com/questions/10921791/melt-reshape-in-excel-using-vba) helps. — Robin Gertenbach, Jul 12 '16 at 14:48
You want **all** the items to be copied 5 times, even if they don't have 5 distinct `Part_code`s? — EBH, Jul 12 '16 at 14:50
@EBH exactly, cause some service_order do have 5 part_code request and some of them don't. — ali javadi, Jul 12 '16 at 14:56
@RobinGertenbach I've read the article but the problem is that i'm not good with vba, could you help fix the code that causing the problem? — ali javadi, Jul 12 '16 at 15:36

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

Well, this answer has 4 parts:

Sort the columns in your data.
Define ranges in your data (it will make things clearer).
Make a 5*copy of all data but the part_code
Convert the part_code matrix to a vector

Let's start...

But before that let's distinguish between the part_code columns, and all the rest of the table, we will handle them separately. So from now on I'll call the left side of the table "Info" table, and the right side will be just the "part_code" table

1. Sorting columns in Info table

Well, since you have only 5 columns in the Info table the quicker way will be to sort them manually. If you have more you can copy-transpose the first row to a column, then sort it, and then order the columns by hand. There may be a smarter way, but that wasn't the essence of your question. This sorting is important for the HLOOKUP function to work.

2. Define ranges in your data

I'll assume that you know how to do that, but if not, have a look here. We will define all the range of the Info table as Info and all the range of the Part code table, without the first row as part_code.

3. Make a 5*copy of all rows in Info table

Using HLOOKUP and ROW we will make 5 copies:

=HLOOKUP(A$11,Info,CEILING((ROW()-ROW($A$11))/5+1,1))

Copy the head row of Info (in any order you like) to a new place, and place the formula above in the most upper left cell where the data should go in (in this example it's A12).

Then correct the formula so A$11 at the start and $A$11 in the middle of the formula, will be the first column title - keep the $ the same for correct referencing. You can now drag this all the way down and left to fill up all the info part of your table:

4.Convert the part_code matrix to a vector

In the last column of your new table "part_code" paste this formula (source) just below the column title:

=OFFSET(part_code,TRUNC((ROW()-ROW($F$12))/COLUMNS(part_code)),MOD(ROW()-ROW($F$12),COLUMNS(part_code)),1,1)

and again correct all the $F$12 to the same cell were you pasted it in (keeping the $!), and drag it all the way down:

And that's all!

Hope it solve your problem ;)

HI...I've tried the third way, but it keep showing me the first part_Code number in all the five cells. — ali javadi, Jul 12 '16 at 19:19
@alijavadi What do you mean by the "third way"? Check 3 things: (1) that you defined `part_code` range correctly. (2) that you copied the formula in part 4 correctly to the right cell. (3) that you changed _both_ instances of `$F$12` in this formula to the cell where you copied this formula. — EBH, Jul 12 '16 at 20:31

Replicating rows in a table by the columns of a different table

1 Answers1