building a book index

[yky]

I have a sorted list with names in column A and page numbers in column B. I'd like to group the page numbers for the same person in the same cell. A person may have up to six pages. Any non-VBA method of doing it?

		desired result
Jill	24	24
Joe	17	17
John	52	52, 99
John	99
Steve	50	50, 66
Steve	66
Tom	51	51, 88, 94
Tom	88
Tom	94

 

[sandy666]

Name	Page		Name	Index
Jill	24		Jill	24
Joe	17		Joe	17
John	52		John	52, 99
John	99		Steve	50, 66
Steve	50		Tom	51, 88, 94
Steve	66
Tom	51
Tom	88
Tom	94

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Index", each [Count][Page]),
    Extract = Table.TransformColumns(List, {"Index", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    Extract

Power Query add-in for XL2010/2013

 

[Toadstool]

Hi YKY,

Does this do it for you?

YKY.xlsx
	A	B	C	D	E	F	G	H
1	Name	Page	P2	P3	P4	P5	P6	Result
2	Jill	24						24
3	Joe	17						17
4	John	52	, 99					52, 99
5	John	99
6	Steve	50	, 66					50, 66
7	Steve	66
8	Tom	51	, 88	, 94				51, 88, 94
9	Tom	88
10	Tom	94
Sheet1

Cell Formulas
Range	Formula
C2:G10	C2	=IFERROR(IF(COUNTIF($A$2:$A2,$A2)<>1,"",", "&INDEX($B$2:$B$9999,AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/($A$2:$A$9999=$A2),COLUMN()-COLUMN($A:$AA)))),"")
H2:H10	H2	=IF(COUNTIF($A$2:$A2,$A2)<>1,"",B2&C2&D2&E2&F2&G2)

 

[yky]

Name	Page		Name	Index
Jill	24		Jill	24
Joe	17		Joe	17
John	52		John	52, 99
John	99		Steve	50, 66
Steve	50		Tom	51, 88, 94
Steve	66
Tom	51
Tom	88
Tom	94

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Index", each [Count][Page]),
    Extract = Table.TransformColumns(List, {"Index", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    Extract

Power Query add-in for XL2010/2013

Thank you. Power query has been on my to-learn list for quite a while. It has now become the top priority.

 

[yky]

Hi YKY,

Does this do it for you?

YKY.xlsx
	A	B	C	D	E	F	G	H
1	Name	Page	P2	P3	P4	P5	P6	Result
2	Jill	24						24
3	Joe	17						17
4	John	52	, 99					52, 99
5	John	99
6	Steve	50	, 66					50, 66
7	Steve	66
8	Tom	51	, 88	, 94				51, 88, 94
9	Tom	88
10	Tom	94
Sheet1

Cell Formulas
Range	Formula
C2:G10	C2	=IFERROR(IF(COUNTIF($A$2:$A2,$A2)<>1,"",", "&INDEX($B$2:$B$9999,AGGREGATE(15,6,ROW($A$2:$A$9999)-ROW($A$1)/($A$2:$A$9999=$A2),COLUMN()-COLUMN($A:$AA)))),"")
H2:H10	H2	=IF(COUNTIF($A$2:$A2,$A2)<>1,"",B2&C2&D2&E2&F2&G2)

Thank you. Works like a charm.

 

[sandy666]

You are welcome
Have a nice day