...

In Excel, combine multiple detail data columns into one row in each group

 

The following Excel table has a grouping column and two detail data columns.

A

B

C

1

Object

Name

Info

2

12A

Gggtz

44456

3

12A

Gggtr

99987

4

12A

Kkkio

66543

5

12A

Bbvgf

66643

6

12A

Vvvhg

888765

7

12A

Fffgt

87

8

6F

Gggtf

232325

9

6F

Xxxde

443216

10

P23

Cccvb

5543287

11

P23

Vvvbj

5656564

12

P23

Sswec

8989764

13

P23

Llloiu

343432

We need to combine the two detail data columns in each group into one row and automatically generate columns headers for the new columns.


E

F

G

H

I

G

K

L

M

N

O

P

Q

1

Object

Name1

Info1

Name2

Info2

Name3

Info3

Name4

Info4

Name5

Info5

Name6

Info6

2

12A

Gggtz

44456

Gggtr

99987

Kkkio

66543

Bbvgf

66643

Vvvhg

888765

Fffgt

87

3

6F

Gggtf

232325

Xxxde

443216







4

P23

Cccvb

5543287

Vvvbj

5656564

Sswec

8989764

Llloiu

343432



Use SPL XLL to type in the following formula:

=spl("=d=E(?).group@o(Object).(Object|(~.conj([Name,Info]))), [$[Object]|(d.max(~.len())\2).conj([$[Name] / #,$[Info] / #])] | d",A1:C13)

Picture2png

E()function reads data from the Excel table. group@o groups rows without a prior sorting. $[] represents a string, ~ is the current member, and # is ordinal number of the current member.

Source:https://www.reddit.com/r/excel/comments/1d5ntg2/how_to_copy_data_from_lines_to_rows_but_keep_it/


 
 •  125 View  •