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
1555 Views

Similar Articles

[PageSpeed] 59

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:

VLOOKUP and multiple columns
Here's a question for an assignment for my computer info systems class. I have a table posted below in cells A4:E13 Credit National National National National Rating Area Area Area Area Score 111 112 113 114 300 3.00% 3.00% 3.00% 3.00% 325 3.25% 3.25% 3.25% 3.25% 350 3.50% 3.50% 3.50% 3.50% 475 4.25% 4.50% 4.75% 5.00% 550 4.50% 4.75% 5.00% 5.75% 650 5.50% 5.25% 6.00% 6.50% 750 6.50% 5.75% 7.00% 7.25% Shipping Rate Per Pound Shipped is $4.00 (4 is placed in cell E16) B21:G25 for this partial example of the table... Credit Shipping Total Total Customer Rating Destination Weight to D...

Hyperlinks to sheets in a workbook
I inherited a workbook with 20 tabs, that has hyperlinks on the firs tab to most of the other tabs. I have added a new tab, and change the name of one of the tabs. However, I cannot figure out how t make the hyperlinks work for these two pages. When I do the "Edi Hyperlinks" procedure I get a list of the tabs in the workbook --- bu not all of them . . . so I need to know how to get my tab names to sho on that list. I've tried reading some of the other postings, but none of them dea with my problem. I am not familiar with VBE (?) programming . . . and wouldn't kno...

Viewing Calendar Entries as a List
Hello, I am trying to find out how I can view my calendar entries as a list. I would like to be able to filter this list by names of participants and names of the organizers. "Rob in France" <Rob in France@discussions.microsoft.com> wrote in message news:4F8B83C9-F56C-49BB-AB5D-96215E297981@microsoft.com... > Hello, I am trying to find out how I can view my calendar entries as a > list. > I would like to be able to filter this list by names of participants and > names of the organizers. Tried View-Current View-Events? You need to change the vi...

Collating stock market data
Dear Friends I have 1 blank excel file and around 200+ text files in the folder c: \stock. Here is a sample data in each text file. Name of the file PARAMOUNT.TXT PARAMOUNT.NS,20100226,09:01:00,521.00,521.40,517.20,519.00,1677 PARAMOUNT.NS,20100226,09:02:00,520.00,520.90,520.00,520.00,1872 PARAMOUNT.NS,20100226,09:03:00,519.25,519.95,518.00,518.70,1783 PARAMOUNT.NS,20100226,09:04:00,518.00,518.00,516.05,517.50,859 PARAMOUNT.NS,20100226,09:05:00,517.50,518.00,516.70,516.70,1466 PARAMOUNT.NS,20100226,09:06:00,517.80,519.00,517.00,519.00,1191 Is it possible to write a macro tha...

Vlookup within multiple ranges
How do I do the following in Excel 2003? I have a column of six digit numbers (About 24,000 Rows) Example: 154563 345678 843565 .... I need to associate each one of those numbers to the following parameters: Column A, Column B, Column C From, To, Equals 221100, 221199, "A" 443000, 443999, "B" 511110,511119, "C" ..... If the number does not fall between the given sets it should show up as "#N/A" or "Other" I know how to do a vlookup by only using the mininum number of the set and setting it to "TRUE" but I am finding it is assoca...

Collating
I am using Publisher 2003 (part of the Office Suite), Windows XM Media Edition. I am printing a 10 page newsletter and I cannot get the pages to collate. YES, I have checked the collate box on the printer screen. Is there something else I need to do? I have taken the exact same newsletter and have redone in Word 2003 and the printer will collate the ten pages. All help is appreciated. Mike Look for collating options in your printer driver, or attempt to update your printer driver. -- Brian Kvalheim Microsoft Office Publisher MVP Official Publisher MVP Site: http://www.kvalheim.org Th...

Marco Entry with Text
Thanks for the input on post dated 08/16/05. Here is more detail. Let's say as an example I am using column 1, rows A-Z and Column 2 rows A-Z I want to start in A1 enter data then have it move to A2, enter data, then b1, etc. But in some of the fields I have drop down options. I am somewhat new to macros - in the past I have used Lotus and in those macros you could build the macro to allow input i.e. "??" would represent data to be entered. If you are interested in assisting with this, please email me at nnoonchester@yahoo.com and I would be happy to forward a samp...

code to create new entry in linked table
hey everyone, here's my problem: I have 2 tables. one contains a list of serial numbers for cd's, along with other information. the second table is linked to the first, and contains the serial and the number of spare copies I have. (i cannot incorporate this into the first table) I have a form who's source is a query which has all the fields from the first table plus the extracopies from the second table. I have a + and - button beneath the extracopies field, to increase or decrease this number. What I want to do, is if I have no entry in the second table for a serial nu...

Timecard Table
Hi Does anyone know what triggers the entries in the Timecard table. There are entries in there but not for every time a user logs in. The entries seem to be days apart in some cases. I know how the entires in the Timeclock table works - but cannot figure the Timacard table. many thanks Greg Timecard entries aren't associated for everytime a cashier logs into the system. the Timecard entries are when the cashiers clock in and clock out. if a cashier clocks in and doesnt clock out, then the entries would be days apart. "Morcott" wrote: > Hi > Does anyone know ...

Multiple page orientation in excel
Hi, is it possible to have a portrait and landscape simultaneously displayed in a worksheet. i want to be able to view portrait and landscape, i can achieve this features in a word document, but unable to do so in excel 2000 environment. olumorock27:o ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Hi, no but there are workarounds, you could create a copy of the sheet, right click sheet tab, select move copy, check create copy and copy it for instance to the end ...

Graph subreport prints multiple times in Report
I have created a database report that filters all records matching one unique field. I have created a subreport (a graph) that compares the results from that one unique field to all the other unique values in that same field. My problem is, as I add unique values to the filtered field (and thus expand the number of values being graphed), the report prints the same graph over and over again until the number of times the graph prints equals the number of unique values in the field. How do I get the graph to print only once? It's difficult to determine your issue without know...

userform commnad from a sheet command
Hello I have the following piece of code in Excel sheet 1 to trigger an event when a cell content changes Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then 'This Code Runs When Cell A4 Changes' End If End Sub What I want to do is insert here code to interrupt the running of a particular userform and goto a particular point and resume that same userform. So something like Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then userform1.stop goto userform 1line 220 userform1.resume End If End Sub ...

Why and When to use COLLATE?
Hi, I have never used COLLATE option before. But when I am searching for some thing online I came across the below Table creation definition. What does the 'COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL' mean. CREATE TABLE [dbo].[ADSI_PARAMETER] ( [ParameterName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ParameterValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO I went a head and created the same table in Management Studio by right clicking on the Table and picked New and just created it as usual with out...

how to unit data in different sheet into one sheet?
I have same style data in differet sheet: sheet1 q ww w ww e r r e t r (and adding) sheet2 5 f 6 g d h e h g juh (adding) and in sheet3 I want automatic display: q ww w ww e r r e t r 5 f 6 g d h e h g juh any solutions? One play via non-array formulas .. In Sheet1, data is assumed in cols A & B, from row1 down Use an empty col to the right, say col E Put in E1: =IF(COUNTBLANK(A1:B1)=2,"",ROW()) Copy E1 down to say, E100, to cover the max expected data in cols A & B In Sheet2, data is assumed in cols A & B, from row1 down Use an empty col to the right, say col E...

While linking to other work sheets
Hello, I am creating an EXCEL sheet where in each of 4 sheets in that corresponds to data in sheet1 of a 4 seperate work sheets. I am able to link the other sheets and getting the data from the other work sheets also..But where ever there is a blank cell is there , i am getting a zero. I don't want to appear un necessary zeros in my consolidated work sheet. I tried to use the option from TOOLs -->options-->view and removed the check against zero values.. This way we can't see the zero values but it occupies the space. so the memory of the sheet is getting increased as i am goi...

Data entry forms
Is it possible to create a data entry form in outlook (2003 or 2007) where the user could fill-in fields... and then send it back to the originator? If so, how? What is it called in outlook so I can look it up further? QB You can use Custom Forms for this or use InfoPath if you have that installed for your users as well. A good resource to get you started with custom forms and VBA see; http://www.outlookcode.com -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more h...

How do I count items based on multiple criteria from a different worksheet?
I have tried {=SUM(('10-16-2005'!F:F="Brentwood") * ('10-16-2005'!B:B="ACTV"))} (entered with F2, then cntrl/shift /return, but it is not returning what It should. What I would REALLY like to do, is if the first letter of column B (STATUS) starts with a "A", "B" or "N" .AND. Column F (City) = "Brentwood" .AND. Column J (BT) = "DE". All of these are on Worksheet 10-16-05 (or preferrably, what ever the column header is on SHEET1, row1, column()) Thanks! Mc Here is a formula =SUMPRODUCT((ISNUMBER(FIN...

Vlookup Multiple Returns_Answers
I am using excel as a scheduling software for construction. I have certain phases that may take 1 or more days. I am trying to type in the job number and have it return all the days each phase is scheduled on. I currently use the following Vlookup to find the first day: =VLOOKUP($E$5&$B11,Data!$E$1:$K$16200,4,FALSE) e5=Job Number b11=Phase Type Data!$E$1:$K$16200= the table_array I am pulling from. 4=the co-_index_num How can I have it return every match for this job number and phase type, with each match in a seperate row under each other? -- Thanks, Ben Hi! Here's one way....

How can I view multiple pages other than print preview
How can I view multiple pages of my document without resorting to print preview. You can't... You will have to resort to print preview. You could setup each page in a different publication (copy/paste) and tile the pages.(Right click the Quick Launch bar). -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Dave" <Dave@discussions.microsoft.com> wrote in message news:DDC274A0-96EA-44F8-B4CB-D0B4529CCED0@microsoft.com... > How can I view multiple pages of my document without resorting to print > preview. ...

Change database collation during redeployment
We have a test/stageing area that we will move to a production area using the redeployment tool . During redeployment you take a backup of the current system and then restore the databasen on the new system. Is it possble to change the database collaction during backup/restore. We need to change the default collaction to Finnish_Swedish_CI_AS. // Mats ...

merging over multiple pages
This might be a basic question, but is it possible to merge over multiple pages (like in word)? I couldn't find any merge rules like in Word (i.e. <<Next Record>>). I want to create a catalog that (A5 format on landscape A4 pages) would run over multiple pages and be able to print the result back to back correctly so that the pages can just be stapled together and folded book style and read p1, p2, p3 etc. Is this possible? Kind Regards Damo What you have described is the way a catalog merge works. Setup your publication as a booklet, use the catalog merge. Lots of hel...

combining data from two separate sheets
hello all I would like to combine some data, here is what I ahve Sheet 1 email | webpage fdsf | AAA aasd | BBB rtew | DDD yuui | EEE Sheet 2 name | address | city | webpage dff | 123 fe | fds | AAA ggfd | 543 re | erw | BBB dfsf | 34 ffd | aawe | CCC ghh | 3424 fd | sfty | DDD jkkl | sdfs | sgdg | EEE of course the names, address, are just junk examples. The number of emails and webpages in Sheet 1 outnumber those in Sheet 2, but the primary information which I need to match belongs to...

Excel 2003 - VBA - Error
Hi Guys: I have a spread sheet where I insert about 42 columns and shift right. This worked for a while, then I started getting an Error "Cannot Shift Objects Off Sheet" . I did find a button that had been shifted so I deleted it. Not change, still got the error. I then selected all cells and deleted. Still no change. Does anyone have any idea of what is happening here? Craig Maybe... XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769 Remember to look for comments and merged cells. Craig Brandt w...

multiple task entry
I would like to add the same task underneath multiple headings in my project file. For example. I have many many jobs. Each job is split into sub tasks. Is there a way to add a new generic Task 4 to each Job automatically. ie. without maunally copying and pasting each row? Job 1 (heading) Task 1 Task 2 Task 3 Job 2 (heading) Task 1 Task 2 Task 3 My file has many many thousands of jobs and the directory structure is more complex than this example, however I am hoping to adapt any advise I get. Many thanks! MS EXCEL and MS WORD are good text editing...

multiple accts in Outlook, how do i set the FROM address when composing?
I have about 5 accounts set up in Outlook. When I compose a new email I want to set the FROM address to be from account of my choosing. Is it possible? Do I need an add-in? I don't want to change the "default" every time. Tom What version of Outlook are you using? You should have an Accounts dropdown to choose the sending account if you're using one of the later versions of Outlook. <thomasamillergoogle@yahoo.com> wrote in message news:1123170480.968995.118130@g49g2000cwa.googlegroups.com... >I have about 5 accounts set up in Outlook. When I compose a new em...