Transpose, Table, or Formulas

[LearnVBA83]

Hi team excel,

I have a large excel spreadsheet with GL Accounts and the ID of people that post to those accounts. I'm building like an account mapping legend and was wondering if there is an easy way to turn the data in columns A & B to the format of columns F - K ? Maybe using a table, pivot, or transpose?

Line Items (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	G/L Account	Parked By				G/L Account	Name 1	Name 2	Name 3	Name4	Name 5
2	1001002	C396230				1001002	C396230	C393743	SYSTEM	DSWHITE
3	1001002	C393743				1001004	C754095	SYSTEM	C210747	C027040	C577891
4	1001002	SYSTEM				1001502	C396230	C393743	SYSTEM
5	1001002	DSWHITE				1001503	C396230	SYSTEM
6	1001004	C754095				1001504	C396230	SYSTEM
7	1001004	SYSTEM				1001505	C396230	SYSTEM
8	1001004	C210747				1001507	C396230	C393743	SYSTEM
9	1001004	C027040				1001508	C396230	SYSTEM
10	1001004	C577891				1001510	DPETRUCCI	C393743	C396230	C577891	SYSTEM
11	1001502	C396230
12	1001502	C393743
13	1001502	SYSTEM
14	1001503	C396230
15	1001503	SYSTEM
16	1001504	C396230
17	1001504	SYSTEM
18	1001505	C396230
19	1001505	SYSTEM
20	1001507	C396230
21	1001507	C393743
22	1001507	SYSTEM
23	1001508	C396230
24	1001508	SYSTEM
25	1001510	DPETRUCCI
26	1001510	C393743
27	1001510	C396230
28	1001510	C577891
29	1001510	SYSTEM
30
Sheet3

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	G/L Account	Parked By				G/L Account	Name 1	Name 2	Name 3	Name4	Name 5
2	1001002	C396230				1001002	C396230	C393743	SYSTEM	DSWHITE
3	1001002	C393743				1001004	C754095	SYSTEM	C210747	C027040	C577891
4	1001002	SYSTEM				1001502	C396230	C393743	SYSTEM
5	1001002	DSWHITE				1001503	C396230	SYSTEM
6	1001004	C754095				1001504	C396230	SYSTEM
7	1001004	SYSTEM				1001505	C396230	SYSTEM
8	1001004	C210747				1001507	C396230	C393743	SYSTEM
9	1001004	C027040				1001508	C396230	SYSTEM
10	1001004	C577891				1001510	DPETRUCCI	C393743	C396230	C577891	SYSTEM
11	1001502	C396230
12	1001502	C393743
13	1001502	SYSTEM
14	1001503	C396230
15	1001503	SYSTEM
16	1001504	C396230
17	1001504	SYSTEM
18	1001505	C396230
19	1001505	SYSTEM
20	1001507	C396230
21	1001507	C393743
22	1001507	SYSTEM
23	1001508	C396230
24	1001508	SYSTEM
25	1001510	DPETRUCCI
26	1001510	C393743
27	1001510	C396230
28	1001510	C577891
29	1001510	SYSTEM
Data

Cell Formulas
Range	Formula
F2:K10	F2	=LET(g,DROP(GROUPBY(A2:A100,B2:B100,HSTACK(LAMBDA(v,"|"&TEXTJOIN("|",,v)),COUNTA),0,0,,A2:A100<>""),1),c,TAKE(g,,-1),m,MAX(c),HSTACK(TAKE(g,,1),TEXTBEFORE(TEXTAFTER(INDEX(g,,2)&REPT("|",m-c+1),"|",SEQUENCE(,m)),"|")))
Dynamic array formulas.

 

[kweaver]

As usual, Fluff's solution is a BEAUTY TO BEHOLD.

I think this also works:

F2: =UNIQUE(A2:.A100)
G2: =TRANSPOSE(FILTER($B$2:.$B$100,$A$2:.$A$100=F2)) filled down

 

[LearnVBA83]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	G/L Account	Parked By				G/L Account	Name 1	Name 2	Name 3	Name4	Name 5
2	1001002	C396230				1001002	C396230	C393743	SYSTEM	DSWHITE
3	1001002	C393743				1001004	C754095	SYSTEM	C210747	C027040	C577891
4	1001002	SYSTEM				1001502	C396230	C393743	SYSTEM
5	1001002	DSWHITE				1001503	C396230	SYSTEM
6	1001004	C754095				1001504	C396230	SYSTEM
7	1001004	SYSTEM				1001505	C396230	SYSTEM
8	1001004	C210747				1001507	C396230	C393743	SYSTEM
9	1001004	C027040				1001508	C396230	SYSTEM
10	1001004	C577891				1001510	DPETRUCCI	C393743	C396230	C577891	SYSTEM
11	1001502	C396230
12	1001502	C393743
13	1001502	SYSTEM
14	1001503	C396230
15	1001503	SYSTEM
16	1001504	C396230
17	1001504	SYSTEM
18	1001505	C396230
19	1001505	SYSTEM
20	1001507	C396230
21	1001507	C393743
22	1001507	SYSTEM
23	1001508	C396230
24	1001508	SYSTEM
25	1001510	DPETRUCCI
26	1001510	C393743
27	1001510	C396230
28	1001510	C577891
29	1001510	SYSTEM
Data

Cell Formulas
Range	Formula
F2:K10	F2	=LET(g,DROP(GROUPBY(A2:A100,B2:B100,HSTACK(LAMBDA(v,"|"&TEXTJOIN("|",,v)),COUNTA),0,0,,A2:A100<>""),1),c,TAKE(g,,-1),m,MAX(c),HSTACK(TAKE(g,,1),TEXTBEFORE(TEXTAFTER(INDEX(g,,2)&REPT("|",m-c+1),"|",SEQUENCE(,m)),"|")))
Dynamic array formulas.

Thank you for the quick reply! I pasted the formula in cell F2 but how are you declaring F2:K10 as the dynamic range? I'm getting an error even when i try the control-alt-enter

 

[Fluff]

What error do you get?

 

[LearnVBA83]

As usual, Fluff's solution is a BEAUTY TO BEHOLD.

I think this also works:

F2: =UNIQUE(A2:.A100)
G2: =TRANSPOSE(FILTER($B$2:.$B$100,$A$2:.$A$100=F2)) filled down

Thank you... I'm getting an error in the second formula. Should there be a period after the colon or should it be $B$2:$B$100 ?

 

[kweaver]

The "period" is one of the new features in Excel 365 (dot operator). It's possible you don't have that yet.
You can take that out and change the UNIQUE formula in F2 to: =UNIQUE(A2:A29) and modify the 29 accordingly based on your length of data in column A.

 

[Fluff]

Once again what error?
Saying "I get an error" doesn't really help us.

 

[LearnVBA83]

What error do you get?

I highlighted F2:K10 and tried paste. If I paste it in F2 and hit control alt enter i get $NAME# in cell F2.

 

[Fluff]

With 365 you never need to use Ctrl Shift Enter.
Just paste the formula into F2 only.