Forum Discussion
Getting data in same row if conditions are met
Hi,
I hope someone can send me in the right direction.
This dataset contains the answers to a form with two answerfields. The 'pseudo_id' is the unique identificationnumber of the subject. 'start_date_check' is the time when the form is opened. 'check_entry_question' are the two questions that are asked, 'check_entry_answer' are the given answers.
What I would like to do is combine the rows that contains the same 'pseudo_id' and the same 'start_date_check'.
I would like to have all the data regarding one questionform in a single row, so I can make some analysis on this data.
It is possible that the 'pseudo_id' is multiple time's in the dataset (when a answerform is filled out more than one time)
| pseudo_id | start_date_check | check_entry_question | check_entry_answer | check_entry start_date |
| 14 | 44814,92 | Check 1 | Dave | 44819,92 |
| 14 | 44814,92 | Check 2 | Dave | 44819,92 |
| 17 | 44813,35 | Check 1 | Ralph | 44817,35 |
| 17 | 44813,35 | Check 2 | 44817,35 | |
| 18 | 44816,75 | Check 1 | Lauren | 44818,12 |
| 18 | 44816,75 | Check 2 | Vera | 44818,12 |
Is it possible to do something like this in Excel? If not, is there another way to do this?
mrstefan If you've never worked with PQ before, it's going to need a bit more than "little explaining". but the basic steps are to flatten the data (i.e., unpivot) and then Group and Split. It's not difficult but not easy to explain. Then perhaps Patrick2788 's formula-based solution is more suitable for you, though don't give up on PQ. Learn more about it here:
https://exceloffthegrid.com/power-query-introduction/
7 Replies
If you have 365, you could do a bit of stacking with a formula:
=LET(header,EXPAND(D1:H1,1,8,""),ID_Start,D2:E7,data,F2:H7,VSTACK(header,HSTACK(UNIQUE(ID_Start),WRAPROWS(TOCOL(data),6))))- Great help! I will try this too! Thanks!
- You're welcome. Glad it all worked out for you.
Yes this is how I imagined a solution! Can you explain it a little more?
mrstefan If you've never worked with PQ before, it's going to need a bit more than "little explaining". but the basic steps are to flatten the data (i.e., unpivot) and then Group and Split. It's not difficult but not easy to explain. Then perhaps Patrick2788 's formula-based solution is more suitable for you, though don't give up on PQ. Learn more about it here:
https://exceloffthegrid.com/power-query-introduction/
