Sorting List to a new sheet

[jermsalerms]

I have a database being exported to a spreadsheet with over 40 fields
(columns) and 1000+ clients (rows). One of the fields (column C)
incidates that referral source. I would like to copy & paste the list
into sheet 1 and have 5 seperate sheets for each of the 5 different
referral sources. So that when I go to sheet 2 the only thing I will
see are the clients and their 40 associated fields that are referred
(column C) by say "John Smith"


Is there a function or script that will search column C for "John
Smith" and then fill in all the 40 fields on sheet 2.

 

[Ron Coderre]

Is there a reason you wouldn't want to use an AutoFilter or Advanced
Filter?

Regards,
Ron

 

[jermsalerms]

I want to be able to open the spreadsheet and immediately go to sheet
2-5 and see the data i need without going through steps. This will be a
report that gets viewed daily and the status' will change constantly.

The reports are to be viewed by multiple employees and I want them to
have quick access to the information without manipulating the
spreadsheet.

 

[Ron Coderre]

Um....OK...those are good reasons!

Now, I know there are formulaic approaches to do what you want, but
IMHO they sometimes unnecessarily bloat the size of the workbook.

Here's an alternative approach to try with an example:

Assumptions:
Sheet1 contains your data in cells A1:Z1000 with col_A holding referral
sources.
Sheets 2 through 6 will contain the extracted data to be displayed.

Using Sheet2:
The extracted data will be begin in cells $A$10:$Z$10
Insert>Name>Define
Names in workbook: Sheet2!Extract
Refers to: =Sheet2!$A$10:$Z$10
_____________________________

A1: ReferralSource
A2: (put your first referral source here)

Insert>Name>Define
Names in workbook: Sheet2!Criteria
Refers to: =Sheet2!$A$1:$A$2
______________________________

Still using Sheet2:
Insert>Name>Define
Names in workbook: Sheet2!Database
Refers to: =Sheet1!$A$1:$Z$1000

(Notice: you are on Sheet2, and creating a Sheet2-level range name,
but
the referenced range is on Sheet1)
______________________________

The reason: An advanced filter cannot SEND data to another sheet, but
it can PULL data from another sheet.

Now...set up the Advanced Data Filter:
Data>Filter>Advanced Data Filter
Select: Copy to another location
List Range: (press F3 and select Database)
Criteria Range: (press F3 and select Criteria)
Copy To: (press F3 and select Extract)
Click [OK]

Repeat that procedure for Sheets 3 through 6, changing sheet
references.

Or for a shortcut...just right-click the tab of Sheet2 and create a
copy in the same workbook. All of the same range names will carry over
and they will each be sheet specific. Then, just change the referral in
the criteria on each sheet.

Note: if you want to run that Advanced Data Filter repeatedly,
you'll need to re-select Database each time
....OR...if you're feeling a bit ambitious...

You can build a simple macro to automatically re-run the filter:
Press [Alt]+[F11] to open the VBA editor
Right click on the VBA Project folder for your workbook
Select: Insert>Module

Then, copy/paste this code into that module:

'---Start of Code-------
Option Explicit

Sub PullMatchingData()
Range("Database").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("Criteria"), _
CopyToRange:=Range("Extract"), _
Unique:=False
End Sub
'---End of Code-------

To run the code:
Tools>Macro>Macros (or [Alt]+[F8]) on each of the 5 report sheets
Select and run: PullMatchingData

You could also put a button on each sheet if you wanted.

If that is something you'd like to try....just post back with any
questions.

Regards,
Ron

 

[jermsalerms]

this works execpt one thing.

One of my sources is "IMC" and another is "IMC - Redist."

When I put IMC in A2 on Sheet 2 it pulls data on both sources. Is ther
a to keep it from pulling the other one?

Once I have this fixed im going to try to tackle the macro

 

[jermsalerms]

Still not sure how to address the two sources with the same firs
words.

One thing I did differently was make a drop down list usin
Data>Validation for the referral source I am searching. This way I don
have to change sheets so much.

I also got the script working. Can you help me to create a button tha
will run the script. That way I will be able to select the source fro
the drop down list and just click the button to run the macro.

Thanks for all the help I am learning alot more than I could hav
imagined

 

[Ron Coderre]

First, The button:
Right-click in the gray area at the top of the window
Check: Forms

On the Forms toolbar, click the button icon
Draw the button on the worksheet
You'll be prompted to assign a macro....choose your macro
That's it....You're done.
Click that button to run the extract.

Next, the multiple, similar Referral Sources issue:
In the Critera cells, change the critera header to "MatchThis" (or an
other text that is NOT a column heading.
In the next cell down, enter a formula like this:
=Sheet1!A2="IMC"

Change the sheet name and reference to suit your situation.
The key is that the A2 refers to the 1st cell under the referral nam
heading in the data list.

Does that help?

Regards,
Ro

 

[jermsalerms]

it is working grea

 

[Ron Coderre]

Thanks for the feedback...I'm glad you got that working.

Regards,
Ro

 

[jermsalerms]

ron,

this macro was working fine until i added a button to run it...now i
get this error.

I even started the spreadsheet from scratch and it does the same thing.
It will work if i manually run the advanced filter but would really
prefer to get the nacro working again.

any suggestions?

 

[Ron Coderre]

I built a new model from scratch using the instructions I posted fo
you.
Clicking the button that I placed on each sheet executes the cod
without incident.

Re-check everything....range names, code, col headings, etc.

Perhaps if you post your code we'll spot somthing amiss in it.

Regards,
Ro

 

[jermsalerms]

Im not sure why it suddenly stopped working but I used the "recor
macro" button and ran the advanced filter. and it is working wit
that.

Here is the code it anyone else every needs it.

Sub CommissionReport()
'
' CommissionReport Macro
' Macro recorded 3/1/2006 by Jeremy Becker
'

'
Range("'Mthly Commission Report'!Database").AdvancedFilter Action:
_
xlFilterCopy, CriteriaRange:=Range("'Mthly Commissio
Report'!Criteria"), _
CopyToRange:=Range("'Mthly Commission Report'!Extract")
Unique:=False
End Sub

Thanks for all the hel