As hidden data for a series of rows in a column, based on certain conditions

Question

I have an excel that has exam data (Theory, Sessional, Practical, etc.). I have to put the same QPCODE-based data on one line.
My details are

 +-------+--------+--------------------+--------------+---------------------------+--------------------+--------------------------+---------------------+-----------+-----------+
    |  id   | qpcode | subject_paper_code | subject_code |       subject_name        | subject_paper_name | subject_paper_short_code | subject_paper_group | min_marks | max_marks |
    +-------+--------+--------------------+--------------+---------------------------+--------------------+--------------------------+---------------------+-----------+-----------+
    | 37790 |  10032 |                  0 | A47          | GEOGRAPHY                 | THEORY             | GEOG1                    | A                   |        21 |        60 |
    | 37791 |        |                  1 | A47          | GEOGRAPHY                 | I.A.(THEORY)       | GE1IA                    | A                   |         0 |        10 |
    | 37792 |        |                  2 | A47          | GEOGRAPHY                 | PRACTICAL          | GE1PR                    | B                   |         9 |        20 |
    | 37793 |        |                  3 | A47          | GEOGRAPHY                 | RECORD             | GE1RC                    | B                   |         0 |        10 |
    | 37794 |  10033 |                  0 | A50          | HINDI (OPT)               | THEORY             | HINO1                    | A                   |        40 |        80 |
    | 37795 |        |                  1 | A50          | HINDI (OPT)               | I.A.(THEORY)       | HI1IA                    | A                   |         0 |        20 |
    | 37796 |  10034 |                  0 | A51          | HISTORY(PRIOR TO 2008-09) | THEORY             | HIST1                    | A                   |        40 |        80 |
    +-------+--------+--------------------+--------------+---------------------------+--------------------+--------------------------+---------------------+-----------+-----------+

I have to put the same qpcode data on the same line so that I can get the same qpcode data on the same line for all qpcodes.

+-------+--------+--------------------+--------------+--------------+--------------------+--------------------------+---------------------+-----------+-----------+---------------------------+------------+------------+---------------------------+------------+------------+---------------------------+------------+------------+
|  id   | qpcode | subject_paper_code | subject_code | subject_name | subject_paper_name | subject_paper_short_code | subject_paper_group | min_marks | max_marks | subject_paper_short_code2 | min_marks2 | max_marks2 | subject_paper_short_code3 | min_marks3 | max_marks3 | subject_paper_short_code4 | min_marks4 | max_marks4 |
+-------+--------+--------------------+--------------+--------------+--------------------+--------------------------+---------------------+-----------+-----------+---------------------------+------------+------------+---------------------------+------------+------------+---------------------------+------------+------------+
| 37790 |  10032 |                  0 | A47          | GEOGRAPHY    | THEORY             | GEOG1                    | A                   |        21 |        60 | GE1IA                     |          0 |         10 | GE1PR                     |          9 |         20 | GE1RC                     |          0 |         10 |
+-------+--------+--------------------+--------------+--------------+--------------------+--------------------------+---------------------+-----------+-----------+---------------------------+------------+------------+---------------------------+------------+------------+---------------------------+------------+------------+

Answer 1

Using VBA for this is easier. But I wanted to see if this could be done using PivotTable. So, here are the four steps that I have completed.

Step 1: Data Cleanup

• Copy your data to a new sheet.

• There are several unnecessary columns. Delete them:

  • ID
  • subject_paper_code
  • subject_code
  • subject_name
  • subject_paper_name
  • subject_paper_group

- , , . , , VLOOKUP, .

. , A:D.

3. , qpcode, .

A ( qpcode), A2 :

=IF(ISBLANK(B2),A1,B2)

qpcodes .

4. . qpcode ( B), .

:

╔════════╦══════════════════════════╦═══════════╦═══════════╗
║ qpcode ║ subject_paper_short_code ║ min_marks ║ max_marks ║
╠════════╬══════════════════════════╬═══════════╬═══════════╣
║  10032 ║ GEOG1                    ║        21 ║        60 ║
║  10032 ║ GE1IA                    ║         0 ║        10 ║
║  10032 ║ GE1PR                    ║         9 ║        20 ║
║  10032 ║ GE1RC                    ║         0 ║        10 ║
║  10033 ║ HINO1                    ║        40 ║        80 ║
║  10033 ║ HI1IA                    ║         0 ║        20 ║
║  10034 ║ HIST1                    ║        40 ║        80 ║
╚════════╩══════════════════════════╩═══════════╩═══════════╝

2.

• qpcode

• ""

  • subject_paper_short_code
  • min_marks
  • max_marks

• , ""

  • ; " "
  • None Subtotals & Filters
  • Layout & Print Repeat item labels

. . (max_marks) .

5. "" ( , "" ):

   • subject_paper_short_code
   • min_marks
   • max_marks

6. "Count" " ". , .

3:

• , , " " " "

• . , :

.

3. B10 ( B10:V12):

=IF(NOT(ISBLANK(B5)),INDIRECT(ADDRESS(MOD(COLUMN()-1,3)+3*(MOD(COLUMN()-1,3)=0),COLUMN())))

.

4. ( ).

5. Ctrl + H ( ) FALSE .

6. F5 Goto Special. Blank cells Enter. .

7. Ctrl + - Shift cells left.

4:

• "Count of", . Count of ( ) .

• Row Label qpcode.

• . .

. , .

, :

╔════════╦══════════════════════════╦═══════════╦═══════════╦══════════════════════════╦═══════════╦═══════════╦══════════════════════════╦═══════════╦═══════════╦══════════════════════════╦═══════════╦═══════════╗
║ qpcode ║ subject_paper_short_code ║ min_marks ║ max_marks ║ subject_paper_short_code ║ min_marks ║ max_marks ║ subject_paper_short_code ║ min_marks ║ max_marks ║ subject_paper_short_code ║ min_marks ║ max_marks ║
╠════════╬══════════════════════════╬═══════════╬═══════════╬══════════════════════════╬═══════════╬═══════════╬══════════════════════════╬═══════════╬═══════════╬══════════════════════════╬═══════════╬═══════════╣
║  10032 ║ GE1IA                    ║         0 ║        10 ║ GE1PR                    ║         9 ║        20 ║ GE1RC                    ║         0 ║        10 ║ GEOG1                    ║        21 ║        60 ║
║  10033 ║ HI1IA                    ║         0 ║        20 ║ HINO1                    ║        40 ║        80 ║                          ║           ║           ║                          ║           ║           ║
║  10034 ║ HIST1                    ║        40 ║        80 ║                          ║           ║           ║                          ║           ║           ║                          ║           ║           ║
╚════════╩══════════════════════════╩═══════════╩═══════════╩══════════════════════════╩═══════════╩═══════════╩══════════════════════════╩═══════════╩═══════════╩══════════════════════════╩═══════════╩═══════════╝