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

 

[LearnVBA83]

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

I just pasted the formula in F2 and then highlighted F2:K10 and then hit control - alt - enter and it gave me this.

 

[Fluff]

Just put it in F2 ONLY and do NOT use Ctrl Shift Enter.

 

[LearnVBA83]

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

I must not have 365 or maybe my company hasn't fully updated it because the "period" dot operator KWEAVER mentioned above doesn't work for me either. When i paste your formula in F2 I get the below return.

 

[Fluff]

Ok, you probably don't have the Groupby function yet. What error did you get with kweaver's formula?

 

[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(u,UNIQUE(FILTER(A2:A100,A2:A100<>"")),m,MAX(COUNTIFS(A:A,u)),DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,HSTACK(y,EXPAND(TOROW(FILTER(B2:B100,A2:A100=y)),,m,""))))),1))
Dynamic array formulas.

 

[LearnVBA83]

Ok, you probably don't have the Groupby function yet. What error did you get with kweaver's formula?

His seemed to work when i took out the periods but I love how yours is one formula.

 

[Fluff]

His seemed to work when i took out the periods

But what was the actual error?

 

[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(u,UNIQUE(FILTER(A2:A100,A2:A100<>"")),m,MAX(COUNTIFS(A:A,u)),DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,HSTACK(y,EXPAND(TOROW(FILTER(B2:B100,A2:A100=y)),,m,""))))),1))
Dynamic array formulas.

Looks like you got it! WOW amazing.

 

[LearnVBA83]

But what was the actual error?

With the :. it wouldn't even recognize it as a formula until i took out the period. I must have an older version of excel.

 

[Fluff]

With the :. it wouldn't even recognize it as a formula

Thanks for that.

Glad we could help & thanks for the feedback.