Append Rows in excel using Index Numbers

Question

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

Answer 1

After you have finished your query and downloaded the table, you can create a series of indices using

=SEQUENCE(1882)

and then use a formula like this (where BB2 is the cell with the SEQUENCE function)

=TOROW(FILTER(Table1[[Header First]:[Header Last]],Table1[_parent_index]=BB2))

Then copy down for 1881 rows....

Answer 2

Hi,

Your question is confusing. There are 5 rows because the Week ending date is different. Even if you want to create one row for ID 4, which week 1 ending date would you want to show?

Answer 3

Hi there

I'm afraid the Query steps chosen might not be the correct ones for your scenario and goals.

For the solution, a different approach might be needed.

Please,

Provide us with a downloadable version of the Source table, the one uploaded to Power Query without any transformation.

Regards

Jeovany

Answer 4

Hello, thanks for the response.

I guess I probably didn't get the Query steps right as I've been a basic Excel user and never had to do anything complex till now.

A brief explanation:

The Excel file is from Kobocollect database which was populated via a form used by enumerators to extract information (Female students' attendance) from schools. On the attendance sheet of the file, each row represents a term (15 weeks).

Some students have spent 5 terms while others only 2. After using Power Query to merge my first (school information) and second (student information) sheets, I have a new merged sheet with 1882 students and all relevant information from the two sheets.

My problem is with the third (attendance) sheet.

For example, student 1 attended for two terms and her index number is 1 (out of 1882) which appeared on the first two rows of the third sheet. After I follow the same procedure to link and merge tables, instead of having a single row like this: [student 1] [first term attendance] [second term attendance], I get two different rows like this:

[student 1] [first term attendance]

[student 1] [second term attendance]

This means that I end up with 5256 rows instead of 1882.

I need the three sheets to be merged into one and the number of rows to be the number of students (1882) without losing any information so it can be used for proper analysis and presentation.

Link to the file as requested: https://docs.google.com/spreadsheets/d/1hwGxUmBP1l-xae47wIbdsLivqBp6qm4u/edit?usp=drivesdk&ouid=100469078643960598719&rtpof=true&sd=true

Thank you

Answer 5

I am sorry about that. I made a more explicit explanation to Jeovany below. you may wish to check it out.

Thanks