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?
 

Excel Facts

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

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
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
8,053
Office Version
  1. 365
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!
 
Master Excel Bundle

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

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top