Sort and Insert

nmspt · Apr 6th, 2002, 04:23 AM

Hi,

I would appreciate some help on this matter:

I'd like to sort a given range of cells (8 columns X variable nº rows) by a given column so that each group of rows, with the same value in the sort column, is "isolated" and a comment is inserted below that group of rows. I would like to do this for all values that appear.

Example:
Sort By
1 45 62 0,03 M 1,74 ...
2 43 62 0.05 M 1.73 ...
4 42 62 0.03 F 1.74 ...
3 Items with 62 ...

3 42 60 0.03 F 1.71 ...
6 40 60 0.03 M 1.17 ...
7 47 60 0.03 F 1.75 ...
8 45 60 0.03 M 1.75 ...
3 Items with 60 ...

...

Thanks,
Nelson

Tom Urtis · Apr 6th, 2002, 07:10 AM

Your post is a little vague, but it sounds like you need to sort your data, and then either filter it based on the criteria of your choice located in the column(s) of your choice (to perform the "isolation"), or, after sorting, you need to do a subtotal, to put the comments in underneath the sorted values of your choice, which in your post looks like is a sum of each change in value of column B.

Each one of those 3 operations (sorting, filtering, and subtotaling) are under the Data menu option.

If you need other assistance beyond that, please repost.

HOTDOG · Apr 6th, 2002, 11:32 AM

This will not insert a row will but it will count the number of items which are the same.

Sort your data by your desired column,
then paste this formula into cell I1, or any column of your choice,
=IF(C1=C2,"",COUNTIF(C:C,C1)&" items with " &C1)

This formula assumes you are sorting by col C, if not then change each c to the column you are sorting by.

then drag it down to the last row with data in it.

I know this is short of what you were looking but I hope this helps.

Apr 6th, 2002, 07:10 AM	#2
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,388	Your post is a little vague, but it sounds like you need to sort your data, and then either filter it based on the criteria of your choice located in the column(s) of your choice (to perform the "isolation"), or, after sorting, you need to do a subtotal, to put the comments in underneath the sorted values of your choice, which in your post looks like is a sum of each change in value of column B. Each one of those 3 operations (sorting, filtering, and subtotaling) are under the Data menu option. If you need other assistance beyond that, please repost. __________________ Tom Urtis Microsoft MVP - Excel

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 6th, 2002, 04:23 AM	#1
nmspt New Member Join Date: Mar 2002 Posts: 2	Hi, I would appreciate some help on this matter: I'd like to sort a given range of cells (8 columns X variable nº rows) by a given column so that each group of rows, with the same value in the sort column, is "isolated" and a comment is inserted below that group of rows. I would like to do this for all values that appear. Example: Sort By 1 45 62 0,03 M 1,74 ... 2 43 62 0.05 M 1.73 ... 4 42 62 0.03 F 1.74 ... 3 Items with 62 ... 3 42 60 0.03 F 1.71 ... 6 40 60 0.03 M 1.17 ... 7 47 60 0.03 F 1.75 ... 8 45 60 0.03 M 1.75 ... 3 Items with 60 ... ... Thanks, Nelson

Apr 6th, 2002, 11:32 AM	#3
HOTDOG New Member Join Date: Mar 2002 Location: Northern Ireland Posts: 6	This will not insert a row will but it will count the number of items which are the same. Sort your data by your desired column, then paste this formula into cell I1, or any column of your choice, =IF(C1=C2,"",COUNTIF(C:C,C1)&" items with " &C1) This formula assumes you are sorting by col C, if not then change each c to the column you are sorting by. then drag it down to the last row with data in it. I know this is short of what you were looking but I hope this helps.