# 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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### 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!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,022
Messages
5,834,994
Members
430,330
Latest member
Syed Yasir Hannan

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back