1

I have a table that I need to pull some data out and I'm not too sure on how to do this.

Here is an example of the table.

|   A    +   B    +       C       +   D   +   E    +   F    |
|--------+--------+---------------+-------+--------+--------|
| FNAME  | LNAME  | FULLNAME      | GRADE | EFFORT | CODE   |
|--------+--------+---------------+-------+--------+--------|
| FNAME1 | LNAME1 | FNAME1 LNAME1 | A     |        | GRADE  |
|--------+--------+---------------+-------+--------+--------|
| FNAME1 | LNAME1 | FNAME1 LNAME1 | H     |        | EFFORT |
|--------+--------+---------------+-------+--------+--------|
| FNAME2 | LNAME2 | FNAME2 LNAME2 | C     |        | GRADE  |
|--------+--------+---------------+-------+--------+--------|

What I need is to move the effort grade to the effort column, but in a different row. Example Below.

|   A   +   B   +     C     +   D   +   E    +   F    |
|-------+-------+-----------+-------+--------+--------|
| FNAME | LNAME | FULLNAME  | GRADE | EFFORT | CODE   |
|-------+-------+-----------+-------+--------+--------|
| NAME1 | NAME1 | NAMENAME1 | A     |        | GRADE  | ←      To column E here         ←
|-------+-------+-----------+-------+--------+--------|                                 ↑
| NAME1 | NAME1 | NAMENAME1 | H     |        | EFFORT | → Move this Grade from column D ↑
|-------+-------+-----------+-------+--------+--------|

So an example of what I want...

|   A    +   B    +       C       +   D   +   E    +   F    |
|--------+--------+---------------+-------+--------+--------|
| FNAME  | LNAME  | FULLNAME      | GRADE | EFFORT | CODE   |
|--------+--------+---------------+-------+--------+--------|
| FNAME1 | LNAME1 | FNAME1 LNAME1 | A     | H      | GRADE  |
|--------+--------+---------------+-------+--------+--------|
| FNAME2 | LNAME2 | FNAME2 LNAME2 | C     |        | GRADE  |
|--------+--------+---------------+-------+--------+--------|

The kicker is, not all students have an effort grade, and if they don't have one there won't be a row for it. Also, sometimes there will be an extra row with comments etc... so the data is not constant.

  • I believe you're looking for a pivot table. gcflearnfree.org/excel2013/pivottables/2 – JustAGrump May 26 '17 at 2:29
  • Please explain your situation more clearly.  Specifically, please show an example input and what output you want to get. – Scott May 26 '17 at 2:42
  • Sorry @Scott, I have added an example of the final result above. – Boneless117 May 26 '17 at 3:09
  • Still kinda vague, but the pivot table seems to be what you want. – JustAGrump May 26 '17 at 3:37
0

In a new sheet, copy the headers (A1:F1) and enter a 1 in a helper column.  I’ll assume that you’re using Column G; i.e., cell G1.  Enter

=IF(INDEX(Sheet1!A:A, $G2)<>"", INDEX(Sheet1!A:A, $G2), "")

into A2, and drag/fill to the right, to F2.  Then change E2 to

=IF(INDEX(Sheet1!F:F, $G2+1)="EFFORT", INDEX(Sheet1!D:D, $G2+1), "")

and enter

=IF(INDEX(Sheet1!F:F, $G1+1)="GRADE", $G1+1, $G1+2)

into G2.  Select A2:G2 and drag/fill down as far as necessary.

Then hide Column G, if you want.

Column G is the index (on the new sheet) of the row on Sheet1 that we’re (primarily) drawing data from.  It is one more than the index from the previous line unless that points to row whose CODE is GRADE, in which case we assume that it’s an EFFORT row and add two to the previous (i.e., to skip over the EFFORT row).  Columns A-D and F are simply copied from the same column on Sheet1, as indexed by Column G. The formula in Column E (EFFORT) looks to see whether the current student is one with two rows (so $G2+1 refers to the same student, with a CODE of EFFORT) and, if so, pulls the EFFORT grade from Column D of that second row.

For these data:

+   A   +   B    +      C       +   D   +   E    +   F    +
+-------+--------+--------------+-------+--------+--------+
| FNAME | LNAME  |   FULLNAME   | GRADE | EFFORT |  CODE  |
+-------+--------+--------------+-------+--------+--------+
| John  | Smith  | John Smith   | A     |        | GRADE  |
| John  | Smith  | John Smith   | H     |        | EFFORT |
| Mary  | Jones  | Mary Jones   | B     |        | GRADE  |
| Isaac | Newton | Isaac Newton | A+    |        | GRADE  |
| Isaac | Newton | Isaac Newton | C     |        | EFFORT |
+-------+--------+--------------+-------+--------+--------+

I got this result:

spreadsheet

0

You can handle that without pivot.

Based on your example, put this on your Effort column on Sheet2:

=INDEX(Sheet1!$D$2:$D$10, MATCH(1, (Sheet2!$C2 = Sheet1!$C$2:$C$10) * (Sheet1!$F$2:$F$10 = "EFFORT"), 0))

and press Ctrl+Shift+Enter to insert the array formula.

Explanation

Sheet1!$D$2:$D$10 is your Effort column on your source table on Sheet1.

(Sheet2!$C2 = Sheet1!$C$2:$C$10) is looking up the matching FULLNAME

(Sheet1!$F$2:$F$10 = "EFFORT") is looking up the matching CODE, which is "EFFORT"

Both return boolean value, 0 and 1. If both conditions match (found a row with matching FULLNAME and CODE), then it returns 1 ( 1 * 1 ) to the MATCH function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.