Not sure what to call this one...

[Ste_Moore01]

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
	A	B	C	D
1	Delivery Note	Delivery Address
2	97325	Kellihers Electrical Units 1 & 2 Loughboy Industrial Estate KILKENNY Rep. of Ireland
3	97326	Kellihers Electrical Units 1 & 2 Loughboy Industrial Estate KILKENNY Rep. of Ireland
4	97327	Kellihers Electrical Units 1 & 2 Loughboy Industrial Estate KILKENNY Rep. of Ireland
5	97328	Kellihers 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?

 

[Oaktree]

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]

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]

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
	A	B	C	D
3	Delivery Address	Data	Total
4	Kellihers Electrical M50 Business Park Ballymount Road Upper BALLYMOUNT Dublin Rep. of Ireland	Min of Delivery Note	0
5		Max of Delivery Note	0
6	Kellihers Electrical Units 1 & 2 Loughboy Industrial Estate KILKENNY Rep. of Ireland	Min of Delivery Note	0
7		Max of Delivery Note	0
8	Total Min of Delivery Note		0
9	Total Max of Delivery Note		0
Sheet5

 

[Oaktree]

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]

Thanks for the help.

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

Thanks again!