# Not sure what to call this one...

#### Ste_Moore01

##### Active Member
Hi guys,

I'm trying to find a formula to work out what the first and last delivery note number is depending on the delivery address.

For example on the table below if the delivery address is Kellihers Electrical in Kilkenny the first delivery note is 97325 and the last is 97327.
Book2.xls
ABCD
297325Kellihers Electrical Units 1 & 2 Loughboy Industrial Estate KILKENNY Rep. of Ireland
397326Kellihers Electrical Units 1 & 2 Loughboy Industrial Estate KILKENNY Rep. of Ireland
497327Kellihers Electrical Units 1 & 2 Loughboy Industrial Estate KILKENNY Rep. of Ireland
597328Kellihers Electrical M50 Business Park Ballymount Road Upper BALLYMOUNT Dublin Rep. of Ireland
Sheet1

Has anyone any suggestions on how I can do this with a formula?

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Oaktree

##### MrExcel MVP
Try using a pivot table.

assuming by "first" you mean smallest number and "last" you mean largest number (and that the delivery notes are indeed numbers).

In the layout of the pivot table wizard, put delivery address in row, put delivery number in data (double click on it and change count to min), and put delivery number in data (again) (double click on it and change count to max).

#### Arcticwarrio

##### Active Member
insert a row above the data, highlight the row and the click this on the menu. Tools > Filter > Auto filter

then change the address filter to the one you want
and look in the note number filter to see first and last

#### Ste_Moore01

##### Active Member
Thanks for the quick reply but it isn't bringing up the correct answers.

I get the tablebelow when I make a Pivot Table with this info.
Book2.xls
ABCD
4Kellihers Electrical M50 Business Park Ballymount Road Upper BALLYMOUNT Dublin Rep. of IrelandMin of Delivery Note0
5Max of Delivery Note0
6Kellihers Electrical Units 1 & 2 Loughboy Industrial Estate KILKENNY Rep. of IrelandMin of Delivery Note0
7Max of Delivery Note0
8Total Min of Delivery Note0
9Total Max of Delivery Note0
Sheet5

#### Oaktree

##### MrExcel MVP
It looks like your delivery note numbers are recognized as text values (instead of numbers).

Try formatting the delivery note numbers as numbers, putting a 0 in an empty cell, copying the cell with the zero, selecting the range of your delivery note numbers, and going to edit --> paste special --> add. Then, refresh your pivot table.

#### Ste_Moore01

##### Active Member
Thanks for the help.

I'll see if I can incorporate it into the rest of the spreadsheet now.

Thanks again!

Replies
0
Views
837
Replies
12
Views
819
Replies
6
Views
921
Replies
1
Views
1K
Replies
13
Views
686