Collating entries from multiple sheets

Dear Experts,
I have 28 worksheets (drug classes) each with a column O in which drug names 
will appear if they meet certain criteria.  I would like to list these drugs, 
from the 28 worksheets on one page, as a summary.  I want excel to look down 
column O, for about 100 rows, and collect any occurrences of drug names.  In 
100 rows drug names may appear 20 times (they will all be unique), the other 
cells being blank.  Can I collect these occurrences on one sheet for summary 
purposes?

regards
Martina
0
Utf
11/24/2009 3:38:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1719 Views

Similar Articles

[PageSpeed] 0

This should give you some ideas:
http://www.rondebruin.nl/copy2.htm

Also:
http://www.contextures.com/xlvba01.html
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm

HTH,
Ryan---

-- 
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"jc132568" wrote:

> Dear Experts,
> I have 28 worksheets (drug classes) each with a column O in which drug names 
> will appear if they meet certain criteria.  I would like to list these drugs, 
> from the 28 worksheets on one page, as a summary.  I want excel to look down 
> column O, for about 100 rows, and collect any occurrences of drug names.  In 
> 100 rows drug names may appear 20 times (they will all be unique), the other 
> cells being blank.  Can I collect these occurrences on one sheet for summary 
> purposes?
> 
> regards
> Martina
0
Utf
11/24/2009 6:14:02 AM
this can be done with pivot table
or simply few worksheet functions. like  a row below column 'O' for counting 
occurances.  sorting all sheets except summary togather.  etc
better if you can post the file it is easier and better.


"jc132568" <jc132568@discussions.microsoft.com> wrote in message 
news:8409F623-5B5E-4720-A3E8-DFF86633AF48@microsoft.com...
> Dear Experts,
> I have 28 worksheets (drug classes) each with a column O in which drug 
> names
> will appear if they meet certain criteria.  I would like to list these 
> drugs,
> from the 28 worksheets on one page, as a summary.  I want excel to look 
> down
> column O, for about 100 rows, and collect any occurrences of drug names. 
> In
> 100 rows drug names may appear 20 times (they will all be unique), the 
> other
> cells being blank.  Can I collect these occurrences on one sheet for 
> summary
> purposes?
>
> regards
> Martina 

0
sajay
11/24/2009 6:30:03 AM
This relatively simple formulas play should deliver the expected results ...
As you posted, target data is within rows 2 to 100 
in col O in each of the 28 drug sheets

In the summary sheet,
List the 28 actual sheetnames for the drugs into AD1:BE1
(A once-off effort, take care to ensure that there's no typos in the 
listing. The names listed must match exactly with what's on the tabs except 
for case, which is immaterial)   

Put in A2:
=IF(ISTEXT(INDIRECT("'"&AD$1&"'!O"&ROWS($1:1)+1)),ROW(),"")
Copy A2 across by 28 cols to AB2, fill down to AB100
[You can hide away/ minimize the criteria cols A to AB]

Then place in AD2:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(INDIRECT("'"&AD$1&"'!O:O"),SMALL(A:A,ROWS($1:1))))
Copy AD2 across to BE2, fill down to BE100*. The expected TEXT results from 
each drug's sheet's col O will appear neatly packed at the top. Voila? hit 
the YES below
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
--- 
"jc132568" wrote:
> Dear Experts,
> I have 28 worksheets (drug classes) each with a column O in which drug names 
> will appear if they meet certain criteria.  I would like to list these drugs, 
> from the 28 worksheets on one page, as a summary.  I want excel to look down 
> column O, for about 100 rows, and collect any occurrences of drug names.  In 
> 100 rows drug names may appear 20 times (they will all be unique), the other 
> cells being blank.  Can I collect these occurrences on one sheet for summary 
> purposes?
> 
> regards
> Martina
0
Utf
11/24/2009 9:50:02 PM
Reply:

Similar Artilces:

Order Entry
Are there any 3rd party order entry pieces out there for our sales/customer service staff. I have heard of something called SalesPad but I know nothing of it. Is that any good? What else is out there? We are looking for something easy to train our users on and cheaper than a GP license. Hi, We have a Excel order entry solution for GP. If you are interested Please drop a email to discuss. Regards, NJ nj.tech@hotmail.com "Ron Wilson" wrote: > Are there any 3rd party order entry pieces out there for our sales/customer > service staff. I have heard of something cal...

Multiple Auto Signatures
Hi All Does anybody know a way in which I can get Outlook to prompt for which autosignature to use on a message by message basis. I have a number of users that have multiple roles within the business and the signature needs to change depending on who they are sending the mail message to. Any ideas gratefully received!!! Thanks Ray T Ray Thomasson <rthomasson@lorica.com> wrote: > Does anybody know a way in which I can get Outlook to prompt for which > autosignature to use on a message by message basis. Prompt? Not that I know. > I have a number of users that have m...

Back up sheet
Hi, I have deleted an excel sheet and closed the file. is there a way where i can restore that sheet or does excel back up copies of deleted sheets. M.Thanks Regards >>I have deleted an excel sheet and closed the file If you didn't save the file after deleting the sheet, it will still be in the workbook. Otherwise you have to look to a backup copy of your workbook. Always(!) keep backups of important workbooks. -- Jim Rech Excel MVP "Mohamed" <Mohamed@discussions.microsoft.com> wrote in message news:2DE834FF-DF42-47AF-9B6C-A8A0264244A7@microsoft.com... | Hi...

SQL Reporting Services "From" and "To" date parameter entry slowness / hesitation
I=92ve got a few reports that utilize =93From=94 and =93To=94 date paramete= rs (along with some others) The date parameters default to last months=92 start and end dates which is fine. However, if you want to report for a different month, and you proceed to change the =93From=94 date, as soon as you get through entering a new date in the field, the report appears to refresh (even appears to run although it doesn=92t really), there is a bit of hesitation, then it finally refreshes again, then you can then enter the =93To=94 date and hit the View Report button. It would be much ...

How to apply AutoArchive settings to multiple Exchange users?
Hi, Is there a way to apply AutoArchive settings to multiple users without having to manually go to every workstation and change the user's Outlook settings? I'm running Outlook 2000 with Exchange 5.5. Thanks, Tyler ...

Sheet deletion
Hi, I am using excel 2003. When I delete a workbook sheet, it doesn't prompt me to delete, it automatically does it. How can I have excel prompt me for workbook sheet deletion? Thanks. Mitch Mitch, Somehow, the DisplayAlerts property got set to False. Open the VBA Editor (ALT+F11), then open the Immediate Window (CTRL+G), and enter the following: Application.DisplayAlerts = True and press Enter. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "mitch" <mitch@discussions.microsoft.com> wrote in message news:16...

When Using a PropertySheet in Wizard Mode Can we Change the Order of sheets at runtime
Hey Friends I am creating a Wizard Using Property Sheet According to User Selection of Data in First Page I want that only one of the PropertySheet should be visible with a Fnish Button But I have added 4 More Property Pages Whereas i want only one of them to appear. Take a look at the CPropertySheet::SetActivePage() member function. -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "vikrant" <vikrant_kpr@hotmail.com> wrote in message news:OaKnxZauDHA.3532@TK2MSFTNGP11.phx.gbl... > Hey Friends > > I am creating a Wizard Using Prop...

Show data on a Visio Sheet from Exce
I am trying to design a corporation flow chart to where i can click on a shape and have it show all the details of a specific corporation (each shape representing a different corporation and thus a different set of data). Is this possible? -- bob74 As a consultant we try to stay away from 'globals and absolutes' ("all the details", "the best solution"). That said you might check this out. http://office.microsoft.com/training/training.aspx?AssetID=RC101977211033 al "bob74" <bob74@discussions.microsoft.com> wrote in message news:...

duplicate entries #2
I have a rather large spreadsheet of Names, addresses, etc. I have noticed that there are many duplicate entries. any suggestions or formulas that would allow me to highlight the duplicates preferably in color, would be much appreciated. Please keep in mind, that I am a beginner with excel, so I need something simple and uncomplicated.......Thanks microsoft.public.excel "Richard Mahan" <dmachen35@comcast.net> wrote in message news:KoydnTFn2LdprOrZRVn-qA@comcast.com... >I have a rather large spreadsheet of Names, addresses, etc. I have noticed >that there are...

Sheet vs File size when sending email in Outlook
In a workbook with a single sheet, if I use "File, Send to, Mail Recipient" the recipient gets an email larger than 3MB, but if I choose "File, Send to, Mail Recipient (as Attachment)", they get an email less than 1MB. I prefer the former as the recipient gets a message with the info in the message body, rather than an attachment, but the file size (arriving in Outlook) is becoming and issue. The excel formatting also needs to be retained. Suggestions? Many thanks, Mike :confused: -- matchball ------------------------------------------------------------------------ ...

Multiple companies / hosted exchange environment
Hello, We operate a very small hosted exchange environment for a few small businesses and one of them would like us to provide MS CRM for their usage. They have looked at the documented options of running their own SBS Server with CRM but they have no support staff themselves and would like to keep their Exchange services as they are and if possible bolt on CRM. I know there would be no problem installing CRM in our infrastructure, but would there be any limitations as to the hosted-exchange side? Also at a later date could we add additional companies to CRM if another of our customer...

Creating a set number entries
I have made a worksheet that will give me a work order number followed by a sequential number. I have created the output so that it fits onto a sheet of labels. I can get 30 labels to a page, but in order to do 5 pages of labels I will have to re-enter my starting number 5 times. It there a way to set up my worksheet so I can tell it I want to start at 1 and end at 150 and all my entries will be there. No more or no less. The number may be different every time and I don't want to have the person who is going to be using this to have to think about what she has to do. What I have so f...

How do I view two sheets with duel monitors?
I run with duel montiors, and am trying to setup excel so that I can view one spreadsheet one on monitor, and another spreadsheet on the second monitor. How do I set this up? Check out this link by Chip http://www.cpearson.com/excel/MultipleMonitors.aspx -- Jacob "Gorte McGinty" wrote: > I run with duel montiors, and am trying to setup excel so that I can view one > spreadsheet one on monitor, and another spreadsheet on the second monitor. > How do I set this up? You are also able to maximize on two screens and view on two screens by opening up ...

Windows user name from GAL entry
How do I easily retrieve the Windows user names for a list of Outlook email recipients? I am not an administrator; the names are required to set up permissions in another ERP application. Ours is an Exchange server environment with Active Directory. -- Sriram ...

Mails delivered multiple times a day
Hi I�ve got the following problem hoping somebody here knows what can be done about it. There are two users on our exchange server (exchange 2000,service pack3) that get each one email multiple times a day. One email is a "deleted without being read" status notification to the user, the other one is a meeting request Let�s concentrate on the status notification. I paste a copy below (Please note the "xyz" I habe put in) -----Urspr�ngliche Nachricht----- Von: /O=XYZ/OU=GERMANY/CN=RECIPIENTS/CN=NKHELIFA Gesendet: Freitag, 3. September 2004 10:52 An: Schaika Volker Bet...

Email to multiple Exchange recipients homed on direct exchange ser
If an Exchange user sends an email to multiple recipents homed on different Exchange servers in the same organizations, are duplicate copies of the email stored on each Exchange server? Or are there only pointers to a single copy regardless of recipients being homed on different Exchange servers. We are contemplating adding a 2nd server or going to enterprise version. But if we will accumulate duplicate internal email, two servers does not sound like good option. Single Instance Storage is maintained on a per-mailbox store (database) basis only. So even if you have multiple mailbox ...

Excel file with 52 sheets
I have received a file that was protected by sheets, means that wvery single sheet is protected, I got the password, but just wondering if there is a macro I can use to unprotect every single sheet the protect it again. Thanks in advance for your help Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Protect Password:="justme" 'adjust pword Next n Application.ScreenUpdating = True End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To S...

FRX Balance Sheet
I have the following scenario: Multiple companies (different databases) in different currencies with different financial year periods (i.e. one ending 31 Dec, others ending 30 June). I have sucessfully done a group PL using the rollup basis on dates option - but this option is not working for my Balance sheet layouts. i suspect it is related to my YTD logic. Has anyone else successfully done a group Balance Sheet in the same scenario? ...

excel personal sheet
hi all,for some strange reason when i open a new excel workbook it opens in a new personal sheet.i can't figure out why its doing this.any help is greatly appreciaited. thx wynb -- wynb Poster stated problem was resolved but said so in another thread. "WYN" <WYN@discussions.microsoft.com> wrote in message news:F4EA559D-92FB-4353-9120-073DDE0C1879@microsoft.com... > hi all,for some strange reason when i open a new excel workbook it opens in a > new personal sheet.i can't figure out why its doing this.any help is greatly > appreciaited. > thx wynb > --...

Problem sending mail to Multiple recipitants
Since i have applied all the latest security updates & service packs to Office 2000, i have a problem sending mails to 400+ recipitants at one time. I receive a no transport provider availible message as soon as the mail is sent. I originally thought it could be a bad contact within the address list, but i have been able to send the mail to all the recipitants by sending it out in blocks of 50 - 100. Is there something within the latest updates or service packs that limits the amount of recipitants you can send to at one time if so is there a way round it. Stephen Lane <steph...

Multiple Hotmail accounts in Outlook02 : possible?
I tried a search on this without any luck... ::I've been trying to add a couple more hotmail accounts to b synchronised in outlook2002, but it can't access them.:: I've currently got my hotmail account, which works in Outlook, and POP3 account which also works. When I try to add my wife's hotmail account or a second hotmail accoun of my own (which can both be accessed from internet explorer without problem)outlook cannot recognise them. I had this problem with Outloo Express as well. I think I have all the settings configured correctly, (like SPA is NO ticked in the acc...

VLOOKUP formula searching multiple worksheets ??
Does anyone know how to search for info from multiple Excel worksheets using the VLOOOKUP formula or any other formula. I have my VLOOKUP formula(column B) and list of values(column A) (sorted in ascending order) on Worksheet 7 and I need to search for these values in Worksheets 1,2,3,4,5& 6 Column F. Is this possible with Excel ? I've tried this by using the formula VLOOKUP(A2,Wrk1:Wrk6!$F:$G,1,FALSE) but I get a #VALUE! error. You will need a construct along these lines: =IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)), IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)), IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2...

Multiple Record Inputs with one Form
I'm programming with SQL and can't seem how to figure out how to send more than one record to the table with one form. You've described a "how" ... as in how you are trying to do something ("send more than one record to the table with one form"). Now, "why" ... as in what will having multiple records generated from a single form allow you to do (i.e., business need)? This isn't idle curiosity ... if we knew more about the underlying business need, we might be able to offer alternate approaches. Regards Jeff Boyce Microsoft Office/Access ...

how to merge data from multiple columns to one column
I have first, middle, and last name in different columns. How do I merge these three into one column? Try... =A1&" "&B1&" "&C1 for the format John T. Smith OR =C1&", "&A1&" "&B1 for the format Smith, John T. Hope this helps! In article <24D1088D-0AB1-4910-BF0E-37D8D7A4C056@microsoft.com>, "w8ting4hlp" <w8ting4hlp@discussions.microsoft.com> wrote: > I have first, middle, and last name in different columns. How do I merge > these three into one column? ...

Ability to enter multiple meter readings per day in field service.
Would like to have the abiltiy to enter multiple meter reading per day in fields service instead of being limited to just one entry per day. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-u...