I have found a list of newspaper in an excel file and at the end of the text it is written the name of the language e.g Bangladesh News 24 (Dhaka) [In Bangla], The Bangladesh Monitor [In English]. I would like to distinguish the names ended with [In Bangla] and in [In English]. How can i distinguish this two different type of data with VBA?
View 1 RepliesI am trying to pull titles out of a text string but its quite difficult because of how the data was originally inputted. I need titles that appear in the following different formats
" title"
title/
title"
These titles were not inputted consistently and it could show up in the middle or at the beginning. I have attached a sample of the data that i am working with and a sample formula that i was trying to use to obtain my information. The formula that i have is a nested formula of find and mid formula that searches for the text within " ".
I have filled up my ComboBox1 with the sheets I want to add information to. My problem now is how to place the info in the correct sheet when I click CommandButton1.
I've been working on this for a few hours now. I got this to work on the first code I found but it didn't work afterward. I just deleted the whole thing and started again.
I have a spreadsheet using an Input Form (user cannot get to the spreadsheet to enter data - they must use the form). This is working well.
What I need to do now is allow the user to retrieve data that is already entered. Example: Spreadsheet has 100 unique records (rows). In each is ID, Name, State, Dues, and about 20 other columns of information.
I need to allow the user to key the ID # and then up pops all the other information for just the ID provided. I will also need the ability for them to then change that information if needed.
So if they key 13WIL they will get Wilson, TX, $50, etc. And then they can make changes to any field needing a change. Add a phone, change and address, etc.
My workbook contains several worksheets each recording the results of equipment tests - one sheet for each piece of equipment. Rather than open each worksheet to check the date of the last test, other 'overview' worksheets pick up and display the last test date from each equipment record using the formula =MAX('sheetref'!A15:A500). Is there a better formula to do this?
Some items of equipment are tested at more than one frequency, ie Monthly, Yearly, etc. Therefore, the entry on row 15 of an equipment record may be column A 28/10/08 column D 'M' and row 16 column A 29/10/08 column D 'Y'. The above formula will only display the last test date, irrespective of frequency. There is a seperate overview sheet for each test frequency therefore, I need to modify the formula so that the date it copies from the equipment record worksheet to the overview worksheet is the date of the last test for the specified frequency.
I have a string of information in an Excel download from PayPal that I need to upload into QB; but, the information needs to be in a different format. The string from the download is:
Property Address - 1116 South PaxonBorrower " Testa and Son Contractors,LLCLoan # - 1402088Acct Executive " Brandon Tully
But, I need it to be:
1402040;TPAPR;141 North Clinton Street, East Orange, NJ 07017;Leslie P Carter
Is there a formula that I can use to extract specific information from the original string into columns so I can then concatenate the columns into the information I need to upload? Or is there a better way to do this? The Property Address and Borrower Name are not static sizes so I can't use a formula that counts spaces.
I have created a userform that allows input of information and then deposits the information on a specific sheet. I am looking for a way to have that information not only deposited on the specific sheet it is already set to but also to another sheet based on a selection made from a combo box.
here is my current
I have a worksheet where users enter an inspection number based on the order a property will be visited, based on the inspection number I want it to automatically enter the information into another worksheet that creates an itinerary which shows this order .
View 4 Replies View RelatedI have a sheet named: tool
In this sheet I have a long list of data that i would like to have sorted out on different worksheets that needs to be created by the macro. The deciding factor for the sorting are found colum B. In this colum there are different text strings. I would like for the macro to look at this text string and copy the entire line over to the respective work sheet. the work sheet needs to be named according to the text string in colom b.
Furthermore in the sheet named tool the first 3 lines are headings and i would like all of these transfered to each of the new sheets.
I have created a gradebook template so that I can keep track of my assignments as I get them and keep on top of my grades. Any way that I can code excel to work so that if my current average in the class ever drops below 70%, I want the color of the text to change to red to alert me that the grade is too low and needs to come up. I'm new to using VBA in excel and not sure how to do it or what to code.
View 8 Replies View RelatedI have a column of data (Column T) which has about 10 different words in it (varieties of fruit). What I want to achieve is based on the data in column T I want to fill Column U with information, let me put it in an example.
Column T has the following words in 2000 rows. Thompson, Crimson, Sable, Superior, Midnight, Ralli.
If Cell T4 for example has 'Thompson' or 'Superior' I want cell U4 to say 'white', but if cell T4 has 'Crimson' or 'Ralli' I want it to say 'Red'. If it has Midnight I want it to say 'black'.
I think this can be done using a vlookup maybe? I don't have to go down 2000 rows to fill this information in.
if a macro or private macro is necessary, I would need basic instruction how to use those as well (I mentioned private macro as I understand they automatically run whenever worksheet info is changed, which I would like in this example.)
I'm simply trying to change fonts in cells based on the inputs of other cells. Obviously, using conditional formatting doesn't allow for this.
For my example, assume the word "MrExcel" is in cells D2, F2, H2, J2, L2, N2, P2 and R2. If I type different font names (Times New Roman, Arial, Bookman, etc.) in the cells to the immediate right of each word "MrExcel," -- cells E2, G2, I2, K2, M2, O2, Q2 and S2 -- I would like the "MrExcel" word in each corresponding cell changed to the corresponding font to the right. I would like the ability to do this for up to 15 different fonts. There will be many rows to this worksheet I need to use this process for.
Is this possible? I'm in a bit of a jam and have exhausted my knowledge of formulas in that they won't work in this case ... I'm assuming I need to delve into the unknown land (to me) of macros, private macros, or VBA.
I have a database of names in one column in excel 2010 and over time I have coloured the text of some of the names green to show that they have been added to a new online database. I want to be able to count how many of the names have been coloured so I can see if it matches with the total number already added on to the online database. I have over 800 entries so I don't want to have to manually count them unless I find I have missed any. Conditional Formatting is greyed out (it is a Shared document so that might be why).
in creating an IF (text is a certain colour) THEN 1, 0 or any others ways I can find the total in that colour. I only have two text colours in my spread sheet, black and light green.
Example of Table.JPG
I am trying to sort/filter the information by columns but I need the information in each row to stay together for instance...
1 2 3 4 5
2 3 4 5 1
3 4 5 1 2
4 5 1 2 3
5 1 2 3 4
I need to sort the numbers in column 3 in ascending order or filter them but i need the entire row to move when sorted so it needs to look like this
4 5 1 2 3
5 1 2 3 4
1 2 3 4 5
2 3 4 5 1
3 4 5 1 2
I am using the list function to sort and filter the data in the columns but when i try to sort them in ascending order i get....
1 2 1 4 5
2 3 2 5 1
3 4 3 1 2
4 5 4 2 3
5 1 5 3 4
I don't know how to write formulas or VBA's or anything but am willing to try.
Is it possible to sort the data and get a result like the second set of data? and is there an easy way to make this fool proof so people that use the spreadsheet will not mess up the data?
I am using Microsoft Excel 2003. I put color in my cells to show contrast with the information in my data list. I used blue and white for my cell colors. When I used sort to put my information in alphabetical order it did not only rearrange the information, it also rearranged the colored cells. My orginal pattern (before using sort) was blue, white, blue, white, blue, white. After I used sort, the pattern was changed to white, white, blue, white, blue, blue. Is there any way I can use sort to arrange my information only in alphabetical order, using sort, without rearranging the color of the cells?
View 5 Replies View RelatedI downloaded a massive dataset in .csv format to work with. My first problem is that I'm having trouble sorting the dataset. When I open the .csv-file every bit of information comes up in the A-column instead of having INFORMATION1 in the A-column INFORMATION2 in the B-column and so on.
View 1 Replies View RelatedI'm trying to make a schedule for some of my employees (300 to be exact) and I was trying to work out a way of speeding things up a bit. I've made plenty of pretty complex equations for working with the task but the solution for what I would like to do now alludes me.
Here's the challenge: I have a sheet where I would like to insert employees within row 4 and then have their shifts shown visually in the column below that, and have the hours they work put into a merged cell (or something similar), like what can be seen in the blue cell under "Employee 1" (C6). What I would like to do is just have something like a VBA detect that a series of cells is merged (red cells) and insert the corresponding time, located within B5:B33, into that cell (with same format as the blue cell). Also, above the employee's name I could put a button to run something like a macro to run the program for the corresponding employee directly under that button.
Essentially I want to merge a series of cells, hit a button, and have excel locate and input the correct times within that merged cell.
I am trying to have rows moved from (Schedule) to (Complete) in the attached workbook. The criteria to have the row moved would be the user-selected "Status" list changing to "COMPLETE" in Column G.
I would like to have the row cut and deleted from Sheet4 (Schedule) once "COMPLETE" is selected from the list in Column G with rows below moving up. The row would then be inserted into Sheet2 (Complete) at the top of the list (Row 7) and rows would move down below the new entry. I would also like to have the formatting remain uniform (every other row fill)
I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
View 2 Replies View RelatedI'm racking my brains as to how I can structure a formula to conditionally rank a value in an array against only those values in the array whose corresponding criteria cell includes a specific letter.
So for example I have a list of 12 values, say 126; 239; 0; 171; 162; 157; 130; 199; 122; 153; 0; 15.
Each of those values corresponds to a heading, say: CDE; DFE; FGE; DFE; ERD; DEA; BDF; DFB; CDE; CEF; CAB; FAB. As you will note some of the headings may or may not be the same and may or may not include the same letters in different orders.
How can I write a formula that ranks in ascending order a given value drawn from the above list (which will be in another cell but which in this case is, let's say, the first value: 126) only against those values whose heading includes a specific character, for example the character C (the character in question will vary and be defined in a specific cell).
As an added complication I need the ranking calculation to exclude any zero values. So in the above example what the formula needs to do is rank the value 126 against a sub-set of the whole array comprising only the values 126; 122; 153; 0.
The answer I need is 2 because, discounting the zero value, 126 is the second highest value.
I have a sheet that has 4500 items on it and want to create a new sheet so I can type in a part number and get the information on the row that number is on.
so in the (new blank sheet) cell a3 will be the input cell and cell a5 to a9 will show the data.
I have a range of cells (A1:K20 for example) and I'd like to have Excel count the number of times a certain set of initials (PT for example) showed up in any of the cells within the range.
View 7 Replies View RelatedBasically I am trying to write a code:
Using a Month specified in an input box eg: July Payday
Look up that month on the "info" worksheet to then copy and paste all the dates for that month on to a worksheet called "Working Hours". This is the code I have at the moment...not much I know, I don't know how to format the code in to tags:
Sub WorkingDays()
Dim LastRow As Long
Dim Month As String 'setting the object of Month being the data we need to find
Month = InputBox("Please enter the month you wish to record, eg July Payday")
'Typing the Month which we need to copy the dates for
Lets say I have a 2 column sheet (A and B). And I want to sort on column B with all the rows that contain a given string, i.e. /g or Δείτε.
Something like doing a find all but then being able to select/copy/cut etc those rows.
How can this be done?
1. When text is entered into a specific cell in the Master Sheet a new Tab is created named after this text and a second string of text i.e the date.
2.At the same time as this TAB is created I would like only the rows with checkboxes ticked in the Master Sheet to be copied to the new TAB(Not the Checkboxes themselves) [possibly using TRUE/FALSE in "B" row].
3.I would like the formulas, formats of these checked rows to be copied too.
4. Additionally the first 12 rows(all headers) and the last row(subtotal row)should be copied too.
The idea of the Workbook is to have an overall options sheet that is customised on a per customer basis with a record saved on a new tab in the same workbook.
I need to create a simple code that will allow me to fill out the userform and then use a cmdbtn within the userform to transfer the information from the userform to specific cell in my excel spreadsheet.
I have a column 'AE' that contains the html code for ecommerce item descriptions. While importing/exporting back and forth with another program and my spreadsheet, the following was added all throughout the descriptions.
%0d%0a
In some places, it may be three or four times consecutively
%0d%0a%0d%0a%0d%0a%0d%0a
Is there a macro script I could run to simply remove this any time it occurs in this column? It is just showing up as text and I am sure in another program it was representing something other than text, probably a space or hard return. Regardless, I don't need it.
See example below: ....
I need a macro to move specific information.
I get an excel sheet with W/C Employer: (whatever) entered below the patient it belongs to in column E. This information needs to move into its own column (H) in line with the above patient information so I can use it in a mail merge to word.
The same thing needs to happen with WC injury date listed in column G. It moves to column I. Then delete the row it was moved from so I don't have an empty row.
Sheet 1 is what I get. Sheet 2 is what I need to end up with.
Check the attached sample file
a. actual data in columns is approx 150 items (that is why i need to hide/unhide columns)
b. there are about 120 persons whom above items are issued depending on expiry of life of item i.e. issue date plus number of months mentioned in row 3. for example if coat was issued to John on 5th Jan then it's life expires on 5th May and so on.
Now what i want
1. by clicking hide checkbox only that specific column should hide or if i click Hide All check box all column in range should hide.
2. as i enter issue date expiry of that item should automatically update on corresponding month sheet i.e for above example 5th May should automatically get entered in sheet May.
I have entered sample data manually in months sheet.
aJITCBP.xlsm
I'm trying to figure out a way to group columns in a bar graph without displaying them in a cluster, but instead in displaying them individually in a specific order. However, I still want to identify each group with a different color. This is hard to describe, so I'll try my best to illustrate what I'm trying to do with an example. Assume I have three groups of data and within each group I have 2 to 3 items in it with different values. In a list the data would look like this:
Group 1 - Item 1 - $2,000
Group 2 - Item 2 - $1,100
Group 1 - Item 3 - $2,200
Group 1 - Item 4 - $3,200
Group 3 - Item 5 - $1,800
Group 2 - Item 6 - $1,200
Group 1 - Item 7 - $2,500
Group 3 - Item 8 - $2,800
What I want to do is create a bar graph that will still allow me to color all group 1 items as red, all group 2 items as blue, and all group 3 items as green while still showing each bar sorted in the order I've shown above and labeled as items 1 through 8. Then, I want a legend to only show the groups 1 - 3 with their colors. To explain, the graph would look like this:
Bar 1 would be labeled as Item 1, with a value of $2,000, and colored red
Bar 2 would be labeled as Item 2, with a value of $1,100, and colored blue
Bar 3 would be labeled as Item 3, with a value of $2,200, and colored red
Bar 4 would be labeled as Item 4, with a value of $3,200, and colored red
Bar 5 would be labeled as Item 5, with a value of $1,800, and colored green
Bar 6 would be labeled as Item 6, with a value of $1,200, and colored blue
Bar 7 would be labeled as Item 7, with a value of $2,500, and colored red
Bar 8 would be labeled as Item 8, with a value of $2,800, and colored green
Then, the legend would only show the groupings of Group 1, 2, 3, 4 and their associated colors.
(By the way... I know I can do this manually by selecting each bar and customizing the color, but I want an automated way of doing this to reduce maintenance time)