R
richzip
I would like an easy way to copy data from a pivot table, to columns outside
of the pivot table. I figured out how to do most of what I need to do,
except I can't get it to show in the row I want it in. I have included a
sample of how this should look--I hope the formatting goes through correctly.
The pivot table has 3 columns: ID, code, hours. Each employee (ID) can
have anywhere from 1-5 rows of hours, broken down by pay code. I want to
take these values and copy them into columns that are not within the pivot
table, and each column is also broken down by pay code
I figured out that I can copy the values into the appropriate column, but
only in the same row it is in within the pivot table. However, I want to the
value to be moved up to the first row for each employee. As you can see,
each employee has a different number of rows, and the values may not be in
the same relative row for each employee. Also, as you can see, not every
column will have a value for every employee
In the example, look at employee 1: had 57 reg hours, 22 ITC hours. I want
these hours to be copied to the first row for that employee (row 4), in the
appropriate column. So "57" would go in cell D4; "22" would go in cell F4
I used a formula throughout each column to copy the values over, but it does
it in the same row. (for example: in cell F5 I have: =IF(B5="ITC",C5,"").
That will put "22" into cell F5, but I want it to go up to row 4. This
becomes more complicated in that each employee has a different number of
rows, and the different codes are not in the same "relative row" for each
employee (ie. ITC is not always the 2nd row for each employee)
Any suggestions?
Columns A-C contain the pivot table; Columns D and on are where I want to
copy the data.
A B C D E F G
1 REG HCC ITC SCC
2
3 ID Code Hrs
4 1 REG 57 57 22
5 ITC 22
6
7 2 REG 62 62 10 27 5
8 ITC 27
9 HCC 10
10 SCC 5
11
12 3 REG 61 61 14
13 HCC 14
of the pivot table. I figured out how to do most of what I need to do,
except I can't get it to show in the row I want it in. I have included a
sample of how this should look--I hope the formatting goes through correctly.
The pivot table has 3 columns: ID, code, hours. Each employee (ID) can
have anywhere from 1-5 rows of hours, broken down by pay code. I want to
take these values and copy them into columns that are not within the pivot
table, and each column is also broken down by pay code
I figured out that I can copy the values into the appropriate column, but
only in the same row it is in within the pivot table. However, I want to the
value to be moved up to the first row for each employee. As you can see,
each employee has a different number of rows, and the values may not be in
the same relative row for each employee. Also, as you can see, not every
column will have a value for every employee
In the example, look at employee 1: had 57 reg hours, 22 ITC hours. I want
these hours to be copied to the first row for that employee (row 4), in the
appropriate column. So "57" would go in cell D4; "22" would go in cell F4
I used a formula throughout each column to copy the values over, but it does
it in the same row. (for example: in cell F5 I have: =IF(B5="ITC",C5,"").
That will put "22" into cell F5, but I want it to go up to row 4. This
becomes more complicated in that each employee has a different number of
rows, and the different codes are not in the same "relative row" for each
employee (ie. ITC is not always the 2nd row for each employee)
Any suggestions?
Columns A-C contain the pivot table; Columns D and on are where I want to
copy the data.
A B C D E F G
1 REG HCC ITC SCC
2
3 ID Code Hrs
4 1 REG 57 57 22
5 ITC 22
6
7 2 REG 62 62 10 27 5
8 ITC 27
9 HCC 10
10 SCC 5
11
12 3 REG 61 61 14
13 HCC 14