Reformatting/Deduping Data to Long Form

[rwille]

Hello,

I'm having trouble reformatting data like how it is shown to the right in my screen shot.

The row data from a1:c10 I have is in alphabetical order by order id, from oldest transaction to newest for each respective account ID. With different transaction information as columns. I would like to reformat the data into a longer form as shown in f1:n4

Any idea the best way to approach this?

Thanks

 

[Saba Sabaratnam]

Hi

You could use filter function to solve this problem.

I have the above data and solution (on right side)

Enter the following formula in D2

=SORT(UNIQUE(A2:A8))

The following formula in E2 and copy it down

=TRANSPOSE(SORTBY(FILTER($C$2:$C$8,$A$2:$A$8=D2),FILTER($B$2:$B$8,$A$2:$A$8=D2)))

Kind regards

Saba

 

[awoohaw]

here is another take on it:

mr excel questions 12.xlsm
	A	B	C	D	E	F	G	H	I
1	ID	Paydate	Type		ID	1ST	2ND	3RD	4TH
2	AA1100	2019-08-28	H		AA1100	H	H	B
3	AA1100	2020-08-28	H		AAC926	H	H
4	AA1100	2021-08-28	B		ABR765	V	H	H	B
5	AAC926	2017-08-28	H
6	AAC926	2018-08-28	H
7	ABR765	2016-08-17	V
8	ABR765	2020-08-28	H
9	ABR765	2021-08-28	H
10	ABR765	2022-08-28	B
Sheet7

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(A2:A10)
F2:I4	F2	=IFERROR(INDEX($C$2:$C$10,MATCH($E2&(1/LARGE((IFERROR(1/((--($E2=$A$2:$A$10))*($B$2:$B$10)),0)), COLUMNS($E$2:E$2))),$A$2:$A$10&$B$2:$B$10,0)),"")
Dynamic array formulas.

 

[Fluff]

You could use filter function to solve this problem.

Not when the OP is using 2019, as that doesn't have most of the functions you've used.