I have a document needed to be printed with some pages in the middle in landscape page type, the rest in portrait. If using Word it would be easier, but in Excel I cant find the section break to chage page setup separately. Is there anyway to do it. Currently I'm printing the document separately in portrait and then landscape with some page break added and page number modified. However it's quite troublesome and easy to make mistake.
I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.
I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".
I have sheets with names of people in columns....some married...some not. When they are married, here's a sample format...
Jones, Donald T | Baker, Sarah Jane | Jones, Sarah Jane | Smith, Sarah J | Jones, Sarah Jane Smith
In this example, I would like to be able to determine which of the Sarah's belongs to Donald w/o having to visually look at each record ( 100,000's of records). (FYI: the names for Sarah would/could be her Maiden Name and possibly a name or two from a former marriage). What I need to be able to do is match and extract the names of Jones, Donald T and Jones, Sarah Jane and Jones, Sarah Jane Smith and eliminate Smith, Sarah J and Baker, Sarah Jane.
In my example, Donald is in the first column, but can be in any column on a row so the name positions are random across the columns. However, the format for each column is then same...Last Name, First Name Middle Name(or Initial) with a comma always after the last name in each column. The length of the last name also varies.
VBA or Formula that will search the cells in the columns of each row and return the names (complete contents of the cells with matching last names) that have a matching last name for that row.
I have a large block of text containing hundreds of serial numbers that I want to extract from the text into their own cells. If I paste the text directly into excel then the whole lot goes into 1 cell. The serial numbers I need are individually wrapped in brackets so I wandered if it was possible to paste the block of text into excel using both '(' and ')' as value seperators.
I am copying data from a PDF. The data I need is in columns, spread over hundreds of sheets. I would like the data in the same layout (columns), but need it in excel instead of the PDF sheets.
I've tried copying and pasting into Excel, but the problem is that what is in columns on the PDF ends up as multiple rows beneath eachother, all in column A, on excel.
I've tried Text to columns, but that seems to only work if all the data you need to seperate is in the same row. What I need to ultimately appear in one row is currently displaying in three rows, one beneath the other. Is there a way for excel to automatically move what's in cell A2, for example, to B1 (and do this for all data, all the way down the sheet, that's similar to that currently in A2?
(If it would be useful for me to post a screenshot, could someone reply with how to do that in the most version of Excel?)
I have hundreds of file names, and to cut a long explanation short, they are exported as a CSV file to excel. In order indentify the owner of the file I need to rename the file with a commar where you see the dash or the underscore in order to have the name appear in the next cell, as can be seen with Syma and Kevin below, so I can then sort the columns etc etc.
I know there is a formula that I can use to achieve this, ie have syma.pdf and kevinc.pdf in the next cell.
I think it works on identifying how many characters along the name and then send i to the next cell.
as you can see below, there is no set amount of characters to put into a formula.
Could I say, for example, if after dash /underscore put next word in next cell?
I have some data that is seperated by spaces. I need to split the data so each is in a seperate cell and then remove the spaces. I can do this if there is only one space inbetween the data, but that is not always the case. I attach a workbook with examples.
Hi, Column C has a bunch of addresses that have no spaces between the number and street. (27smith lane instead of 27 smith lane). Is there a way for me to quickly seperate the number and street name from the preceding number in each cell of column c? There are 1075 cells in column C.
I am posting the worksheet so that it may be a little clearer. I have 2 worksheets, the input area and the model area.
In the input area, users will input their data with a maximum of 5 beverage items and 10 food items along with a certain number of other data inputs.
In the model area, i would like to seperate the food items from the beverage items along with their corresponding other information as you can see in the spreadsheet i posted.
However, food items can vary in number up to 10 as well as beverage up to 5. Also, users may enter the beverage and food items in any order they wish to.
Could you help me figure out a way to transfer my data from the input area to the model area and sorting the beverage and food items so that beverage and foods go into their specific zones? i will be performing calculations which are different from food and beverages.
I have a 25k line spreadsheet w/ 37 columns in use. The spreadsheet is organized by sales rep among others data. Is there a way i can insert some kind of command to auto seperate or copy all the data associataed w/ "rep1" into one tab, then "rep2" into another and so on?
I need a macro that searches my spreadsheet for a keyword in Column B. If it finds the keywords (or an array of keywords would be even better), it then deletes the entire row above the keyword, the row with the keyword, and the row below the keyword.
I have three columns. Column 1: A list of items with a very long text description in each cell Column 2: The Key phrase found within the long description in the cells of Column 1. (The key phrase is different for each cell). Column 3: Custom shortened description in text format. Column 4: Formula to return the Custom shortened description, equivalent to the item in Column 1
How do I create a formula in Column 4 so that Excel searches for the Key phrase found within the string of Column 1 and returns the custom description in Column 4?
In the attachment, on the totals sheet I am doing a count of the results on Sheet2. Under "Alcohol as it Applies to Me" on Totals I am trying to count the 5 different categories, but the original question is a pick all that apply so at times there are multiple answers. I can't figure out the formula to count each phrase when there is multiple answers.
I am trying to write a code for VBA code for find and replace, I want to find a particular phrase (i.e. 1. Value Added Processing) which is all in one cell and replace it a range of cells of other cells which is contained on a different sheet.
So basically the original 1 cell would be replaced for anywhere between 1 to 20cells. Depending upon what I type in.
I have a large keyword list in a sheet called "AllKWs" In ColA from A3 downwards).
What I'd like to be able to do is this, which I'm sure will be complicated, but I will explain.
Say keyword phrase sheet has 25,000 rows of data (could be more/could be less). I click an assigned macro button. A pop up box appears. I type in a word or words I'd like some info on, so for example I type in a word or words like "car rent"
It then returns for me on a new sheet called "Multi Keywords" a lot of data on this sheet, which would hopefully be as follows:
All Row 1 will contain Column headings All row 2 will contain Total Counts (I'll explain in a minute this row)
So, all data to be returned from Row 3 downwards. OK, as to the data to be returned. All returned data In all Columns to show data in descending order by No of occurrances/appearances
Col A (From A 3 downwards) = The actual number of 2 word appearances (In this example that contain the words "car rent"
In Col B = All 2 word Phrases Containing ("In this example "Car Rent") (As a note, In this example, ColA (CellA3) could only show the number"1" and ColB (B3)could only show the phrase "Car Rent") once. (As there isn't no other possible combination).
In Col C =The actual number of 3 word appearances listed in descending order That contain the word "Car rent"
In Col D =All 3 word Phrases Containing "Car Rent"
In Col E =The actual number of 4 word appearances containing "Car Rent" listed in descending order
In Col F =All 4 word Phrases Containing "Car Rent"
In Col G =The actual number of 5 word appearances listed in descending order
Actually if anyone can crack this I really do take my hat of to them. OK,A few more points,
Cells B2,D2,F2,H2,J2,L2,N2,P2,R2, All contain the word "Total:" and if the macro can fill in the number as appropriate.
So for example Cell L2 (For 7 word phrases) would say something like "Total:42" (If in Col L From L3 downwards the macro found 42 7 word phrases that contained the words "Car rent"
Ok, Cells A2,C2,E2,G2,I2,K2,M2,O2,Q2 All these cells will contain the word "Total". So these cells would list the combined total number of occurrances of all the phrases. So for example cell K2 might say "Occur:324" as the total number of occurances of 7 word phrases that had the words "Car rent"in.
OK. as an example, I will post a code that Jindon wrote for me sometime ago. I'm posting this now, as it is very similar in what I would like this macro to be able to do, and might help as I'm sure this 1 will be complicated. This 1 looks for a phrase, returns by No of occurrances etc, but for all the combinations (Word lengths) within the Keyword phrase list, rather than what I'm asking for now, which splits them into Number of words columns.
Here it is anyway:
Sub NicheKeywordFinder() Dim a, dic As Object, X, myTxt As String, b(), c(), n As Long, i As Long, e, s, myTotal As Long myTxt = InputBox("HuaHinCarRental - Niche Keyword Finder") 'change to suit If Len(myTxt) = 0 Then Exit Sub Set dic = CreateObject("Scripting.Dictionary") dic.CompareMode = vbTextCompare ReDim b(1 To Rows.Count, 1 To 1): ReDim c(1 To Rows.Count, 1 To 3) With Sheets("All KWs") 'change to suit a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value End With