Selecting and copying columns based on a cell value

I am trying to do somthing simple (?) without unnecessary copies etc o
the data, (my methods so far seems too messy)

Basically What I want to do is scan through these various workbooks an
worksheets copying in all columns that match a specific value (eg wher
row 5 = "XYZ")

More info:
Using Excel 2002 and this needs to be rerunable on a daily basis t
replace the manual copy/paste method in use now.
I have source data in multiple workbooks (submitted by differen
individuals) and I want to scan through these extracting out only thos
columns that match a criteria.

Unfortunately the data is NOT organised ideally for filtering.  It i
column oriented rather than row so each column holds data for 
particular account (numbers, dates and text) with some fomatting an
calculations happening.  If I tramspose the data (which I've bee
playing with) then I can use list filtering etc but then I loose th
formatting etc.

I thought there should be some way to do this elegantly.

Thanks

--
Message posted from http://www.ExcelForum.com

0
2/17/2004 2:08:19 AM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
428 Views

Similar Articles

[PageSpeed] 56

Reply:

Similar Artilces:

Another multiple criteria/column question
Ok, first post and pretty much a new user to Excel. I have two sheets that I am working with, trying to recall data from one to the other that meets criteria. In a nutshell: Sheet 1 contains a column of unique values (col A), cols C,D, and E are where I want to insert the formula to find data on Sheet 2. Sheet 2 contains 4 columns, A contains multiple occurrences of each value (from Sheet1, column A), each with its own timestamp in column D. On Sheet1, in column C, I want to find a value on Sheet2 in column A and return the timestamp in column D. I know I can use VLOOKUP for this. =VLO...

How to shift address listings from row list to columns?
I have a mailing list with name, address, city, state & zip with each item in individual rows like a list of labels and a few empty rows of space between each listing. How do I create/transfer this list into columns accross so I can sort by city or zip? Thank you! If your data is nicely grouped, with each group in 5 lines, viz: name add city state zip then an earlier suggestion given which worked might be worth a try: See: http://tinyurl.com/wgcb -- hth Max ----------------------------------------- Please reply in newsgroup Use xdemechanik <at>yahoo<dot>com for email --...

Excluding hidden columns and rows when copying to another workbook
When I print part of a worksheet that has hidden columns and rows - the hidden columns and rows do not print. That's what I want. Now--I'd like to take that same data and copy it to another workbook excluding the formulas and hidden columns and rows so that the new file contains only the data as was printed. How can I do that? PJ Select your range including hidden rows and columns then Edit>Go To>Special>Visible cells only>OK Now do your copy/paste. Gord Dibben Excel MVP On Fri, 6 Feb 2004 10:16:07 -0800, "PJ" <anonymous@discussions.microsoft.com> wr...

format changes when copying from one cell to another
I'm sure this is simple, but it is not clear to me... I am copying the contents of a group of cells that exist on one worksheet to another. In Sheet2, I tell cell A1 that it should equal Sheet1's A1 value. That works fine. But if the cell is blank on Sheet1, it comes up as "0" on Sheet2. I figured it was probably assuming numeric formatting, but when I right-click > format cell on both Sheet1 and Sheet2, both show up as "general", so why would Sheet2 take a blank value and populate it with "0" ?? I gather than you are putting a formula in one...

Move to match cells
A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 000801097-3 1998 11 000801091-7 009601098-9 2004 000801098-...

Sorting with Column has Formula
Hi everyone, I never imagined that the formula in the column would affect the sorting order in any way, but it does in my case. Below is the formula in that I have in Col I, and I'd like to sort it in Ascending order, but the result is that it sorts with all the empty rows on top and the one with the result from the formula at the bottom. I assume it consider the "I" in the "IF" function in the formula, but I'm not sure. Can anyone tell me how to fix this please? =IF(E2="","",IF(J2="X","Priority #1",IF(...

Case select returning error when cell contains #N/A : how must i avoid this error
Title says it all Thanks, Luc maybe this will do what you want Sub test() If Not IsError(Range("C1").Value) Then Select Case Range("C1").Value Case 1 MsgBox "1" Case 2 MsgBox "2" End Select End If End Sub -- Gary Keramidas Excel 2003 "Luc" <lferr@live.be> wrote in message news:7AB07996-F1EE-41C1-8D9D-8A7DA54CED91@microsoft.com... > Title says it all > > Thanks, > > Luc Thanks for your help !!!!! Luc "Gary Keramidas" <GKeramidasAtMSN.com> schreef in berich...

Results from blank linked cells
I am linking cells from different worksheets in the same workbook, using the copy/paste/link cell method. How can I get a blank space (as opposed to the zero I am presently getting), in the destination, if the source cell is blank. I am linking a input sheet to several forms that must be sent out, but I don't want a form that will have a number of zeros in it. =if(sheet1!a1="","",sheet1!a1) If the linked cell looks empty, show empty, else show the value. Mr. Anolog wrote: > > I am linking cells from different worksheets in the same workbook, using the &g...

How to slant cells in Excel on top of chart at about 45 degrees
Trying to make a chart and slant cells at the top at about a 45 degree angle with borders and still be able to type into it. Is this possible? ckricci Wrote: > Trying to make a chart and slant cells at the top at about a 45 degree > angle > with borders and still be able to type into it. Is this possible? HI ckricci Try Format > Cells > Alignment Tab and set the Orientation to 45 degress -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2...

Copying to a disk
when I try to copy data to a disk, it often can't read the disk or it says the disk is full when there is only 1 small file on it. I also can't erase the file. I can't find a command in file to allow me to erase or delete the file or format the disk. Wha tdo I do?? Dear jqh: This is a Great Plains forum. Although someone here might be able to answer your question, you might have better luck on a MS Windows forum. Frank Hamelly MCP-GP, MCT East Coast Dynamics www.eastcoast-dynamics You throw the disk away and get a new one. You're asking for trouble copying data t...

Copying Modified Invoices
We currently use SOP Short Invoice and I want to copy the report to the SOP Other Invoice in report writer. The invoice layout is totally different, and I am trying not to spend days recreating it. I could not locate any type of copy functionality from one modified invoice to another. Any ideas out there. We are on version 7.0 Zach 1.export the modified Short Invoice report as a package file. 2. open the Package file in notepad and change the Component name and Report SOP Other Invoice and save 3. import the package file Voila! HS "Zach Morgan" <ZachMorgan@discussions....

retain only rows with condition that cells in column H containing "AU"
I have a large database containing columns with one column specifies the product name. In one instances, I need to retain only rows with condition that cells in column H containing "AU" of the entire string in the cell. Delete those rows without it. The problem is , that the AU of the string can appear in any position, not a fixed position. Is there any simple way of doing it? Many Thanks! Regards, Bora Hi, You can use AutoFilter. Choose the command Data, Filter, AutoFilter. Then open the autofilter drop down in the column H and choose Custom and from the first drop down, t...

Varying column widths
I have 2 excel sheets - one is 20 columns wide the 2nd is 7 columns wide. The 20 column sheet has a general information section at the top that I would like to add to the 7 coulmn spreadsheet. However, when I copy and paste it "spreads out" or goes way beyond the width due to the number of columns. I have tried paste special object and entering the excel sheet that way and it doesn't fit properly and too many cells are shown. Any other way to do this? If you only want it up there for appearance purposes, you can try: Copy the selection. Click the cell where you want t...

validation list or combo box dependant on cell value
Am i able to determine the values shown in either a validation list or combo box being dependant on a value in another cell? ie: Cell A1 = BOB then validation or combo box would then base it's list from the named range (or whatever the solution may be) based on Bob. if i was to change A1 to ROY then it would also change the underlying list? I have tried everything that i think SHOULD work but that it pretty limited... thanks in advance rich I'd start with Debra Dalgleish's site: http://contextures.com/xlDataVal02.html Richard Edwards wrote: > > Am i able to deter...

worksheet labels based on cell results
How can I build a macro to use the contents of several cells in a column to label a corresponding number of worksheets with their contents. Ideally this would also build links to the tabs so that a user could click on a specific cell (in that column) and be redirected to the corresponding worksheet Thanks, Mitch Hi for labeling the tab try something like activesheet.name=activesheet.range("A1").value For the second question try using a Hyperlink (Insert - Hyperlink) -- Regards Frank Kabel Frankfurt, Germany Mitch wrote: > How can I build a macro to use the contents of ...

Help me create sales chart based on state and quantity
We have a production report on excel. It shows the details for our clients. Part of that data includes the state in which the client lives. We are trying to create a chart showing the percentages of each state( so we know where the most deals are closed) Any suggestions? Hello mr_merchant_man, this sounds like a job for a pivot table, using Average as the data calculation operator. Or, depending on your version of Excel, you can use AVERAGEIFS (in Excel 2007) or calculate an averate with a combination of SUMIF divided by COUNTIF. To be more specific, it would help to s...

Equivalent Column Break
In MSWord you can insert a column break when doing newspaper columns. If I have two text frames connected, and I am almost at the bottom of one but want Publisher to start at the top of the next one, how do I insert a 'break'? or do I have to press return until it move the text. Thanks Anita (1) You could shorten that column so that your text breaks where you want it to. (2) Or you could check the Help file and search for "insert break". Insert a column break You can insert a column break anywhere in a text box. If the text box contains more than one column, the ...

clearing pictures copied from websites
Hello, I posed this question a couple of years ago and someone helped by providing a macro which I unfortunately lost. I often create spreadsheets with prices I copy from pricewatch.com. I want to keep the product image but I want to get rid of the buy now button. I can't delete that row or column - the image remains. Thanks Try rightclicking on that button and hitting the delete key. Boe wrote: > > Hello, > > I posed this question a couple of years ago and someone helped by providing > a macro which I unfortunately lost. I often create spreadsheets with ...

copy without space
I copy a cell to another app. and it contains a space. How should I cop from Excel without this space -- Message posted from http://www.ExcelForum.com ...

copy rows to another file
Dear Experts I have following code,i need to copy desirde rows to new file (r.xls) on sheet 1. This code is able to copy desired rows from active sheet to sheet2(same file) based on values in column G. Would you please guide me? regards Sub Marine() Dim arrParts() As String Dim MyRange As Range, CopyRange As Range Dim LastRow As Long LastRow = Cells(Cells.Rows.Count, "G").End(xlUp).Row Set MyRange = Range("G1:G" & LastRow) response = InputBox("Enter rows to copy in the format nnn,nnn,nn") arrParts = Split(response, ",") For Each ...

IN EXCEL, WHEN I CLICK ONA SINGLE CELL It HIGHLIGHTS WHOLE Page
please help Hi maybe: http://www.mvps.org/dmcritchie/excel/ghosting.txt -- Regards Frank Kabel Frankfurt, Germany "confused" <confused@discussions.microsoft.com> schrieb im Newsbeitrag news:36180425-96C2-4368-9DE0-3E60741154FF@microsoft.com... > please help Just click on a cell and nothing else? Or perhaps you mean click on a cell then as you move the cursor around other cells are selected like the cursor is stuck? If the former, you must have some event code which selects the cells or you are clicking on the gray box at intersect of rows and columns(top left above ro...

Column comparison
I am trying to compare 2 columns of numbers so that I can identify and delete numbers no longer required. Can anyone help me find a formula for this please? Many thanks DT Hi Dave, Need more information like a sample of the existing data plus a sample of what you want left. Maybe an explanation of the criteria for what needs to be deleted. -- Regards, OssieMac "Dave T" wrote: > I am trying to compare 2 columns of numbers so that I can identify and > delete numbers no longer required. Can anyone help me find a formula for > this please? > > Many than...

Hiding Column and Row Bars.
I know theres a way to costumize the way you view an excel page by hiding toolbars, but is there a way to hide the rows and column bars just so all you can see is the actual page. E.G. is there a way to Hide the top bar that defines the colums "A", "B", "C","D" and Rows 1,2,3,4,5. Go to Tools>Options>View, there are a number of options you can play with there. -- HTH RP (remove nothere from the email address if mailing direct) "tamato43" <tamato43@discussions.microsoft.com> wrote in message news:7D1E5DEB-D6F2-47C8-95E2-2...

Help with cells auto formatting
Hi, I have posted a similar question before however I never really got this sorted so sorry for repeating myself. Basically I use two spreadsheets daily at work all with various information on and various formats in each Column. My problem is when I close the spreadsheet and reopen it the cells that are formatted as 'general' or 'number' turn into Euro currencies. Does anyone know why this happens or how I can stop it? The spreadsheets aren't stored locally they are stored on a serve that only myself and my boss can get onto and we both have the same p...

Disallowing duplication of nmbers in a column
Is Excel capable of disallowing the same numbers in a column? I have a column in a sheet that invoice numbers are entered into. I would like that column to alert or something if duplicate numbers are typed in. Hi Have a look here: http://www.cpearson.com/excel/NoDupEntry.htm -- Andy. "Barb1" <Barb1@discussions.microsoft.com> wrote in message news:E3631DD3-724E-4C0D-956B-4201876A9A4A@microsoft.com... > Is Excel capable of disallowing the same numbers in a column? I have a > column in a sheet that invoice numbers are entered into. I would like > that > c...