I have one column with a list of 2000 rows like this
apple
banana
carrot
dog
elephant
fish
goat
hat
I want to do is put all those lines of text / rows into one cell with the word "or" between each phrase. In other words in one cell all the above should be made into "apple or banana or carrot or dog or elephant or fish or goat or hat" . There should be no breaks.
Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.
I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):
This would be the data on sheet 1:
Name Bonus Commision
Tom $45 $50
Mary $25 $75
Mary $30 $80
Tom $60 $50
Tom $90 $25
What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:
Tom's Sheet:
Tom $45 $50
Tom $60 $50
Tom $90 $25
Mary's Sheet:
Mary $25 $75
Mary $30 $80
I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.
Is this possible to do in Excel without using a macro?
I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.
View 11 Replies View RelatedI have a spreadsheet where on a weekly basis data is copied in to various tabs. I then have a "formula" tab where I have a single line of formulas which look up the various data tabs and extract the results I want to show.
Currently each week, before I import the new data into the various tabs, I copy the last row in the "formula" tab and paste to the line below it. This contains all the working formulas. I then paste values only on the line that I copied, thus "locking in" the values it calculated with that weeks data. This means that each row then contains the results with that week's data, and this will grow week by week.
What I am looking for is a formula that automates this process. So let's say that row 30 is the final row of data on my formula tab, it contains the formulas I want to use. I would need a macros that does the following:
1. Looks up last row (row 30)
2. Copies last row (row 30)
3. Pastes to next empty row (row 31)
4. Pastes values only to second last row (row 30)
I'm not too great with writing macros, I've found plenty that can find the last row, but I can't get them to work to highlight that row.
I have a workbook of contracts that I have designed in excel. Each worksheet tab/title is the contract number, i.e. 3487. On each individual contract I have the contact name, address etc. Each cell/field is in the same position on every contract. In other words, every B1 is the Group name, every B2 is the first name, every C2 is the last name, etc. What I'm trying to do is to make a new page with all of the contact info on it so I can make labels etc. I hope this is clear.
View 5 Replies View Relatedi have to make a synonym list out of existing wordlist for my professor. As far that’s the most boring job I would like to ask you people, if it could be automatically done by a vba code. I don’t think that that’s technically possible but I hope though that I am wrong and that some one here can help me. I will try my best to describe the problem as good as I can. Please let me know if its not precise.
Here we go!
I have a wordlist containing four columns A,B,C and D. In column A we have got Source Words(sw), in column B word specifications1(ws1), in column C word specifications 2(ws2) and in column D we have many meanings and target words separated by comma (tw1,tw2,tw3,…) for (sw). The interesting columns for our problem are only A containing words (sw) and column D containing meanings and synonyms(tw). The wordlist is supposed be get larger through finding, enhancing and adding synonyms in the same list. The difficult thing would be to make a macro with two modes. In first mode which asks each time before it adds or enhance a cell and in second mode the macro should do all the work alone without asking the user. The existing wordlist looks like: ...
what im trying to do right now is get some scripting to work better
the following is what the macro recorder gave us
what i need it to do is read how many rows are in the range and put the totals row one after the end. now it seems to be set to 507 and one wksheet has almost 700. can someone help me decipher all this.
thanks
kev
Sub General_Reports()
'
Range("A:D,F:F,G:G,H:H").Select
Range("H1").Activate
ActiveWindow.SmallScroll ToRight:=4
Range("A:D,F:F,G:G,H:H,I:I").Select
Range("I1").Activate
Selection.Delete Shift:=xlToLeft
The following code adds a new row at a given point (Based on a fomula in a column returning "1")
The code works well BUT I want to merge this new row from column A to coulmn M
Im not sure how to do this, (I dont want to merge the entire row)
Example Code:
Sub ExtLne()
Dim Lastrow As Long
Dim I As Long
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
I = 1
While I
In it I have a list of different profiles (From autocad Civil 3D). With a column for the station value, and a column with the elevation. These profiles are arranged all below each other. In the example xls, I've limited it to 2 example profiles to make it a bit clear. The 1st few columns come from the export out of Civil 3D (with empty rows between them, but these are easy to delete). For each station of the profile, you have an elevation
What I need to do is calculate a 3D length for each profile. So manually you can do this with A=(B2+C2)^1/2, and then make a sum. Which is not really a problem if you have a list with 2 profiles. But sometimes we have a list with 100 profiles.
Optionally, it would also be handy that we then get a list in another sheet, with in 1 column the profile names, and next to it the 3D length.
My main question is: Is this possible with VBA? (I would assume it is..) Next, it would be handy if I could be directed in the right direction to produce these routines, because I don't have that many experience in vba.
1. Is it possible for this hyperlink box to only be displayed when ALL the questions have been answered? i.e. All drop down boxes have been used?
2. Is it possible for the result (along with their individual names and scores) to be saved on another sheet? This of course would be supplemental (i.e. When the next person finishes the test, their name would be next on the summary etc.)? I would then have a whole list of who had taken the test...when...and what their score was!
3. Finally, and I know I'm pushing my luck, but is it possible for this summary sheet of names and scores to be hidden?
I have a bunch of excel files in a folder, and I want to make an excel spreadsheet that makes a list of them all. This drive is a shared drive and their are different authors for files. I have already made the list, with the file name, date modified, date created, etc. However, I am having trouble importing the list of the authors for the files. Is there a way to automatically import a list of excel files in an excel, with an "authors" column? I can't do it manually, for their are hundreds of excel files.
View 1 Replies View RelatedI have a list that looks sort of like this:
Last Name | First Name | Date | Service Day
Smith...........John....................Monday
Jones............Alex...................Thursday
etc..
This list goes on down for like 50 cells. Anyway, I'd like to make a new list based on Service Day so that I can organize the names under the service day so it should look something like this:
Monday | Tuesday | Wednesday | Thursday | etc..
Smith, John..............................Jones, Alex
etc...
I tried using the vlookup function but then it confused me when i also needed to match the names with their respective service days.
I have a table where I need to make a drop down list in two consecutive columns (G and H). The list in column H will depend on the Option chosen in Column H.
Column G
Compensation
Payroll
Column H
COMPENSATION
Savings Box
Update Information General PAYROLL
Payment Inquiry Update Information Severance Pay
As shown above If you select Compensation in G you will have those three options in column H and the same for Payroll.
How do i make a drop down list in a cell show Select One in the cell when the cells previous contence (Option selected from drop down list) have been deleted. E.g Cell E5:E400 must show Select One in the cell and when clicked on show drop down list I dont want a input message box or error box i can do that with validation
View 1 Replies View RelatedI need to make an automated way of extracting a list of IDs and the associated cost by a date range, say 6/1/2015 to 12/31/2015, plus the starting letter of the ID. I need the data to then fill a form where it can be sub-totaled. There are several additional columns of data that are not relevant so I know I can create a pivot table and then filter the data, but then I need to copy and paste the filtered data into the form. Is there a way to do this without me copy and pasting everytime?
I added a test worksheet to show the data and form.
Basically I would like to copy rows where the name of the person (in Column A) matches a separate this.
E.g.
Name
DOB
Peter
30/9/1956
Jim
09/1/1923
Paul
01/2/1966
Lookup list:
Peter
Jim
In Sheet2:
Name
DOB
Peter
30/9/1956
Jim
09/1/1923
Note that there are many names (this is just an abstract) so I think it requires a macro.
So far I am capable in creating a loop that deletes a row with specific parameters (like font color is vbred). But this requires me to re-run the loop because the current approach I am using ("for each" loop) is causing the loop to jump over a row that I want to delete.
For example:
R1 To delete
R2 To delete
R3 To delete
R4 To delete
**After running the loop I still have
R2 To delete
R4 To delete
Is there another approach to identifying, selecting, and deleting the rows besides using a "for each" loop? Could I do this in one go?
How do you make an active-cells value / text a hyperlink?
View 1 Replies View RelatedI have a spreadsheet with rows of cells which are either blank or have text in them.
Is there an easy way to write a formula which will make the cells which have text in them bold and make the cell colour grey. But if the cell is blank keep the cell white.
How would I make a msgbox text flash/ blinks in VBA? Is there a way to make the msgbox flash/blinks as well?
View 1 Replies View RelatedI'm using excel to open a new word document, stick some text on different lines and then save and close. The trouble is, I want to bold the titles but when I do I either bold or unbold the entire document. The codes looks like this:
With wrdDoc
With .Content
.InsertAfter "TEST"
.Bold = True
End With
.Content.InsertAfter [Word_WordCount].Offset(0, 1).Value
.Content.InsertParagraphAfter
, etc....
I have some text that I am having to analyze which comes out of our computer looking like this:
N/0/N/Y/N/N/N/N/N/N/N/N/N/N/N/N/N
I use the text to columns function and headers above it to translate what each field is.
As I'm doing my testing if I do this once, I can keep the file open and any time I paste a similar entry in it takes care of the formatting.
But, is there way to make the file do that permanently? I want to let the other testers use it without making them have to go through the steps of clicking on the delimiter, etc.
I track inventory/sales for a very small sole proprietorship. I'm looking to track the number of items that I sell based on the text that I enter in a certain column.
Specifically, I want to have a visual chart/graph to show the percentage of each size/color of an item that I sell, as I sell it. When I enter the words "3T pink" I want the chart/graph/whatever to show another quantity of this category. As I sell a new item, I want the chart to reflect it.
The problem I'm having, is that the graphs/charts require two data sets, but I want the graph to calculate it for me! All I want to do is enter the text for specific items, and when the text occurs subsequent times, I want this reflected in the graph/chart.
It seems so much more simple in my head....basically I want a dynamic chart, not based on a static data set.
EXPENSE MASTER 2013 sample color.xlsx
I have numbers that will display in column G. I have payment types entered in column E. So if 'C FUEL','FA','C M/C', is entered in column E I want the number in column G to be red.
Im trying to add value to a data validation list using the guide at [url]
The only issue I have with it is that my validation list is in another sheet, and I can't get the code ti work with the changes suggested in the comments at that site.
I have the following vb to insert a row above every row in an autofiltered list (autofiltered for nonblanks).
Problem is that if in the autofiltered list there is not a gap then it inserts a row for each line where there is no gap
SO for instance if in the filtered list cell A500:a505 have no gaps between them (meaning all are nonblanks) the vb will insert 6 rows above A500 and leave no rows inserted above a501, a502, ...etc
I have approx 10,000+ rows and there are approx 550 rows that I need to insert a row above (but want to automate/save time) by having the vb do it.
Code:
Sub InsertRow()
' Insert a row between each Client/Cycle
'Will have some manual cleanup after this step
[Code].....
if anyone knows some VBA code to filter on the next line down in an autofilter list.
My spreadsheet has a column with the names of people, which can appear multiple times in no particular order.
I have a macro that I run several times -- once for each individual in the column. The way that I currently do this is by manually using the autofilter on that column to select a person from the autofilter dropdown list. After I've manually filtered on a person, I click a button which runs the assigned macro. I then manually select the next person in the autofilter list, and click the macro button -- over and over until I've done this for everyone in the autofilter list.
I'd like to add some code at the end of my macro so that the macro ends by automatically filtering on the next person (line) in the autofilter list.
I would like to know how I can get excel to randomly select one line of data for each of the people listed below (so one for bob, one for jenny, one for Trish etc.). I have been trying a mixture of vlookup and rand but to no avail, below is just a sample list but I would like to try it on a large set of data. I have been able to get a random transaction but not one for each person.
Code:
a1
b
c
d
e
f
g
h
i
j
2
3
4
REF_ID
NAME
DATE
BILL TOTAL
[Code] .....
Let's say I have the following file which is imported via a macro which comes from a txt file. [Apologies it's 7mb zip file] http://www.fileden.com/files/2007/8/...mpNotFixed.zip
The way the txt file is that two rows make up one row, but SAP spits the data out into two rows. Now there are specific things that I need from these tabs. The way i can get this data is to perform sort by descending on the tabs. When you sort by a descending you get the data that i need [at the second half of the worksheet]:
Basically what I want to know when i create a macro, how can i get it do delete all the rows above a specific row. In the attached file, go to DataImport2.
I would like a macro to find the word "English" in column C and then delete all the row's above it. I cannot record this because it copy's the specific cells, which i do not need. DataImport3 is how the data comes out, All i do is sort by descending ... and i need help as stated above to delete all unnecessary data.
Simple question: Can an Excel Macro insert a line of boilerplate text under the last thing copied to a worksheet? I want to paste something into a worksheet, and add a message under it, and a blank line.
View 5 Replies View Related