Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old September 13th 13, 02:04 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 27
Default

Quote:
Originally Posted by Claus Busch View Post
Hi,



in which column are your numbers? You have to modify the references to
your sheet.



I have now made it possible to find quickly the highest number in my
cataloguing sequence by inserting an extra column, now column A, and
entering sequential numbers into it. However, this seems a clumsy way
to do it.


Another way is to insert a new column with your medium, e.g. CD, DVD.
Then you can put your tables together to one table, you can sort first
by medium and then by composer and if you want enter new data you can
sort by number, and so on.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
The numbers, now out of sequence, are in column B

  #12   Report Post  
Old September 13th 13, 03:30 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,607
Default Sorting rows

Hi,

Am Fri, 13 Sep 2013 14:04:47 +0100 schrieb Quilp:

The numbers, now out of sequence, are in column B


if the name of the CD or DVD is in column E and you will look for the
highest sequential number of the name in E1, then try:
=MAX(IF(E1:E1000=E1,B1:B1000))
and enter the array formula with CTRL+Shift+Enter.
If i still misunderstand your problem please upload a sample.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #13   Report Post  
Old September 13th 13, 05:16 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 27
Default

Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2[/quote]

I can't see a file format listed in the "Attach Files" which allows me to attach my worksheet. I apologise if my posts are confusing as a result of inexperience of worksheets so let me try again:
Column A shows the catalogue number I have allocated to the recording (cd's, for example, have a label on them showing this number) When I buy a new recording, either cd or mp3 download, I allocate the next number. This was easy when the numbers were in sequential order but now, after sorting the sheet in alphabetical order of composer listed in column D, the numbers in column A are no longer in sequential order which is to be expected. This makes it difficult to find the last number used so as to enable me to allocate the next to a newly acquired recording. I would like to like to either put a formula in the next A cell which would return this number or find it in some other way.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2[/quote]

Last edited by Quilp : September 13th 13 at 05:18 PM Reason: message was shown in duplicate
  #14   Report Post  
Old September 13th 13, 05:19 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 27
Default

Quote:
Originally Posted by Quilp View Post
Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
I can't see a file format listed in the "Attach Files" which allows me to attach my worksheet. I apologise if my posts are confusing as a result of inexperience of worksheets so let me try again:
Column A shows the catalogue number I have allocated to the recording (cd's, for example, have a label on them showing this number) When I buy a new recording, either cd or mp3 download, I allocate the next number. This was easy when the numbers were in sequential order but now, after sorting the sheet in alphabetical order of composer listed in column D, the numbers in column A are no longer in sequential order which is to be expected. This makes it difficult to find the last number used so as to enable me to allocate the next to a newly acquired recording. I would like to like to either put a formula in the next A cell which would return this number or find it in some other way.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2[/quote][/quote]
  #15   Report Post  
Old September 13th 13, 06:01 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,607
Default Sorting rows

Hi,

Am Fri, 13 Sep 2013 17:16:50 +0100 schrieb Quilp:

Column A shows the catalogue number I have allocated to the recording
(cd's, for example, have a label on them showing this number)


try:
=MAX(A:A)


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


  #16   Report Post  
Old September 14th 13, 08:54 AM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 27
Default

Quote:
Originally Posted by Claus Busch View Post
Hi,

Am Fri, 13 Sep 2013 17:16:50 +0100 schrieb Quilp:

Column A shows the catalogue number I have allocated to the recording
(cd's, for example, have a label on them showing this number)


try:
=MAX(A:A)


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
I have been trying this function(?) but the results are confusing. If I try it in column A it works (not that I need to use it in this column because it is in sequential order) but when I use it in column B, which contains the same numbers but out of sequence following the sorting previously discussed, it returns zero. Is it the case that you cannot use the MAX function in columns that have been sorted? Or, perhaps it should be applied before sorting to make it work.
  #17   Report Post  
Old September 14th 13, 11:34 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,607
Default Sorting rows

Hi,

Am Sat, 14 Sep 2013 08:54:32 +0100 schrieb Quilp:

I have been trying this function(?) but the results are confusing. If I
try it in column A it works (not that I need to use it in this column
because it is in sequential order) but when I use it in column B, which
contains the same numbers but out of sequence following the sorting
previously discussed, it returns zero. Is it the case that you cannot
use the MAX function in columns that have been sorted? Or, perhaps it
should be applied before sorting to make it work.


if the MAX function returns 0 your numbers are text formatted.
Write a 1 in an empty cell, copy this cell, select all numbers in column
B, Insert = Paste Special = Multiply


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #18   Report Post  
Old September 15th 13, 08:53 AM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 27
Default

Quote:
Originally Posted by Claus Busch View Post
Hi,

Am Sat, 14 Sep 2013 08:54:32 +0100 schrieb Quilp:

I have been trying this function(?) but the results are confusing. If I
try it in column A it works (not that I need to use it in this column
because it is in sequential order) but when I use it in column B, which
contains the same numbers but out of sequence following the sorting
previously discussed, it returns zero. Is it the case that you cannot
use the MAX function in columns that have been sorted? Or, perhaps it
should be applied before sorting to make it work.


if the MAX function returns 0 your numbers are text formatted.
Write a 1 in an empty cell, copy this cell, select all numbers in column
B, Insert = Paste Special = Multiply


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
The PlanMaker Paste Special is, I suspect, different to Excel and I am unable to follow your instruction. It seems that I will have to use the extra column A, containing the sequential numbers, in order to locate the last.
I should not really be using this forum because I do not have Excel but it is so much better than the PlanMaker forums. Thanks for all your help Claus - it is much appreciated. Perhaps one day, if you have nothing better to do, you could download the free version of PlanMaker and see if you can resolve this MAX problem!
Regards
Quilp


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting rows out of order after all rows are numbered in sequence Mikey Excel Programming 1 December 16th 09 10:41 PM
sorting rows Mike Excel Discussion (Misc queries) 1 November 7th 09 07:54 PM
Sorting block of rows with empty rows between it jgmiddel[_9_] Excel Programming 1 October 2nd 08 01:38 PM
Sorting rows Lowell Excel Programming 4 May 13th 07 05:54 AM
[sorting rows] digisummo Excel Worksheet Functions 1 June 16th 06 03:45 PM


All times are GMT +1. The time now is 12:05 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017