i was stuck with some mass data which spans over many rows and columns. i am relatively new to excel and wanted to know how i can get this data arranged in a way where i can access all the data from a printable view. i have attached a file explaining basically what i want.
View 5 RepliesI need to be able to convert a large spreadsheet to pdf for sending to a commercial printer. The spreadsheet is a family tree and covers an area a1 to ca107. The printers normally do the conversion from an emailed copy of the .xls file but charge for this.
Printing will be done on a wide carriage printer so the pdf has to be one sheet not a large number of a4. I have a copy of Acrobat but cannot work out how to get the spreadsheet to save as one sheet.
The format of the numbers are:
####-##-###-####
I have 2000+ numbers is coloumn B.
There are a lot of sets of numbers that are still text! It's highly annoying and I need to analyze the data in Access.
I tried the paste special advice and the 3 ways microsoft suggests. I have a feeling its not working because of the required format that I need.
I need to do this project tonight!
Background: these are imported numbers, I had to combine other excel databases... By the way, is their an easier way to combine excel sheets into 1?
I'm struggling with getting my Excel worksheet to fit nicely on the pg for printing to .pdf. I could tinker with column widths manually to work this out for a printed report. But I need to print a couple hundred reports with varying column widths, so I need a vba solution.
The context is that I'm using vba to process two sets of interlinked data on a worksheet. I only want to print one set of data, and keep the second set out of the print view. To do this, I'm keeping the first set of data in columns 1 to 10 and the second set of data in columsn 21 to 30. I set the print range to only include columns 1 to 10.
This works fine for keeping the second set of data off the printed pg. The problem is that there is always a gap between the end of column 10 and the maximum width of the printed pg. Given the formatting of the report, this doesn't look very good.
I know that page setup offers the "fit to" option. This would work if I wanted to fit everything on the worksheet on the pg, but I don't. I just want to fit columns 1 to 10 to the pg. Is there a way to do this, allowing for varying widths in columns 1 to 10? Is there a way to do it via vba?
I was wondering if there was an easy way to copy columns (headings) and the formulas to a new sheet. I'm currently working on Sheet 1, need to copy everything onto a new sheet so I can keep things separated by week..
Example, Sheet 1 is Week October 30th, would like my 2nd sheet to have same exact headings and formulas which I would then rename to Week Nov. 1st etc..
Also, is there a way that I can then copy the 4 sheets that would make up a month into a new spreadsheet so I can then start December...
Is there an easy way to collapse rows and columns on all sheets in a workbook.
View 3 Replies View RelatedI am facing problem to make summary sheet of top 3 value from large number of value of my excel sheet like-
Sheet1
A
Group
B
Name
C
Value
1
Nashid
200145
[Code] ......
Now I want to make summary in sheet2 top 5 value among the list of sheet1 separately from group 1, 2 & 3.
If group number is 1 in sheet1 then top 3 value will be like this-
Group1
Name
Value
Nashid
200145
Jabed
184198
Eman
156622
If group number is 2 in sheet1 then top 3 value will be like this-
Group2
Name
Value
Rubel
172569
Hasin
152304
Monem
104463
If group number is 3 in sheet1 then top 3 value will be like this-
Name
Value
Romel
168251
Nasim
136357
Hasan
120410
I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.
I need to move part columns of data from 6 columns to form 1 large column in column A.
For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !
Also need to delete unnecessary 'page headers' throughout as in rows 2-6
In column A I have a list of product names..(e.g....ABCD, JKLM....)...this is the "master product list" -- 1000 rows of products.
In columns C:G, I have additional product names, each column represents products sold by state. (each column lists between 100-250 products).
I want to do a VLOOKUP and I wanted to know what products that are in
A1:A1000, that is NOT listed in C:G?
My experience with VLOOKUP is limited...but I do know that I can only look in 1 column at a time.
How can I write it to look at the ENTIRE range? I suppose I can copy all the all the products to 1 column...?
I have set up a form where the user can enter the number of copies they want for 10 items (I will call them item1, item2 etc for ease)
Is it possible to then link the numbers in the form to separate parts of a large printing Macro?
Each sheet contains 30,000 rows of data in column A, C, E, G and I.
I want to find each cell of column A, C, E, G and I to another sheets column A, C, E, G and I respectively.
If match found then it should return "True" in column B, D, F, H and J.
I have tried VLookup, but it takes too much of time for calculation (1,50,000 vlookup Formulas needs to calculate in each sheet with If condition) some time calculation gives wrong results.
As well as I want to do filtering on this all the data... so that time calculation gives the problems (I have used application.Calculation=xlCalculationManual)
So now I am using Range.Find method, but that loop excecutes for 30,000 times and each ittration contains 5 Find methods.
I have a very large database where I want to filter out numbers that do
not have certain two digits as their last two. For example, the column
based on which I want to filter out data contains numbers like
197301310153. I want to filter out anything that does not end in 53....
simplifying a formula which gathers data from about 50 worksheets from within the same work book.
The data to be gathered is in the same cell on each worksheet and is simply a number but i want the SUM of theses numbers carried forward to another worksheet. Each worksheet is named by date i.e. sheet 1 is named "16 June 2014" and sheet 2 is named "23 June 2014" and so on until "30 March 2015" (Each sheet represents one full week Monday - Sunday).
i have a problem in copying many columns to to rows that originally contains data, this is gonna be really complicated so here is wt i want, let's assume i have this table .....
View 6 Replies View RelatedI am trying to convert some data...
I have a spreadsheet of 1000 rows on my spreadsheet and I am trying to convert them to 2 columns. Below is an example
Antidiarrheal AgentsBrand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
Antidyskinetics Brand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
I want it to look like this:
Antidiarrheal Agents Brand or Series
Antidiarrheal Agents Application
Antidiarrheal Agents Generic Drug Name
Antidiarrheal Agents Strength
Antidiarrheal Agents Type
Antidiarrheal Agents Dosage Form
Antidiarrheal Agents Container Type
Antidiarrheal Agents Volume
Antidyskinetics Brand or Series
Antidyskinetics Application
Antidyskinetics Generic Drug Name
Antidyskinetics Strength
Antidyskinetics Type
Antidyskinetics Dosage Form
Antidyskinetics Container Type
Antidyskinetics Volume
Is there a formula or a suggested way of doing a macro to acheive this?
I have a spreadsheet that consists of columns of data and I need to covert it into rows depending on how the data is set up. In column A, there is a list of invoice numbers. Columns B, C, & D are as follows:
Column B: Sales Rep Name
Column C: Sales Rep Number
Column D: Sales Rep % Split
I want to have a single row for each unique invoice number. As an example, I have the following:
Row 1: invoice ABCD-1234 Sales Rep 1: 55%
Row 2: invoice ABCD-1234 Sales Rep 2: 25%
Row 3: invoice ABCD-1234 Sales Rep 3: 20%
I want the output as follows:
Row1: invoice ABCD-1234 Sales Rep 1: 55% - Sales Rep 2: 25% - Sales Rep 3: 20%
I have attached a sample workbook with two sheets; one containing the raw data and the second, which shows the desired output.
I have data which is in rows. I want them to be in 5 columns.
I am attaching the sample.
for some of the entries, there will be 6 rows:
Company Name - 2nd row
Address - 3 row.
In this case, I want to combile them as one entry Separated by a comma
& placed in one column
Eg: For 3rd one in sample,
Haifa Marble & Tile
69 Garfield St
Wanted Result: Haifa Marble & Tile, 69 Garfield St
If this is not possible, then suggest how to combine two columns to make it
into one column separated by comma.
When I was using Excel 2000, there was an Excel add-in where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel add-in does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.
Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?
I have a very large spreadsheet that is a consolidation of approximately 105 separate of contacts lists. The new consolidated spreadsheet contains demographic data, and a column for the name of each of the 105 lists. Each contact in the consolidated list had an "X" placed in the column if they were a member of that particular list. Each contact can be in more than one list. This is not very useful for me.
Here's a visual representation of the current list along with what I need:
FName
LName
Address
Phone
City
List1
List2
List3
List4
List5
List6
[Code] .....
I would like to add a column to the spreadsheet and call it "Source"
And then have a macro or VB script to do the following:
1. As shown above, put the column name (ie "List2") in the "Source" column whenever there is an "X" in that row for the corresponding Column.
2. Because each contact can be in more than one list, I need to append the column names so that "Source" column contains all of the column names (Lists) that had an "X"
3. Once all of the list names have been added to the "Source" column, I will delete the list columns.
convert the data in column B to Number so that i can perform action in Column D. see attachment for the query.
View 3 Replies View RelatedSolution to convert data columns to rows in excel.
View 4 Replies View RelatedI have a 45 page spreadsheet with over 3500 contacts and the data is currently listed in individual cells as seen below:
John Smith
Director of Business Travel Sales
ABC Hotel
1200 Market St.
Philadelphia, PA 19107
Phone (215) 555-1234
Fax (215) 555-4321
jsmith@abchotels.com
www.abchotels.com
I want to convert the each item [data] above into separate columns so I can then save it as a CSV file and then export th data into an email list; but I have no idea how do to this.
I have a software package that requires serial number effectively data to be entered in a particular format.
As this can cover hundreds of lines I would like to make it less tedious to enter, and as my MACRO knowledge is very basic.
The data starts off in format below in example 1. After the data is CUT from the .html or .pdf document and PASTE into EXCEL. I would like the MACRO to start by pressing an activate button within EXCEL,
The serial numbers always have four digits with single serial numbers being separated by spaces and ranges being separated by a hyphen with the odd carriage return depending on how many numbers there are.
I would like the data to end up in two separate columns as shown in example 2.
Example 1 (Starting format)
* indicates space
- indicates a range, this needs to be separated into two separate columns
2252*2254*2256*2257*2259*2272*2274-2276*2278*2280*2282*2284*2286-2641*2643-2681*2683-2712*2714-2717*2719*2721*2724*2726*2727*2729* 2733*2735 *2738*2739*2746
Example 2 (Finished format ready to be paste into software package
2252
2254
2256
2257
2259
2272
2274 2276...............................
I have exported a text file from a universe database and need to import it into excel.
Below is an eg of one section. The text file contains approx 257 of these, all one below the other.
Between each set of dotted lines is one user profile (not data all shown in eg) .
Each one (user) is NOT the same amount of lines(rows) deep and only the dotted lines separate each user.
A simple import places all data into one very long column.
I need a way to place each user in there own column.
I have user names as column headings the labels of the values below as row headings. I cannot change this.
Manually entering the data took a very long time and i need to update this workbook regularly.
In the text file, " the variable......: value " (eg UserCode.....: XYZ) sets are all lined up so that using a text editor, with column mode, I can delete all the data headings/labels up to the space after the colon.
This leaves me with 7000 rows of values (eg XYZ) one column wide and various numbers of rows deep per user.
Is there a way to import the data and seperate each user via the dotted lines or other so that each users data is contained in consecutive columns. 1 user profile per column.
The standard import utility allows for column placement (delimted, fixed width). Can this be done on rows, sort of flip it 90 degrees and use the dotted lines as column markers. Just guessing now. Any and all assistance would be most appreciated.
Another issue (not as important but still helpful) is that the "procudures barred" section of user profile can also be one row(line) or serveral. Problem is each Name...:Value set must be on one row(line) for everything to match in the work book.
Using custom macros in my text editor i moved all the 'proc. barred' data to a single row. Alot of manual editting was involved and any ideas on this area would also be muchly appreciated.
One EG user profile (not shown, but all the colons line up) ....
find the attached workbook
I have a Database and user form, in the user form i have a field named “Vehicle No” this is a combo box from which a user needs to select the Vehicle numbers, and all these are working fine now, I need your help in the following:
When user selects the second field named "Select Vendor name" i need a pop up window which shows all the Vehicles belongs to the vendor which they have selected, and with the popup window user selects the vehicle number then the Vehicle number combo box should be filled.
Currently users have to select by scrolling through Combo box which takes long time and difficult to find by scrolling.
I have a spreadsheet that the Print range is A1:E265.
I want to know if I can print this range without Column C.
I have a spreadsheet list in the following format (Note Tax ID would be cell
A1, etc):
A B C
Tax ID Numbers Street
11-03-3040-0178-01-794HIGMAN PARK
11-03-2310-0001-00-3100HELMAR
11-03-3040-0169-00-0102HIGMAN PARK
11-03-3040-0165-00-4104HIGMAN PARK
11-03-0020-0039-01-6104S CRYSTAL
11-03-0021-0006-01-9105S EUCLID
11-03-0024-0004-08-8105PAUL
11-03-0018-0029-00-1105HIGMAN PARK
11-03-1880-0069-00-4107S CRYSTAL
This continues downward to cells 6577. I'm looking for a way to carry these three columns over at some point so I can put more data on a single page, thus minimizing the # of pages I will need to print, but yet remain in the proper sequence as established in column B.
I have a data sheet which I need to print everyday, I need to print Column A plus other individual columns on separate pages. For eg. Column A + B, Column A + C, Column A + D etc until the last column. Besides hiding and unhiding, is there any way to do it via vba? For eg, pop up to ask user which column to print?
View 6 Replies View RelatedI have a list of domains (about 200,000) that looks like this:
I have a list of urls (about 1 million) that looks like this:
I want my results to only show this (since this is the only thing in the second file that does not contain anything from the first file):
I have tried programs designed to do this but they either (a) shorten everything down to the domain or (b) wont remove anything since the domains are part of a longer url in the second file.
Is there a way to print my columns headers in every page?
Everytime I have to print my spread sheet, I always have to keep page#1 in hands, because I cannot remember the headings.