Page numbering in Excel with formula

[darzu]

I need a solution in Excel, i want to make an indexing page (a sort of content page / summary with page numbering) i want to use a formula what will search for a specific word eg. (OEM A & OEM B) and will make a summary page as i attached in the picture.

 

[jasonb75]

Here's how I would do it. The page numbers in column B are just numbers, 1 to 5 with a custom cell format of "Page "0

Book1
	A	B
1	OEM A	Page 1
2	OEM A
3	OEM A
4	OEM A
5	OEM A
6	OEM B	Page 2
7	OEM B
8	OEM B
9	OEM B
10	OEM B
11	OEM B	Page 3
12	OEM B
13	OEM B
14	OEM B
15	OEM B
16	OEM B	Page 4
17	OEM B
18	OEM B
19	OEM B
20	OEM B
21	OEM B	Page 5
22	OEM B
23	OEM B
24	OEM B
25	OEM B
26
27
28	OEM A	Page 1 / Page 1
29	OEM B	Page 2 / Page 5
Sheet8

Cell Formulas
Range	Formula
B28:B29	B28	="Page "&MINIFS($B$1:$B$25,$A$1:$A$25,A28)&" / Page "&MAXIFS($B$1:$B$25,$A$1:$A$25,A28)

 

[darzu]

Hello,

unfortunately i am using office 2016 and i don't got the MINIFS function and on my business computer we are using only 2016, sorry that i didn't specified this, also i would like to specify an important statement the column B it is not existing the Page numbering, i created the Page numbering only to demonstrate the page separations.

Thanks

 

[jasonb75]

Your example shows 5 identical items per page, will your actual file have a similar layout or could the number of lines per page vary?
Will it always be whole pages of items, or could they be mixed? If mixed, how should that be reflected in the page numbers?
Will column A always be sorted in ascending order as per the example?

 

[darzu]

Hello,
there is not a standard how many rows will be per page (it is defined based on the print area) the number per page may vary.
Yes can be on the same page 2 different item and on the summary page it should show me exact the same thing the 2 items starts and ends on the same page

I tried to create an example on the last statement:

OEM A	Page 1
OEM A
OEM A
OEM B
OEM B
OEM A	Page 1 - Page 1
OEM B	Page 1 - Page 1

 

[jasonb75]

If print area is the only known factor for the start and end of each page then, to the best of my knowledge, what you are asking will not be possible with a formula.

 

[darzu]

If print area is the only known factor for the start and end of each page then, to the best of my knowledge, what you are asking will not be possible with a formula.

Ok in understand, lets say i make an aditional column and complete on each row the page numbering it should work in this way?

OEM A	1
OEM A	1
OEM A	1
OEM B	1
OEM B	1
OEM A	Page 1 - Page 1
OEM B	Page 1 - Page 1

 

[jasonb75]

Yes, that would work as long as you do each row as in your last example, with the rows merged as in the screen capture in post 1 would not work).

Will column A always be sorted in ascending order? If it will then you can use this method, for descending or mixed order some changes will be needed.

Book1
	A	B
1	OEM A	1
2	OEM A	1
3	OEM A	1
4	OEM A	1
5	OEM A	1
6	OEM B	2
7	OEM B	2
8	OEM B	2
9	OEM B	2
10	OEM B	2
11	OEM B	3
12	OEM B	3
13	OEM B	3
14	OEM B	3
15	OEM B	3
16	OEM B	4
17	OEM B	4
18	OEM B	4
19	OEM B	4
20	OEM B	4
21	OEM B	5
22	OEM B	5
23	OEM B	5
24	OEM C	5
25	OEM C	5
26
27
28	OEM A	Page 1 - Page 1
29	OEM B	Page 2 - Page 5
30	OEM C	Page 5 - Page 5
Sheet8

Cell Formulas
Range	Formula
B28:B30	B28	="Page "&INDEX($B$1:$B$25,MATCH(A28,$A$1:$A$25,0))&" - Page "&INDEX($B$1:$B$25,MATCH(A28,$A$1:$A$25,1))

 

[darzu]

Well the OEM name is not sortable automatically but it should be grouped manually by my colleagues.

I will try to implement the above solution and will get back with feedback.

Thanks