Trying to group cells so that I can sort the upper most cell with.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have schools that are linked to three different businesses. I am trying to
sort the schools by their name, but when I do, it sorts the businesses by
their names as well. How can I make the schools sort and simply pull the
businesses that are under them with them? I need to group them in such a way
that it sees the school and moves the entire block with the school name
without moving any of the businesses. Can anyone help me with that?
 
Hi

it's hard to answer this without knowing what your worksheet looks like, if
it looks like this:
..........A..............B
1....School1......Business1
2....School1......Business3
3...School2......Business1
4....School1....Business2

and you want to see
..........A..............B
1....School1......Business1
2....School1......Business3
3....School1....Business2
4...School2......Business1

then click in A1 and choose data / sort and ensure only the first drop down
box has school in it and the other 2 drop down boxes have nothing in them.

However, if you have
..........A..............B
1....School1......Business1
2......................Business3
3...School2......Business1
4....School1....Business2

and you want to see
..........A..............B
1....School1......Business1
2......................Business3
3......................Business2
4...School2......Business1

then i have no idea on how to do this

Cheers
JulieD
 
It is like this:
School A
Business 1
Business 2
School B
Business 3
Business 4
etc.
I need School A and business 1&2 to stay as one block. Same with School B.
So that I can sort these as I go without having to copy and paste the entire
block. Thank your for trying, though.
 
Changing the thread title kinds screws up the archiving system, so best to
leave as is :-)

If your data example means that you have School in one column and Business
in the next, then select all of the school column from the first school
entry to the last row that has a corresponding business, do Edit / Go to /
Special / Blanks, hit the = button and then hit the UP arrow once and use
CTRL+ENTER to enter this. Now Copy the school column and paste special as
values. Select both columns and sort to your hearts content now. If you
want to get rid of the duplicate school tags once you are done then just
post back.
 
You can do this very easily with a helper column. First, back up your data
and use a copy for this exercize. Assuming that your data is structured as
a single column with school names and business names following them downward
then another school name and then more business names, etc etc down the
column......then, if you have some one identifier in each and every school
name string, such as the word "school", then try this:
Column A has your data
Cell B1 put =IF(COUNTIF(A1:A1,"*school*")>0,A1&1,"")
Cell B2 put =IF(COUNTIF(A2:A2,"*school*")>0,A2&1,B1&1) and copy down column
B.
Then highlight column B and Copy > PasteSpecial > Values over itself..

Now, you can sort on column D ..........the data will come out with the
schools being in ascending order followed immediately by their associated
businesses.

After sorting, you can delete the helper column if you wish.

Vaya con Dios,
Chuck, CABGx3
 
I thank you for your help and am trying out the suggestions as I write this.
I will see if I can get it to work. Thanks for the time and have a great day.
 
Not quite sure what you were telling me to do, but I will go figure it out.
I am computer literate, but not when it comes to excel. Thank you for your
time and I will see if I can't get this to work from here. Have a great day.
 
Give it a go and just post back if need be :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

magnetoworld said:
Not quite sure what you were telling me to do, but I will go figure it out.
I am computer literate, but not when it comes to excel. Thank you for your
time and I will see if I can't get this to work from here. Have a great
Click to expand...
day.
 
Back
Top