Linking sort results

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

Your help would be greatly appreciated!
I have a master list of "Hot Topics" that includes many
entries for different departments and is updated daily.
This list is not sorted and has the items entered
chronologicly, not grouped by department. I would like to
extract the items that pertain to each individual
department to a seperate worksheet, so each department
would have a sheet containing the items that pertain to
them. I have department name column and an item column.
Thanks in advance.
 
Randy,

As I am no whiz at this, there may well be a better
solution and I'm sure someone will post it if it's out
there, but here's what I would start with:

Assume:
Row1 = Header
Row2 and down = Entries
Col1 = Dept Name
Col2 = Item
All other Col's may or may not contain data.
For the sake of example here I'll name Dept's Dept1, Dept2
(you can have as many Dept names as required)

On Sheet2 of your workbook enter this into A1:

=Sheet1!A1

Copy this and paste it across R1 as far as necessary to
pick up all your headers.

Then in Sheet2 R2C1 enter this:

=IF(Sheet1!$A3="Dept1",Sheet1!A3,"")

Copy and paste this formula down as far as necessary to
pick up all your entries on Sheet1.

Then on Sheet2 R2ColB enter this:

=IF(Sheet1!$A3="Dept1",Sheet1!B3,"")

Again, copy this and paste it down ColA as far as
necessary.

Do the same for each Column, adjusting as necessary.
Do this same procedure on a seperate sheet for each Dept.

Now, to bring all data on Sheet2 to the top of the sheet,
on Sheet2 click your cursor in R1ColA, hit
Data/Sort/ColA/Descending/check "Header Row" Click OK and
all the data for Dept1 should appear at the top of Sheet2.

Do this to Sheet3-4-5-etc, adjust formulas as needed and
you'll have a seperate data sheet for each Dept.

The sort function can be done using a macro, sorting each
Dept's Sheet in one operation, if you're familiar with the
macro recorder.

Couple of words of warning here...enter data only on
Sheet1 and make sure the "Header Row" is ticked when doing
the sort.

HTH,

Don
 
Maybe you'd like to try this approach as well ..

Assume the sample list below
is in Sheet1, cols A and B
with data from row2 down

Dept Item
ABC 123
DEF 234
ABC 456
DEF 222
ABC 333

List the depts across in D1:E1
In D1: ABC
In E1: DEF

Put in D2: =IF(ISBLANK($A2),"",IF($A2=D$1,ROW(),""))

Copy D2 across to E2, then down a safe number of rows
to cater for the max likely rows of data in cols A and B
(copy down to say, E1000)

Cols D and E will set it up for the extract formula
we're going to put in Sheets 2 and 3 below

-------------
In Sheet2 (for dept: ABC)
-------------
Put in A1:

=IF(ISERROR(MATCH(SMALL(Sheet1!D:D,ROW(A1)),Sheet1!D:D,0)),"",OFFSET(Sheet1!
$A$1,MATCH(SMALL(Sheet1!D:D,ROW(A1)),Sheet1!D:D,0)-1,1))

Copy A1 down to A1000
(or by a lesser "safe" number of rows
to cater for the max likely rows of data for dept: ABC)

In A1:A1000 will be the extracted items
from col B of Sheet1 for dept: ABC
-------------
In Sheet3 (for dept: DEF)
-------------
Put in A1:

=IF(ISERROR(MATCH(SMALL(Sheet1!E:E,ROW(A1)),Sheet1!E:E,0)),"",OFFSET(Sheet1!
$A$1,MATCH(SMALL(Sheet1!E:E,ROW(A1)),Sheet1!E:E,0)-1,1))

( Formula above is identical to the one we set-up in Sheet2,
except that it points to col E in Sheet1 [for dept: DEF],
instead of col D. You could just copy > paste the same formula
into A1, then do an edit > replace to change "D:D" to "E:E" )

Copy A1 down to A1000
(or by a lesser "safe" number of rows
to cater for the max likely rows of data for dept: DEF)

In A1:A1000 will be the extracted items
from col B of Sheet1 for dept: DEF
--
For the sample list in Sheet1,
the results in col A will be:

In Sheet2 (dept: ABC)
-------------------------
123
456
333

In Sheet3 (dept: DEF)
 
Hi Max,

Another function I must study.....just learning this stuff
and taking it slowly one step at a time.

Don

-----Original Message-----
Maybe you'd like to try this approach as well ..

Assume the sample list below
is in Sheet1, cols A and B
with data from row2 down

Dept Item
ABC 123
DEF 234
ABC 456
DEF 222
ABC 333

List the depts across in D1:E1
In D1: ABC
In E1: DEF

Put in D2: =IF(ISBLANK($A2),"",IF($A2=D$1,ROW(),""))

Copy D2 across to E2, then down a safe number of rows
to cater for the max likely rows of data in cols A and B
(copy down to say, E1000)

Cols D and E will set it up for the extract formula
we're going to put in Sheets 2 and 3 below

-------------
In Sheet2 (for dept: ABC)
-------------
Put in A1:

=IF(ISERROR(MATCH(SMALL(Sheet1!D:D,ROW(A1)),Sheet1! D:D,0)),"",OFFSET(Sheet1!
$A$1,MATCH(SMALL(Sheet1!D:D,ROW(A1)),Sheet1!D:D,0)-1,1))

Copy A1 down to A1000
(or by a lesser "safe" number of rows
to cater for the max likely rows of data for dept: ABC)

In A1:A1000 will be the extracted items
from col B of Sheet1 for dept: ABC
-------------
In Sheet3 (for dept: DEF)
-------------
Put in A1:

=IF(ISERROR(MATCH(SMALL(Sheet1!E:E,ROW(A1)),Sheet1! E:E,0)),"",OFFSET(Sheet1!
$A$1,MATCH(SMALL(Sheet1!E:E,ROW(A1)),Sheet1!E:E,0)-1,1))

( Formula above is identical to the one we set-up in Sheet2,
except that it points to col E in Sheet1 [for dept: DEF],
instead of col D. You could just copy > paste the same formula
into A1, then do an edit > replace to change "D:D" to "E:E" )

Copy A1 down to A1000
(or by a lesser "safe" number of rows
to cater for the max likely rows of data for dept: DEF)

In A1:A1000 will be the extracted items
from col B of Sheet1 for dept: DEF
--
For the sample list in Sheet1,
the results in col A will be:

In Sheet2 (dept: ABC)
-------------------------
123
456
333

In Sheet3 (dept: DEF)
-------------------------
234
222
--
Extend the set-up to suit
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
Your help would be greatly appreciated!
I have a master list of "Hot Topics" that includes many
entries for different departments and is updated daily.
This list is not sorted and has the items entered
chronologicly, not grouped by department. I would like to
extract the items that pertain to each individual
department to a seperate worksheet, so each department
would have a sheet containing the items that pertain to
them. I have department name column and an item column.
Thanks in advance.
Click to expand...


.
Click to expand...
 
Thanks for your help!

Randy
-----Original Message-----
Hi Max,

Another function I must study.....just learning this stuff
and taking it slowly one step at a time.

Don

-----Original Message-----
Maybe you'd like to try this approach as well ..

Assume the sample list below
is in Sheet1, cols A and B
with data from row2 down

Dept Item
ABC 123
DEF 234
ABC 456
DEF 222
ABC 333

List the depts across in D1:E1
In D1: ABC
In E1: DEF

Put in D2: =IF(ISBLANK($A2),"",IF($A2=D$1,ROW(),""))

Copy D2 across to E2, then down a safe number of rows
to cater for the max likely rows of data in cols A and B
(copy down to say, E1000)

Cols D and E will set it up for the extract formula
we're going to put in Sheets 2 and 3 below

-------------
In Sheet2 (for dept: ABC)
-------------
Put in A1:

=IF(ISERROR(MATCH(SMALL(Sheet1!D:D,ROW(A1)),Sheet1! D:D,0)),"",OFFSET(Sheet1!
$A$1,MATCH(SMALL(Sheet1!D:D,ROW(A1)),Sheet1!D:D,0)-1,1))

Copy A1 down to A1000
(or by a lesser "safe" number of rows
to cater for the max likely rows of data for dept: ABC)

In A1:A1000 will be the extracted items
from col B of Sheet1 for dept: ABC
-------------
In Sheet3 (for dept: DEF)
-------------
Put in A1:

=IF(ISERROR(MATCH(SMALL(Sheet1!E:E,ROW(A1)),Sheet1! E:E,0)),"",OFFSET(Sheet1!
$A$1,MATCH(SMALL(Sheet1!E:E,ROW(A1)),Sheet1!E:E,0)-1,1))

( Formula above is identical to the one we set-up in Sheet2,
except that it points to col E in Sheet1 [for dept: DEF],
instead of col D. You could just copy > paste the same formula
into A1, then do an edit > replace to change "D:D" to "E:E" )

Copy A1 down to A1000
(or by a lesser "safe" number of rows
to cater for the max likely rows of data for dept: DEF)

In A1:A1000 will be the extracted items
from col B of Sheet1 for dept: DEF
--
For the sample list in Sheet1,
the results in col A will be:

In Sheet2 (dept: ABC)
-------------------------
123
456
333

In Sheet3 (dept: DEF)
-------------------------
234
222
--
Extend the set-up to suit
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
Your help would be greatly appreciated!
I have a master list of "Hot Topics" that includes many
entries for different departments and is updated daily.
This list is not sorted and has the items entered
chronologicly, not grouped by department. I would like to
extract the items that pertain to each individual
department to a seperate worksheet, so each department
would have a sheet containing the items that pertain to
them. I have department name column and an item column.
Thanks in advance.
Click to expand...


.
Click to expand...
.
Click to expand...
 
Back
Top