Extract data on one spreadsheet to another based on criteria

I have a list of data on one worksheet called raw_data coming from an 
external HR database

example below:

Month	Site	         Hire Date	         Name	Employee ID
January	Bedfont	      01/01/2007	Andrew	000000001
January	Bedfont	     05/01/2007	        Harry	000000002
January	Hams Hall  31/01/2007	     Simon	000000003
January	Colworth     31/01/2007	       Jenner	000000004
January	Colworth      31/01/2007	Harry	000000005
February Leeds	       09/02/2007	Simon	000000006
February Colworth	10/02/2007	Mavis	000000007
February Colworth	11/02/2007	Harry	000000008
February Hams Hall	12/02/2007	Jenner	000000009
March	Colworth	13/03/2007	Harry	000000010
March	Colworth	14/03/2007	Simon	000000011
March	Leeds	          15/03/2007	Mavis	000000012
March	Colworth	16/03/2007	Harry	000000013
March	Colworth	17/03/2007	Simon	000000014
March	Leeds	        18/03/2007	Susan	000000015

I would like to extract from the list and display on another worksheet 
called Jan in cells a1 to e1 and below employees that match the month and the 
site of Bedfont, I would then like to repeat the exercise a few cells along 
for Hams Hall etc.  

Then repeat the exercise for month of February.

From this I can then have a separate table which would count the number of 
starters by location and month, and attach a hyperlink to it so that the HR 
team can see a listing of how many starters in each month, and use this 
information to prepare for inductions and other such HR related matters.

Any help much appreciated on how to achieve this.

Regards

Andrew

0
AndyJ1 (3)
1/3/2008 11:43:01 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
502 Views

Similar Articles

[PageSpeed] 41

Take a look at the Data > Filter > AutoFilter feature........it should do
the filtering you want, then just copy and paste........

Vaya con Dios,
Chuck, CABGx3


"AndyJ" <AndyJ@discussions.microsoft.com> wrote in message
news:7A621A15-A93E-4A2D-B629-EAF9D3920D49@microsoft.com...
> I have a list of data on one worksheet called raw_data coming from an
> external HR database
>
> example below:
>
> Month Site          Hire Date          Name Employee ID
> January Bedfont       01/01/2007 Andrew 000000001
> January Bedfont      05/01/2007         Harry 000000002
> January Hams Hall  31/01/2007      Simon 000000003
> January Colworth     31/01/2007        Jenner 000000004
> January Colworth      31/01/2007 Harry 000000005
> February Leeds        09/02/2007 Simon 000000006
> February Colworth 10/02/2007 Mavis 000000007
> February Colworth 11/02/2007 Harry 000000008
> February Hams Hall 12/02/2007 Jenner 000000009
> March Colworth 13/03/2007 Harry 000000010
> March Colworth 14/03/2007 Simon 000000011
> March Leeds           15/03/2007 Mavis 000000012
> March Colworth 16/03/2007 Harry 000000013
> March Colworth 17/03/2007 Simon 000000014
> March Leeds         18/03/2007 Susan 000000015
>
> I would like to extract from the list and display on another worksheet
> called Jan in cells a1 to e1 and below employees that match the month and
the
> site of Bedfont, I would then like to repeat the exercise a few cells
along
> for Hams Hall etc.
>
> Then repeat the exercise for month of February.
>
> From this I can then have a separate table which would count the number of
> starters by location and month, and attach a hyperlink to it so that the
HR
> team can see a listing of how many starters in each month, and use this
> information to prepare for inductions and other such HR related matters.
>
> Any help much appreciated on how to achieve this.
>
> Regards
>
> Andrew
>


0
croberts (1377)
1/3/2008 11:58:10 PM
If you just want numbers of starts per month then a Pivot Table will do all 
that you want at once.

Data > Pivot Table Report and follow the steps of the Wizard.

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"AndyJ" <AndyJ@discussions.microsoft.com> wrote in message 
news:7A621A15-A93E-4A2D-B629-EAF9D3920D49@microsoft.com...
>I have a list of data on one worksheet called raw_data coming from an
> external HR database
>
> example below:
>
> Month Site          Hire Date          Name Employee ID
> January Bedfont       01/01/2007 Andrew 000000001
> January Bedfont      05/01/2007         Harry 000000002
> January Hams Hall  31/01/2007      Simon 000000003
> January Colworth     31/01/2007        Jenner 000000004
> January Colworth      31/01/2007 Harry 000000005
> February Leeds        09/02/2007 Simon 000000006
> February Colworth 10/02/2007 Mavis 000000007
> February Colworth 11/02/2007 Harry 000000008
> February Hams Hall 12/02/2007 Jenner 000000009
> March Colworth 13/03/2007 Harry 000000010
> March Colworth 14/03/2007 Simon 000000011
> March Leeds           15/03/2007 Mavis 000000012
> March Colworth 16/03/2007 Harry 000000013
> March Colworth 17/03/2007 Simon 000000014
> March Leeds         18/03/2007 Susan 000000015
>
> I would like to extract from the list and display on another worksheet
> called Jan in cells a1 to e1 and below employees that match the month and 
> the
> site of Bedfont, I would then like to repeat the exercise a few cells 
> along
> for Hams Hall etc.
>
> Then repeat the exercise for month of February.
>
> From this I can then have a separate table which would count the number of
> starters by location and month, and attach a hyperlink to it so that the 
> HR
> team can see a listing of how many starters in each month, and use this
> information to prepare for inductions and other such HR related matters.
>
> Any help much appreciated on how to achieve this.
>
> Regards
>
> Andrew
>
> 


0
sandymann2 (1054)
1/3/2008 11:59:27 PM

"Sandy Mann" wrote:

> If you just want numbers of starts per month then a Pivot Table will do all 
> that you want at once.
> 
> Data > Pivot Table Report and follow the steps of the Wizard.
> 
> -- 
> HTH
> 
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
> 
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
> 
> 
> "AndyJ" <AndyJ@discussions.microsoft.com> wrote in message 
> news:7A621A15-A93E-4A2D-B629-EAF9D3920D49@microsoft.com...
> >I have a list of data on one worksheet called raw_data coming from an
> > external HR database
> >
> > example below:
> >
> > Month Site          Hire Date          Name Employee ID
> > January Bedfont       01/01/2007 Andrew 000000001
> > January Bedfont      05/01/2007         Harry 000000002
> > January Hams Hall  31/01/2007      Simon 000000003
> > January Colworth     31/01/2007        Jenner 000000004
> > January Colworth      31/01/2007 Harry 000000005
> > February Leeds        09/02/2007 Simon 000000006
> > February Colworth 10/02/2007 Mavis 000000007
> > February Colworth 11/02/2007 Harry 000000008
> > February Hams Hall 12/02/2007 Jenner 000000009
> > March Colworth 13/03/2007 Harry 000000010
> > March Colworth 14/03/2007 Simon 000000011
> > March Leeds           15/03/2007 Mavis 000000012
> > March Colworth 16/03/2007 Harry 000000013
> > March Colworth 17/03/2007 Simon 000000014
> > March Leeds         18/03/2007 Susan 000000015
> >
> > I would like to extract from the list and display on another worksheet
> > called Jan in cells a1 to e1 and below employees that match the month and 
> > the
> > site of Bedfont, I would then like to repeat the exercise a few cells 
> > along
> > for Hams Hall etc.
> >
> > Then repeat the exercise for month of February.
> >
> > From this I can then have a separate table which would count the number of
> > starters by location and month, and attach a hyperlink to it so that the 
> > HR
> > team can see a listing of how many starters in each month, and use this
> > information to prepare for inductions and other such HR related matters.
> >
> > Any help much appreciated on how to achieve this.
> >
> > Regards
> >
> > Andrew
> >
> > 
> Spreadsheet is already using pivot tables to give me the results I need but was hoping to come away form that option and also not to use autofilter - I unfortunately have staff at other locations who have very basic excel knowledge so have to present the data as simply as possible so that when they open the spreadsheet they can just see the results without having to manipulate the data in any way.

Regards

Andy
> 
> 
0
AndyJ1 (3)
1/4/2008 12:15:01 AM
"AndyJ" <AndyJ@discussions.microsoft.com> wrote in message 
news:363D2794-87C5-4523-B240-965E5F3B6C5D@microsoft.com...
> Spreadsheet is already using pivot tables to give me the results I need 
> but was hoping to come away form that option and also not to use 
> autofilter - I >unfortunately have staff at other locations who have very 
> basic excel knowledge so have to present the data as simply as possible so 
> that when they open >the spreadsheet they can just see the results without 
> having to manipulate the data in any way.

When I was at work I wrote a spreadsheet for people who had very basic 
knowledge.   I put in place holding hidden entries as place holders so that 
the Pivot table would not alter if there was not full data entered, set the 
range bigger than ever would be used and wrote a Macro to refresh the Pivot 
Table.

The staff then just had to add data and then click on a button to get the 
updated result.

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


0
sandymann2 (1054)
1/4/2008 12:32:54 AM
My wife says that I expect people tpo know what I am thinking without me 
telling them.......


I forgot to add that I hid the sheet with the pivot table and had a Results 
sheet for the staff in which the cells were linked to the Pivot table cells. 
The Table in the Staff sheet therefore was set out and formatted to look 
*user friendly* and the staff did not feel intimidated by it.

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" <sandymann2@mailinator.com> wrote in message 
news:uAeJnlmTIHA.280@TK2MSFTNGP03.phx.gbl...
> "AndyJ" <AndyJ@discussions.microsoft.com> wrote in message 
> news:363D2794-87C5-4523-B240-965E5F3B6C5D@microsoft.com...
>> Spreadsheet is already using pivot tables to give me the results I need 
>> but was hoping to come away form that option and also not to use 
>> autofilter - I >unfortunately have staff at other locations who have very 
>> basic excel knowledge so have to present the data as simply as possible 
>> so that when they open >the spreadsheet they can just see the results 
>> without having to manipulate the data in any way.
>
> When I was at work I wrote a spreadsheet for people who had very basic 
> knowledge.   I put in place holding hidden entries as place holders so 
> that the Pivot table would not alter if there was not full data entered, 
> set the range bigger than ever would be used and wrote a Macro to refresh 
> the Pivot Table.
>
> The staff then just had to add data and then click on a button to get the 
> updated result.
>
> -- 
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> 


0
sandymann2 (1054)
1/4/2008 12:43:34 AM
Reply:

Similar Artilces:

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

sort data
i am trying, unsuccessfullty, to sort some data (a small example is below) so I can graph it. I want to put it into a pivot table format but don't want calculations, just the actual data. any ideas??/ Location DATE Result WAT002WA ALBANY BOTTOM 19-Jul-05 0.83 WAT002WA ALBANY BOTTOM 23-Jun-05 0.8 WAT002WA ALBANY BOTTOM 28-May-05 1.16 WAT002WA ALBANY BOTTOM 02-May-05 0.93 WAT002WA ALBANY BOTTOM 06-Apr-05 0.66 WAT002WA ALBANY BOTTOM 11-Mar-05 0.59 WAT002WA ALBANY BOTTOM 13-Feb-05 0.54 WAT002WA ALBANY BOTTOM 18-Jan-05 0.62 WAT002WA ALBANY TOP 09-Mar-07 0.73 ...

Matching call data based on date, time and number called to give c
I have the following two tables on different sheets, both containing other information within them that is irrelevant in my question. SHEET 1 Date - Time - Number Called Duration Cost 01/02/10 09:05:21 01234123456 02:16 0.50 SHEET 2 Date - Time Number Called - Duration Extension 01/02/10 09:07:56 01234123456 02:15 1234 01/02/10 15:30:45 01234123456 01:59 1234 Sheet 2 HAS A LOT MORE RECORDS THAN SHEET 1 AND THERE ARE A NUMBER OF CALLS TO THE SAME NUM...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

data migration of cdf tables to crm
hi, i am trying to implement crm for an organisation. the cdf tables for both accounts and contacts are ready and data is present int he info tables.but the next step of migration is not happening. executing the migration.exe tool shows process completed in the log but verifying the migration_info table shows "not processed" against the corresponding rows.it is not showing any errors but still not migrating. please help. i can use all the help i could get. thank you. "Yamini.P.S" <Yamini.P.S@discussions.microsoft.com> wrote in message news:94FD56E3-3B28-43D...

Appending data in cells that utilize a Dropdown list.
Hello all, I would like to append entries in cells that utilize a dropdown list. Currently, when I select (in this case names) from my dropdown list I cannot append them if I want to add more than one to a cell. If I select another name from the dropdown, it erases the first entry. Is there some way to append entries within cells that use this form of validation so that I can enter more than one in a cell from the dd list? -- Thanks, Geek using Office XP Professional Hi Geek Debra Dalgleish has an example workbook showing how to do this http://www.contextures.com/excelfiles.html...

Removed content in right columns and limited size of spreadsheet
I was trying to get rid of the contents in the columns to the right of my spreadsheet and instead I accidently deleted everything to the right (area is now grey). Now I can't insert new columns because I have limited the size of the spreadsheet. How can I recover columns to the right? I can't just click undo because I have saved and gone out of the spreadsheet. The data in deleted columns is lost I do not understand how you think you have limited the size of the worksheet That is not possible tell us more best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/...

sending data to com port
Hello, I am using VC++ 6.0 (of Visual studio 6.0) How can I send data to com port via VC++ code ? Need sample code, please. Thanks :) See this article on codeproject..it covers up most of the stuff... http://www.codeproject.com/system/serial_com.asp - Parhar "Eitan" <no_spam_please@nospam.com> wrote in message news:uVpJFP87DHA.1804@TK2MSFTNGP12.phx.gbl... > Hello, > I am using VC++ 6.0 (of Visual studio 6.0) > How can I send data to com port via VC++ code ? > Need sample code, please. > > Thanks :) > > See the "CSerial" class from Tom...

queries which pull data in multiple "rows" in one table and compar
I have a project to complete where the end user wants me to write several queries which pull data in multiple "rows" in one table and compare them to data in another using access. He also needs a report written based on the findings of which data meets certain criteria within those queries. I think the report part will be pretty easy, but can someone give me some suggestions on what would be the best way to create the queries in access? Thanks Da Chosen One On Sun, 10 Jan 2010 19:22:01 -0800, Da Chosen One <Da Chosen One@discussions.microsoft.com> wrote: You...

One SOMgr with Four POSs
I have a LAN installation where four seperate stores are each operating SOpts (they want items seperate from each other and managed by each Store Manager). However the CFO(also on LAN) needs to be able to access each store from her desktop. We know how to change Admin's DB but are looking for a more elegant solution. Like bat file called from shortcut before SOMgr - that does the switch??? I also have another Client that runs a lot of reports basied on DB's state as of last night of EOM.bck DB. The above solution may also work them! NcS, Change to the store's database in S...

Data............Group
if I group columns and hide the columns with the + is it possible to give the diferent groups names where they are listed at the top left 1, 2, 3. Not quite sure what you want, but maybe.... View|Custom Views would allow you to hide/show the groups the way you want. Esrei wrote: > > if I group columns and hide the columns with the + is it > possible to give the diferent groups names where they are > listed at the top left 1, 2, 3. -- Dave Peterson ...

display only one formula
i'm taking a computer test and i need to know how to display my formula when it prints so the formula doesn't calculate, but i only need one of the formulas to be shown the rest need to stay in calculated form. How would i do this? Hi Format the cell as Text, select the cell, press F2, and then Enter keys. -- When sending mail, use address arvil<at>tarkon.ee Arvi Laanemets "norcalchick2207" <norcalchick2207@discussions.microsoft.com> wrote in message news:6F223ADF-4546-44C9-8BC8-6D2825872C10@microsoft.com... > i'm taking a computer test and i need ...

Fill text boxes with data from another text box
I have a form that has two sections. Section 1 has orginator name, address, city state, zip. Section two has owner name, address, city, state, and zip. If orginator and owner are the same, I want to just hit a button or check mark to copy data from section one to section two. Can this be done, and how would you do it. Thanks for your help- Porkchop. Porkchop, To copy data from one control (and, hence, field, if the form control source is set to the name of a field in the form's underlying record source), put the following in the AfterUpdate event procedure of the checkbox: If ...

data points #3
I have a scatter chart with the following info : sales price, square footage, and subdivision. With the x and y axis being sales price and square footage. There are some entries where i will have several sales in one subdivision and i would like all those points to show up in the same color. How do i get several points to show up in the same color instead of excel assigning each entry a new label? Include all the related points in the same data series, rather than as a different data series for each point. -- David Biddulph "affordablegreen" <affordablegreen@discussion...

Compile data from several workbooks
I have multiple workbooks for different projects. they are all in separate sub folders. Within each wookbook is a worksheet I call Bug. The Bug sheet pulls the data from that workbook which I need in a separate master workbook. I want to pull the data from every Bug worksheet into my master workbook without having to open each file to force it to update. I can do it cell by cell using a direct link but that is a lot of work. I have compiled a list of each workbook including the path. I can use that path and get the data using =INDIRECT(ADDRESS(2,3,,,F3) where F3 is the path. ...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

Export/Import Acces Table to another Acces table/file
Hi, Is anybody can help me how to export/import table to a new table/file only using 1 click?. I am sure, we'll use Visual Basic, but I don't know how. Thanks in advance, I am appreciated it. KT -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200707/1 I suspect you will need to look at the TransferDatabase method of the DoCmd object. See the Access help. Steve "KimTong via AccessMonster.com" wrote: > Hi, > > Is anybody can help me how to export/import table to a new table/file only > using 1 ...

Recovering Data from MDBDATA
My Server is now not up... As My Global Catalogue Server just crashed. I tried level best to make another but whole effort just was a waste.... Problem is now I have made another server with New Domain. (New One) . How can I retrive mails from MDBData File of the Previus Server. -- Zeshan ------------------------------------------------------------------------ Posted via http://www.webservertalk.com ------------------------------------------------------------------------ View this thread: http://www.webservertalk.com/message1020038.html www.ontrack.com/powercontrols -- John Oliver, Jr...

extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time. The sum totals to more than 24 hours. I have to a cell with a cost per hour and I need to caclculate the total cost. The problem is that the hour function returns values in the range of 0-24. My current sum is 25:30 and the hour function return 1 and not 25. Any ideas how to bypass it? It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will. To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and a day is s...

Repeat data from previous records in a form
I am creating a a form for data entry and want to know if there is a way to repeat data in fields from the previous record (or default to previous record). For example, I user has 100 entries - I would like to set up the form so they do not have to enter their ID each time they enter a record. When you hold the ctrl key and click the apostrophe key ' access copies the contents of the same field from the previous record. This assumes that the new record immediately follows the old record so you may have to sort the source table and requery it after each time a new record is inserted...

data consistency check
How can we perform a data consistency check/fix on the information store and the directory in exchange 5.5 running on a windows 2000 server. Is here any harm in running any tool. Pls advise. Thanks. What is the problem your seeing? -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply to this newsgroup. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "khan" <anonymous@discussions.microsoft.c...

Using validation to make data appear or not
I have an array of cells that all rely on a "Y" or "N" value in another cell, and an IF statement to either make the data appear or not appear. It works fine, except that when the data appears and fill, the borders pop up fine. Some of them, however refuse to disappear the same way the data does, yet others do just fine. This includes cell color formatting, which I am able to make stay or go. I just seem to have a problem with some cell retaining persistent border, which go away completely if I remove them, but that isn't what I want. I want them to beh...