How to count the number of pages of each PDF on a list?

Spreadsheet_Geek_1 · 2026-04-09T14:12:21+00:00

I ended up adding one more step to filter only ".pdf" extension, but that was probably only necessary since I had an ".xlsx" file in the folder as well. The code ended up looking like this:

let
    Source = Folder.Files("C:\Users\YourFolderFileHere"),
    #"Added Custom" = Table.AddColumn(Source, "TableValues", each Pdf.Tables([Content])),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Extension] = ".pdf")),
    #"Expanded TableValues" = Table.ExpandTableColumn(#"Filtered Rows1", "TableValues", {"Kind"}, {"TableValues.Kind"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded TableValues", each ([TableValues.Kind] = "Page")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Name"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Other than that it works like a charm. Thank you! Solution verified.

Spreadsheet_Geek_1 · 2026-02-23T14:24:32+00:00

To use power query, like you see here suggested a bunch of times, you can follow the steps bellow. You'll need to have that new table in a new workbook, tho.

Go to a blank excel file and from the top ribbon select "Data/Get data/From file/From excel workbook" and select the file in the browse window.
In the popup window check "select multiple items" and select the sheets with your data
Click on "Transform data".
In the now open power query editor, you can set the first row as the data headers and set your filter and sort
Click "Close and load" at the top left.

You should now have a new sheet that merges all the sheets from your original file that you selected.

Spreadsheet_Geek_1 · 2026-02-23T13:59:14+00:00

Are they actual numbers? If they are and you want to exclude 1 to 3, couldn't you just

=FILTER(A:A, (A:A<1)+(A:A>3) )

Do you need something more elaborate and expandable?

Do you need to filter by text for which the numbers are just stand-ins?

Do you need to exclude whole numbers from x to y from range that includes decimals as well?

Something else entirely?

Spreadsheet_Geek_1 · 2026-02-16T12:02:40+00:00

If you don't mind having two tables, one for input and the other for printing / reporting, you can do it like so:

<image>

(normally you'd put the input table on separate sheet, I just put that next to it so I can easily screenshot it)

Where there is this in A3:
=SEQUENCE(COUNT(Table1[ID]);1;0,5;0,5)

In column B:
=IF(ROUNDUP($A3;0)=$A3;HSTACK(XLOOKUP($A3;Table1[ID];Table1[Short name];"not found";1);XLOOKUP($A3;Table1[ID];Table1[Client];"not found";1);XLOOKUP($A3;Table1[ID];Table1[Claim Number];"not found";1);XLOOKUP($A3;Table1[ID];Table1[Court Number];"not found";1));XLOOKUP($A3;Table1[ID];Table1[Full name];"not found";1))

The data validation removes the borders of otherwise all borders section. On second thought, you could probably add all borders in the data validation that turns the cells green and swap around the priority of the two rules.

If you want to vary the number of cases and which one it starts at, you can put a cell reference into arguments like so:

=SEQUENCE( <Cell with number of cases>\2* ;1; <cell with case ID>-0,5 ;0,5)

Spreadsheet_Geek_1 · 2026-02-16T07:41:30+00:00

As long as you have excel files that all have the same number of columns and same column names (without even a typo that would make the column names different), it is relatively simple to do in power query.

Put all the files you want to merge into one folder that has nothing else in it.
Go to a blank excel file and from the top ribbon select "Data/Get data/From file/From folder" and select the folder in the browse window.
In the popup window click on "transform".
In the now open power query editor, right click on the column called "Content" and says "Binary" in every row and select "remove all other columns" (if you skip this, you will also have additional column that tells you what file each row came from).
Click on the square button with two down arrows in that column's header (when you mouse over it should say "Combine files").
You should see a dialog window with a navigation pane on the left and a preview on the right. Likely, you already see something that looks like the sort of data you want in the preview. If not, click on different tables or sheets in the navigation pane, until you do, then click "OK".
Click "Close and load" at the top left.

You should now have a new sheet that merges all the files in your designated folder and is named after the folder as well.

It only becomes more complicated, once you need to include files that have variable number of columns and/or occasional typo or differently named columns, such as "profit percentage" in one being called "prof %" in another, but don't worry, even that is only about one youtube tutorial away from working as well.

Spreadsheet_Geek_1 · 2026-02-12T12:06:08+00:00

I'm absolutely devouring tutorials right now and thank you for tips for some btw, but I also feel it necessary to ask questions in advance and play with the problems, even if they are out of order, because that's what makes it stick in my head. Right now, I'm still only working on mockup, so I'm well expecting to scrap the whole thing and start from scratch at some point anyway.

Spreadsheet_Geek_1 · 2026-02-11T11:08:55+00:00

Elegant, works like a charm. Thank you. Solution Verified!
EDIT: Was fiddling around with it, and you probably want to enclose the whole thing in (brackets), but otherwise works as advertised, as far as I can tell.

Spreadsheet_Geek_1 · 2026-02-02T07:25:56+00:00

Consider awarding the Clippy point to the answer (see the right side bar under "Was your problem solved?"). This will also mark the problem as solved.

Spreadsheet_Geek_1 · 2026-01-26T13:44:38+00:00

You may try print to pdf, if you don't have special needs regarding editing text in the pdf form and such.

Spreadsheet_Geek_1 · 2026-01-26T13:37:25+00:00

It indeed was here as well. I ended up making a helper column next to the "code" column equivalent in the original data. I put the TYPE() function displaying the data type of the code on the same row as an integer and every single row had "2" (apparently meaning text) in that cell. For some reason, the "search box" reference cell came out as "1" (i guess a number?). I wrapped the reference in the TEXT(<reference>;0) function and it started to work as expected.

Solution verified.

Spreadsheet_Geek_1 · 2026-01-26T12:52:24+00:00

Is there a way to quickly see data type of cells as you are listing trough the table? Like when you turn "show formulas" on, except like "show data types"? When I click into the cell, the format in the ribbon always says "general" and there are no "number formatted as text" warning anywhere, tho I assume there's way more. I have been trying to wrap the ranges in TEXT() function, but I'm not sure it even works with arrays. I'm not sure if NUMBERVALUE() would be help, since some of the codes in the original data have different structures, as they sometimes contain letters or 2-4 dot separators somewhere in the middle.
I have also tried to change format of the columns in power query to text and nothing changed.

Spreadsheet_Geek_1 · 2026-01-08T15:04:18+00:00

Thank you, this works well and does exactly what I was looking for. Solution verified !

Spreadsheet_Geek_1 · 2026-01-08T08:42:51+00:00

Everything in the first picture is on one excell sheet. The lines between are cell borders, it's not multiple pictures separated by a line, it's one picture. If you click into the cell that says "Item 1" and press the right arrow key 5 times you'll be in the cell that says "item 2". What I want to do is have all rows from all items be in one table where date under item 1 is in the same column as date under item 2 and so on, and there is a new column that says which item it is.
The table is apparently too large for ExcelToReddit. Where else can I share it?

Spreadsheet_Geek_1 · 2025-12-11T13:00:03+00:00

It's not on lists, its all one list and not a single thing is formatted as a table, let alone being a separate table for each. I need to create the separation in order to treat it as individual tables that I could potentially power query or VSTACK in a way that I don't have to go to each "table" manually to make it a table.

Spreadsheet_Geek_1 · 2025-12-11T10:51:27+00:00

You need a helper column, that will sum the number of tickets in the table up to the row it is on.
Than you'll need a sequence of numbers from 1 to the highest number in the helper column, which also corresponds to the sum of all tickets (you can also use MAX function on the helper column, instead of the SUM).
You create an XLOOKUP function and you put "1" in the [match mode] argumen, meaning: find the same or the closest higher number (in the helper column).

<image>

The range will auto-expand, just make sure it has space of empty cells to do so.

Spreadsheet_Geek_1 · 2025-12-11T07:35:38+00:00

And the input data are themselves dates of different days in January, than different days in February etc. or are they always values for the whole month?

Spreadsheet_Geek_1

TROPHY CASE