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
"magnetoworld" <[email protected]> wrote in message
news:[email protected]...
>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?
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.
--
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" <[email protected]> wrote in message
news:[email protected]...
> 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.
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
"magnetoworld" <[email protected]> wrote in message
news:[email protected]...
> 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?
Sorry, my type-o, I meant you can then sort on Column B, not D
Vaya con Dios,
Chuck, CABGx3
"CLR" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
> "magnetoworld" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
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" <[email protected]> wrote in message
news:[email protected]...
> 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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks