Not sure what to call this one...

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
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
1Delivery NoteDelivery Address
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?
 

Some videos you may like

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
Joined
Jun 20, 2002
Messages
7,988
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
Joined
Dec 6, 2005
Messages
439
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
Joined
Jul 13, 2005
Messages
467
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
3Delivery AddressDataTotal
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
Joined
Jun 20, 2002
Messages
7,988
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
Joined
Jul 13, 2005
Messages
467
Thanks for the help.

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

Thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,678
Messages
5,573,608
Members
412,539
Latest member
itchy00
Top