Separating data into new sheet

Hi guys,

I have the following data on worksheet "Sheet1"

Code    Value
A         1000
B          200
A          100
C          150
B          300 
B          200
C          400


I have worksheet "Sheet2" that I want user to input what CODE they want to 
IMPORT...
Let say A

then on "Sheet2" i would get:

Code    Value
A         1000
A          100

It is a combination of sorting, copying and moving the wanted data to the 
targeted sheet.
Is there an excel feature/solution that does this? or a VB code is needed?


Thanks in advance
0
Utf
3/3/2010 10:39:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
782 Views

Similar Articles

[PageSpeed] 4

One way ..
Your source table as posted in Sheet1, data from row2 down
In Sheet2,
Let's say the input for the code will be made in A2, eg: A
In C2: =IF(A$2="","",IF(A$2=Sheet1!A2,ROW(),""))
In D2: 
=IF(ROWS($1:1)>COUNT($C:$C),"",INDEX(Sheet1!A:A,SMALL($C:$C,ROWS($1:1))))
Copy D2 to E2. Select C2:E2, copy down all the way to cover the max expected 
extent of data in Sheet1's col A. Minimize col C. Cols D & E returns the 
required results dependent on the input code in A2, all neatly packed at the 
top. Joy? hit the YES below 
-- 
Max
Singapore
--- 
"apache007" wrote:
> I have the following data on worksheet "Sheet1"
> 
> Code    Value
> A         1000
> B          200
> A          100
> C          150
> B          300 
> B          200
> C          400
> 
> 
> I have worksheet "Sheet2" that I want user to input what CODE they want to 
> IMPORT...
> Let say A
> 
> then on "Sheet2" i would get:
> 
> Code    Value
> A         1000
> A          100
> 
> It is a combination of sorting, copying and moving the wanted data to the 
> targeted sheet.
> Is there an excel feature/solution that does this? or a VB code is needed?
> 
> 
> Thanks in advance
0
Utf
3/3/2010 10:53:01 PM
Reply:

Similar Artilces:

Totalling columns and repeating formulas in new entries
i'm working in excel 2003 i've tried searching this, but have come up with nothing. it seems s basic, i'm sure i must be calling it the wrong thing or something. in my worksheet (that's what a spreadsheet is called now, isn't it?) enter expenses in column "e" and revenues in column "i", with the ne gain or loss for that row (entry) showing up in column "j". i had n problem setting that up. the first problem i'm having is the formulas and formatting bein copied to the next entry. some of the new entries have no value i column "e&...

How to create a single line separated by commas from a matrix?
Hello, I've the following problem. I've a table made of several columns. Each colum represents a characteristic regarding to a subject (e.g. Name, Surname, Home Street, etc...). In the rows I put the different people. I need to export these info to a format compatible with other program - an email program-, which requires the information to be arranged in a different way. The program needs all these data to be ordered in a single row, with each characteristic between quotation marks, and separated by commas, following this pattern: "characteristic 1","characteristic ...

Copy Page Setup from Sheet to Sheet?
Is there a simple way to copy the page setup (eg margins, page orientation) or must I continue to go through the whole process for each sheet? Right click on any one sheet and then click select all sheets Now go to page setup This will change settings on all the pages "blank" <blank@void.net> wrote in message news:428de82a$0$7136$5a62ac22@per-qv1-newsreader-01.iinet.net.au... > Is there a simple way to copy the page setup (eg margins, page > orientation) > or must I continue to go through the whole process for each sheet? > > Depress the right mouse b...

auto fill data from cells
i would like to be able to enter text data in cells in column b on 6 different worksheets and then have a seventh worksheet that will display all of the text data from the six different sheets on it. So, if i list 40 words in b5 thru b45(one word per cell) on all six sheets, i would like to see all 240 words on the seventh sheet(total "catch all" sheet) Any help would be greatly appreciatiated. thanks dude On the 7th sheet, in cell A1 ='Sheet1'!B5 fill the formula down to A40 On the 7th sheet, in cell B1 ='Sheet2'!B5 and again fill the formula down to B40 R...

Separate inboxes for separate accounts
How can I separate incoming e-mail from separate e-mail accounts into folders so I can tell where incoming mail came from? Using Outlook 2000. >-----Original Message----- >How can I separate incoming e-mail from separate e-mail >accounts into folders so I can tell where incoming mail >came from? Using Outlook 2000. >. > I'm a novice but I just ask that question and dl@spoofmail.com gave me the following advice: It is done with rules. 1st create your folders (sounds like you've done that.) Then select Tools / rules & alerts / New Rule... This brings up...

Checking if a cell is filled up with data
Hi! I would like to write a function that checks if a cell is empty or if it has any kind of data. The kind of datas (numbers or words or bocth) is irrelevant. The only thing that is important is whether or not the cell is empty. Has anyone an idea how I could write such a function? Thanks -- xpucto ------------------------------------------------------------------------ xpucto's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23748 View this thread: http://www.excelforum.com/showthread.php?threadid=374119 =IF(A1="","",A1) Enter this f...

Expand sheet as information is entered
Is there a way in Excel to have the sheet expand as information is keyed and the keyed information wraps to new lines. Also, can x of y page numbering be used with this if it is possible? Thanks! Ken K. -- akkrug An Excel sheet contains millions of cells to enter data into. What do you mean by "expand"? What do you mean by "wraps to new lines"? To start a new line just hit enter to move to next cell. If want more lines in one cell you can employ "Wrap Text" formatting or hit Alt + ENTER to start a new line within a cell. You can also use Row Autofit t...

Master image to use in separate sheets?
Hello All, Does anyone know if there is a way to tell Excel to use the same image/background for each sheet, instead of needing to import for each one? Or is there a way to set up a (similiar to Power Point/Master slide). Thanks in advance, drew If it's just for one workbook, I'd have a master worksheet in that workbook that I could just copy. If it's for lots of workbooks, I'd set up a workbook with one sheet that had that background the way I wanted it. Then save this workbook as a Template (let the folder default to excel's favorite). Tnen when you rightclick on...

re-install Office:Mac onto a new ibook
My daughter is trying to install her offic:Mac 2004 ver. onto her new ibook. She has tried all 3 product keys and the software tells her that none of the keys is a valid key. I am using one of the keys. There is one that is not used by anyone. Any ideas? menace Kindly see: http://groups.google.com/group/microsoft.public.mac.office/browse_thread/thr ead/86500aa2b6a12ed1/a02bed6966e34e47?lnk=st&q=office+mac+install+key&rnum=8 &hl=en#a02bed6966e34e47 http://www.mcgimpsey.com/macoffice/office/pid.html On 28.11.2006 5:13, in article 1164687194.510100.155880@j44g2000cwa.googlegroups...

Import data 04-19-04
I need import some documents(WORD, PDF...) into the "annotation" table. This is a one time running utility routine. I found that the API I need is in microsoft.crm.platform.proxy.dll. Anyone know what I should do next? Can I just copy the .dll to some place, and add the reference to VB.net application? I read on a document that I couldn't write the documents directly to SQL server table. Thanks. ...

Getting external data filters
I have successfully generated queeries to import data in my spreadsheets, but I don't know if I can or how to do something. My filtering data can change because it is personnel based, so I want to know if I can generate a queery that will allow the user to define the filters without having to redo the queery every time someone moves out of the respective work section or a new person joins. Basically, can I have a data entry area for them to list employees and have a queery reference this list to sort out the new data for only those people? Is there another way to do it? I am learning a...

How to automatically number a new document(Invoice) when opening #2
I am trying to set up a template in Excel 2000 and would like to be able to automatically number each invoice as the template is 63opeed to a new document, can anyone tell me in basic terms if this is possible and how to do it please? ...

Separating a comma separated list
Not having a great deal of experience with macros, I thought I might see if I could pick the brains of the group. I have to essentially break out a comma separated text string in a single cell into a vertical list, with a reference number thrown in for good measure. Example. Data I have: A B 1 NUMBER RESOURCE 2 101 Smith John,Jones Adam,Brown Philip,Greene Thomas 3 102 Smith John,Jackson Arthur Needs to look like: A B 1 NUMBER RESOURCE 2 101 Smith John 3 101 Jones Adam 4 101 Brown Philip 5 101 Gr...

$ large amount of Data.
Hi, I've been working on quite a large worksheet, and after about 5000 rows, I realized I forget to hit F4 and $$ the referenced cells. Is there a way to highlight a large area, and have it do the whole thing at once? Thanks, Confused Man Hi! Take a look at this: http://tinyurl.com/5pp4q Biff >-----Original Message----- >Hi, >I've been working on quite a large worksheet, and after about 5000 rows, I >realized I forget to hit F4 and $$ the referenced cells. Is there a way to >highlight a large area, and have it do the whole thing at once? > >Thanks, ...

unable to paste excel format data in mail body!!
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Email Client: Exchange Loosing all formatting when i try to copy &amp; paste an excel data in email body....pls suggest a solution. Entourage does not support the creation of rich HTML messages. You can simply attach your spreadsheet to the email message as an attachment, or you can, while in Excel, click on File>Send To>Mail Recipient (as HTML). Note that this method only works if Entourage is set up as your default e-mail application. On 10/02/10 8:49, Dhillon@officeformac.com wrote: > Version: 2008 ...

Keeping data current
How are others making sure your account and contact information is up to date? John ...

Data Protection
Where are in the process of implementing CRM and have a number of concerns relating to Data Protection. Is there any data protection experts out there who could advice on any question we might have ? Thanks ...

Varying data on X axis
Hi all, I have a graph that includes a lot of data, it looks messy hard to see individual points. I would like to be able to vary the amount of data points shown at anyone time on the x axis. I have had a look at adding a scrollbar, but I can only get that to increase the number of points shown. I want to be able to set a limit to the number of points shown at any one time. So if I have 1000 data points I want to able to view points 1 - 100, then 2 - 101, 3 - 102 up to 901 - 1000. Hope I have explained myself properly, I appreciate any help I can get. -- coa01gsb -----------------------...

Copy publisher sheet to word document
I have literally spent hours attempting to copy "ads" created in publisher to word document. Copy and paste does not work. I have to copy each portion of the ad and paste into Word. alot of wasted time - more like "hours". Please help in the midst of a major project. -- DH What version Word & Publisher? I can copy the ads to Word 2007. Select all, group. If you paste special and select one of the formats it should work. Opening the Office clipboard in both Publisher and Word will help too. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.m...

Separating strings in a field to separate fields
Hi, I have a field that consists of a string value that looks like Item1, Item2, Item3, each item is separated by commas. I want to separate that string value so that each item has its own field. Is there code that will easily do this? Thanks, Jaime On Wed, 5 Dec 2007 12:08:57 -0800 (PST), jseger22@yahoo.com wrote: >Hi, > >I have a field that consists of a string value that looks like Item1, >Item2, Item3, each item is separated by commas. I want to separate >that string value so that each item has its own field. Is there code >that will easily do this? > >Thank...

Data Series question #2
Hi, I have a line chart with 2 data series. One for actual balance and one for budgeted balance. The y-axis is $ amount, the x-axis is month. There are 12 points on the x-axis. (Jan to Dec). I have 12 data points for the budgeted balance. I'm using a dynamically named range for the # of data points for the actual balance series based on the current accounting month. (Jan shows 1 point, Feb shows 2 points etc.) I'm trying to add a 3rd series for Projected balance which would start where the actual balance stops. I have another dynamically names range as the data source. I have...

Archiving data
I already have an archived file from a few years ago. It it possible to create another archive that will just add to that original file? Or do I just have to create another one? Brian In microsoft.public.money, Brian wrote: >I already have an archived file from a few years ago. It >it possible to create another archive that will just add >to that original file? No. > Or do I just have to create >another one? No. You don't really have to do that either. You *could* do neither. >Subject: Archiving data >From: "Brian" anonymous@discussions.microso...

Linking a sheet's content into a Word document
Hi. I have a Word document and an Excel document. Some of the content of the Word document merely duplicates the content of the Excel document. What I would like to do is to set up the Word document so that it automatically retrieves the text from the Excel document. Similar to doing a '=D5' in an Excel spreadsheet, so that it just harvests the contents of a particular cell (in this case, D5) and transplants it to another place. Is there a way to implant text from the Excel spreadsheet (which is in another file) into the Word document, so that it automatically opens the Excel spre...

Launch Oulook Express Newsreader separately
Hi I use Outlook Express as my newsreader. My problem is that in order to access it, I need to first launch Outlook. Is there some way I can launch the newsreader portion of OE without launching Outlook? A command line switch or something I imagine. I'm using Outlook XP with OE 6 on Windows XP pro. Thanks I believe the command-line switch is /newsonly. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "aJax&...

Required field with deleted data
Hi, I have a one field continuous form that gets its data from a one-field table. The field is unique and required. The form also has a command “Close” button. I really have problems with the “On Event” order of things. If a person goes to a new record and starts to enter data and then deletes it by using the backspace or delete key and then leaves the record by 1) clicking on another record, 2) clicking on the “Close” button or 3) presses the “Enter” key. I get the Access error message because the field is required. If the new record is blank, I want to prevent the error message an...