Share via

Append Rows in excel using Index Numbers

Anonymous
2023-04-19T18:02:48+00:00

Hello there!

I'm having a challenge with getting some work done on Microsoft Excel.

I have an attendance table with lots of data. It has 5256 rows but they belong to only 1882 people; each with an index number (1 - 1882).

For example, index 4 has 5 instances (rows). I would like the 5 rows to be joined into a single row (placing the 5 instances in a single file) without the values getting merged in one cell.

So, in the end, I should end up with only 1882 rows.

Thanks

Microsoft 365 and Office | Excel | For education | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

11 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-04-24T14:05:35+00:00

    You have more rows than what you thought because your have 2,118 unique values in your column _parent_index

    =COUNT(UNIQUE(CO:CO))-1

    Then I used this formula in A2 of a blank sheet

    =SEQUENCE(2118)

    And this formula in cell B2, copied dow:

    =TOROW(FILTER(Child_attendance!A:CL,Child_attendance!CO:CO=A2))

    But then I changed to this to show blanks instead of 0 when the cells are blank:

    =TOROW(IF(FILTER(Child_attendance!A:CL,Child_attendance!CO:CO=A2)="","",FILTER(Child_attendance!A:CL,Child_attendance!CO:CO=A2)))

    Then I just copied the 90 headers across the top for 90x13 = 1170 columns, and added the date format to the columns of dates.

    0 comments