Extract Information To Another Worksheet

Hi All,
I have a worksheet with Colonm Headings and Data below each heading, except 
the "Column A" (in some cases it has and in some cases it doesnt). I want to 
extract all rows where there is data in "Column A" to a separate worksheet. 
The Entire Row should be copied to the new Worksheet once i enter a value in 
Column A for each record and the new sheet should be frequently updated from 
the original list, its like a building list.
-- 
M Imran Buhary
0
ibuhary (25)
4/1/2007 8:20:01 AM
excel 39879 articles. 2 followers. Follow

7 Replies
308 Views

Similar Articles

[PageSpeed] 38

In a separate, helper,  column in row 2 enter:

=--ISBLANK(A2)

and copy down

Then switch on Autofilter and set this new column to display only 0
Then copy the visible rows and paste them to your separate worksheet
Then remove Autofilter in the original sheet

-- 
Gary''s Student - gsnu200713
0
GarysStudent (1572)
4/1/2007 12:12:01 PM
One formulas play which makes it dynamic as required

Assume source data in Sheet1, cols A to C, from row2 down. Col A is the key 
col, as per post

In Sheet2,

Paste the same col headers into B1:D1

Put in A2:
=IF(Sheet1!A2="","",ROW())
Leave A1 blank

Put in B2:
=IF(ROW(A1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROW(A1))))

Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of 
data in Sheet1, say down to D200. Hide away col A. Cols B to D will return 
the required results, ie only the lines where col A in Sheet1 is not blank, 
all neatly bunched at the top. When you update in Sheet1's col A, Sheet2 
will reflect it automatically.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"M Imran Buhary" <ibuhary@hotmail.com> wrote in message 
news:DB391BEA-5080-4C03-B3E0-72F21DB849C3@microsoft.com...
> Hi All,
> I have a worksheet with Colonm Headings and Data below each heading, 
> except
> the "Column A" (in some cases it has and in some cases it doesnt). I want 
> to
> extract all rows where there is data in "Column A" to a separate 
> worksheet.
> The Entire Row should be copied to the new Worksheet once i enter a value 
> in
> Column A for each record and the new sheet should be frequently updated 
> from
> the original list, its like a building list.
> -- 
> M Imran Buhary 


0
demechanik (4694)
4/1/2007 12:51:56 PM
Thanks a lot Max.
That was really impressive and helpful.
keep up the good work.
Regards,
Imran.
-- 
M Imran Buhary


"Max" wrote:

> One formulas play which makes it dynamic as required
> 
> Assume source data in Sheet1, cols A to C, from row2 down. Col A is the key 
> col, as per post
> 
> In Sheet2,
> 
> Paste the same col headers into B1:D1
> 
> Put in A2:
> =IF(Sheet1!A2="","",ROW())
> Leave A1 blank
> 
> Put in B2:
> =IF(ROW(A1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROW(A1))))
> 
> Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of 
> data in Sheet1, say down to D200. Hide away col A. Cols B to D will return 
> the required results, ie only the lines where col A in Sheet1 is not blank, 
> all neatly bunched at the top. When you update in Sheet1's col A, Sheet2 
> will reflect it automatically.
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> --- 
> "M Imran Buhary" <ibuhary@hotmail.com> wrote in message 
> news:DB391BEA-5080-4C03-B3E0-72F21DB849C3@microsoft.com...
> > Hi All,
> > I have a worksheet with Colonm Headings and Data below each heading, 
> > except
> > the "Column A" (in some cases it has and in some cases it doesnt). I want 
> > to
> > extract all rows where there is data in "Column A" to a separate 
> > worksheet.
> > The Entire Row should be copied to the new Worksheet once i enter a value 
> > in
> > Column A for each record and the new sheet should be frequently updated 
> > from
> > the original list, its like a building list.
> > -- 
> > M Imran Buhary 
> 
> 
> 
0
ibuhary (25)
4/2/2007 7:50:01 AM
Hi Buddy,
I think Max gave me the answer I was looking for.
I wanted to do this without the filtering machanism. And auto updation too.
So, thanks a lot for your time and effort anyway.
keep up the good work Experts....
Regards,
Imran.
-- 
M Imran Buhary


"Gary''s Student" wrote:

> In a separate, helper,  column in row 2 enter:
> 
> =--ISBLANK(A2)
> 
> and copy down
> 
> Then switch on Autofilter and set this new column to display only 0
> Then copy the visible rows and paste them to your separate worksheet
> Then remove Autofilter in the original sheet
> 
> -- 
> Gary''s Student - gsnu200713
0
ibuhary (25)
4/2/2007 7:52:01 AM
Welcome. Glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Apr 2, 3:50 pm, M Imran Buhary <ibuh...@hotmail.com> wrote:
> Thanks a lot Max.
> That was really impressive and helpful.
> keep up the good work.
> Regards,
> Imran.
> --
> M Imran Buhary

0
demechanik (4694)
4/2/2007 10:42:42 AM
Hi Max,

You gave me this tip long time ago. Sorry to disturb you again in this one.
Suppose in one column i have maturity dates, Using the same formulas you 
gave me earlier is it possible reflect the information automatically sorted 
by the column which has the dates?
How do I do this? 
-- 
M Imran Buhary


"Max" wrote:

> One formulas play which makes it dynamic as required
> 
> Assume source data in Sheet1, cols A to C, from row2 down. Col A is the key 
> col, as per post
> 
> In Sheet2,
> 
> Paste the same col headers into B1:D1
> 
> Put in A2:
> =IF(Sheet1!A2="","",ROW())
> Leave A1 blank
> 
> Put in B2:
> =IF(ROW(A1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROW(A1))))
> 
> Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of 
> data in Sheet1, say down to D200. Hide away col A. Cols B to D will return 
> the required results, ie only the lines where col A in Sheet1 is not blank, 
> all neatly bunched at the top. When you update in Sheet1's col A, Sheet2 
> will reflect it automatically.
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> --- 
> "M Imran Buhary" <ibuhary@hotmail.com> wrote in message 
> news:DB391BEA-5080-4C03-B3E0-72F21DB849C3@microsoft.com...
> > Hi All,
> > I have a worksheet with Colonm Headings and Data below each heading, 
> > except
> > the "Column A" (in some cases it has and in some cases it doesnt). I want 
> > to
> > extract all rows where there is data in "Column A" to a separate 
> > worksheet.
> > The Entire Row should be copied to the new Worksheet once i enter a value 
> > in
> > Column A for each record and the new sheet should be frequently updated 
> > from
> > the original list, its like a building list.
> > -- 
> > M Imran Buhary 
> 
> 
> 
0
ibuhary (25)
6/22/2008 7:05:00 AM
Hi Max, I added you in my yahoo mail chat. I need some help on this function 
you suggested me below. Can we have a chat. please add me on your yahoo 
ibuhary@yahoo.com

in your answer below as you have designed it the data is sorted based on 
numbers in Column A of sheet 1. Can I sort the data based on a different 
column, e.g I have dates in another column and I need to stack the data in 
sheet 2 based on this. Hope you understood my question.

Thanks & regards, 
-- 
M Imran Buhary


"Max" wrote:

> One formulas play which makes it dynamic as required
> 
> Assume source data in Sheet1, cols A to C, from row2 down. Col A is the key 
> col, as per post
> 
> In Sheet2,
> 
> Paste the same col headers into B1:D1
> 
> Put in A2:
> =IF(Sheet1!A2="","",ROW())
> Leave A1 blank
> 
> Put in B2:
> =IF(ROW(A1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROW(A1))))
> 
> Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of 
> data in Sheet1, say down to D200. Hide away col A. Cols B to D will return 
> the required results, ie only the lines where col A in Sheet1 is not blank, 
> all neatly bunched at the top. When you update in Sheet1's col A, Sheet2 
> will reflect it automatically.
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> --- 
> "M Imran Buhary" <ibuhary@hotmail.com> wrote in message 
> news:DB391BEA-5080-4C03-B3E0-72F21DB849C3@microsoft.com...
> > Hi All,
> > I have a worksheet with Colonm Headings and Data below each heading, 
> > except
> > the "Column A" (in some cases it has and in some cases it doesnt). I want 
> > to
> > extract all rows where there is data in "Column A" to a separate 
> > worksheet.
> > The Entire Row should be copied to the new Worksheet once i enter a value 
> > in
> > Column A for each record and the new sheet should be frequently updated 
> > from
> > the original list, its like a building list.
> > -- 
> > M Imran Buhary 
> 
> 
> 
0
ibuhary (25)
2/17/2010 10:01:01 AM
Reply:

Similar Artilces:

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...

transferring data in rows of one table to columns of another table
Hi All, We are working with FCC station data that puts some simple numeric data in one file, arranged in a number of consecutive rows for each station. The next station's data follows consecutively. Each group of rows that are common to a station share an index number, while the next group uses its own separate number. A separate file contains the main information fields in a table of separate rows, or records, along with a matching index number. We'd like to move the numeric data in a group of rows that share the index # for a station, to a series of new fields add...

Making a worksheet and a graph that doesn't show zero values
Hi I am trying to build a template worksheet for my research. I get a file of logged data per day. I want to be able to copy the logged data from a .CSV file to a template file. Then separate worksheets will look at the raw data and import data from certain columns into that worksheet. The data is not logged in a regular way, there can be 30 cells between logging intervals or 29 or 31 cells. I can select that every cell in a column of Worksheet 1 looks at a set cell in the "Raw Data" worksheet. This will then give me a column consisting of logged data values with zeroes ...

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? ...

Excel opening blank or Gray worksheet
I have 50 spreadsheets, one for each doctor, that was created with what looks like some macros in them. When a nurse opens the spreadsheet, the workbook is gray looking as if there is no data. However if they click Window - Arrange and OK, then it tiles the worksheet and it's fine. While this is an easy work around, I am attempting to figure out what causes this. I understand that the original owner created macros in the spreadsheet and then copied a template to create these 50. I also understand that there was probably a personal.xls create to save and hide the macros. What ...

Copying and Pasting---Another question...
The original question is at the bottom, but I was still struggling with it so this is more information in the hopes that someone else can help me. Thanks! I'm having trouble with the formula.... If my worksheets are: Apr. 3 May 5 March 13 and I want cell B14 from every worksheet to all appear in a new worksheet. What would my formula look like??? Thanks for your time and assistance!! "Gord Dibben" wrote: > Tia > > You refer to "tables" in a workbook. > > Do you mean "worksheets" in a workbook? > > If the latter, how are the...

Schedule+ Free Busy Information
I have inherited an Exchange Server that has some configuration issues with the public folders...most notibly the Schedule + Free Busy Information object. This server was originally on Exchange 5.5 and migrated to 2000. Additionally, the Exchange server was moved to a new domain. Long story short, I'm getting Event ID 8207 repeatedly in the server application log Error updating public folder with free-busy information on virtual machine <<servername>>. The error number is 0x80004005. I ran ADSI Edit to view detailed information on the public folder objects and found t...

How do I separate address information from within an Excel cell?
I have a Christmas Card list that was created using a single cell for the whole address. Street, City, Zip/Postal etc. I need to split those components out into separate cells in order to do a proper mail merge. Is there any way to do this. I am not completely unfamiliar with macros but I if that is the solution I would require a fair bit of hand holding. Thanks S. If you actually used commas to separate the fields (and none of the fields contain commas), you could select the column and do: Data|text to columns delimited comma Remember to keep enough open columns to the right s...

drawing information from the old hard drive into Vista
I'm wondering how I can get access to all my stuff in the old hard drive. The computer is dead but I have an external drive where I have put the old drive in there. It seems like Vista is preventing me from having access to files where my emails are stored. Is there a way to get to them and work around the security issues? Thanks for your help Sorry, this newsgroup is for questions about Access, the database product that's part of Office Professional. I'd suggest reposting your question to a newsgroup related to Vista, such as microsoft.public.windows.vista.general or ...

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(" ",...

Deleting information
Hi All HYCH Am looking for some assistance with the following, i use a form to enter details about individuals, this enters the info into a column at the end of the line and then sorts into alphabetical order. no problem with this, But, I would like to use a form to select an individual from anywhere in the range and delte their details, every name is range from D3:EU3 (Combo box initialises this ok) And the data is listed under each person down a column, is there someway to adjust the code below to allow this to work?? This section works fine!!! Private Sub UserForm_Initialize() Dim ...

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 ...

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 ...

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...

coping an appointment to another calendar
I just switched from a gmail account to a google apps account. When I did so, and installed the google apps sync program, it opened up and installed another outlook into my computer. It transfered all of my calendar/contacts and mail into the "new" outlook, and everything seems to be working fine EXCEPT: When I try to click and drag or edit/copy to folder one appointment into one of the other calendars under "my calendars" I get the error message below: The folders you are trying to change do not support this operation. Could not complete the operation ...

Reformatting information in a cell
I am trying to type 8/58 in a cell. This is exactly how I want it to appear. Excell keeps changing it to Aug-58. This is not what I want. I have tried to format the cell as general but it keeps chaning to custom. Preformat the cells to Text or precede the 8/58 with an apostrophe. '8/58 Gord Dibben Excel MVP On Thu, 16 Sep 2004 18:21:03 -0700, mvan24 <mvan24@discussions.microsoft.com> wrote: >I am trying to type 8/58 in a cell. This is exactly how I want it to appear. > Excell keeps changing it to Aug-58. This is not what I want. I have tried >to format the ce...

Can I extract unique cell values from every nth column?
I have a range that covers B5:XA160. For each row, data is entered in 11-cell groups: date, some numbers, description (a text value), more numbers, and more numbers. At first it was enough to merely count how many times certain descriptions appeared, because those were the only ones we would see - or so the story went. Now, I need to extract the unique descriptions AND provide a count! Actually, I'm cheating a bit. I'm using SUMPRODUCT to return the number of times the expected descriptions appear, and by subtracting these from the total number of text values I get a count of &quo...

how do I import pages from one publisher file to another
I am trying to merge two documents made with publisher into one document. I am finding this entire process frustrating What version Publisher? There is this for 2007 Publisher Tools PubCat v1.0.0.0 http://ed.mvps.org/Static.aspx?=Publisher/tools -- Mary Sauer http://msauer.mvps.org/ "AKB" <AKB@discussions.microsoft.com> wrote in message news:B41D7855-F0CF-47A3-B215-5D3B0B4D5542@microsoft.com... >I am trying to merge two documents made with publisher into one document. I > am finding this entire process frustrating A p.s. I have a convoluted way to combine 2003 d...

another EXPERT LEVEL FORMULA from me
Hi All A B 1 #N/A d 2 #DIV/0!, h 3 5 k 4 3 t In Sheet1 I’ve got two columns: Column A and Column B. What I’m tiring to do is to create a formula to get the value of the first cell in a column “B” which corresponding (offset) cell in column A is the first row in Column A with NO ISERROR on it like #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! In the example above this is “k” (the value in cell B3). So far my guided formula is array and look like this::=INDEX(A1:A20,MIN(ROW(B1:B20)*(B1:B20<>#N/A))) but it doesn’t work at all. I’m also planning the formula to be in ...

Another question
I used Money to go to this page: http://www.microsoft.com/money/special/MoneySpecialPages_Launch2006.mspx Here is what this page states: Important things to know before you download Microsoft Money 2006 Premium: This download offer is available in the US only. You must have a Microsoft Passport to purchase and download the software. What if I don't want to use Passport? I don't have one and don't want to get one, or if I do have one, I have no idea waht it or the password. If I buy it from a store, will I have to use Passport to install and use it? I don't use...

Extract Text
Dear all I have this text in a field: 200701-I 200701-II 200701-III 200701-IV and I need to separate only the data after the six first data: -I -II -III -IV How can I do it? Thanks a lot!!! Andr=E9. Take a look at Access HELP for the Mid() function. Open a query in design view and add a new field something like: NewField: Mid([YourField],7) -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ <gatarossi@ig.com.br> wrote in message news:11893397...

exchannge 2003 server on another server
dear all , I have one Server running on windows 2003 server standard with active directory and another new server plan to deply exchange server , my problem is when i install exchange server 2003 , setup /forestprep it unable locate to AD , please help , thank you . Check your DNS settings. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "ys" <ys@discussions.microsoft.com> wrote in message news:0909D69D-AEE2-41FA-BD45-C96C46DB64B1@microsoft.com... > dear all , > I have one Server running on windows...