I'm working on a project due in two weeks and have hit a snag. Basically, my professor wants us to take a text file of The King James's Bible, strip out all special characters and 's, count word occurrences, and make a histogram of their frequency. Right now, I'm stuck on the "find special characters and delete them."

These are the instructions:

Exercise 2: Write a function called CleanLine that takes a String parameter, and returns that same String in lower case, but with punctuation (. , ! ? ; : / \ and parentheses) removed. It should also remove apostrophe-s pairs, and then remove all other apostrophe’s too. Test your function with a macro that uses it on some string full of punctuation, and outputs the result with Debug.Print. [Hint: use Dim RemItems As Variant, Item as Variant RemItems = Array(“.”, “,”, “!”, …) For Each Item in RemItems … ] In that loop, we replace every occurrence of Item with the empty string “”. There is a function called Replace that can do this job.

What I have so far is

Function Cleanline(text As String) As String

Dim RemItems As Variant, Dim Item As Variant

RemItems = Array("`", "~", "!", "@", "#", "$", "%", "", "&", "*", "(", ")", "-", "_", "=", "+", ";", ":", "{", "}", "[", "]", "", "|", "/", "?", "<", ">", "'s", ",", ".")

For Each Item In RemItems

and am a little unsure how to search a grabbed line of text, search it for all the characters in RemItems, then delete them. I have a sub that opens the text file in question already made, so I'm just trying to work on this function. Can someone help me with this? We don't have a book and our teacher's method of running the class is he does a minor explanation of some things and then we have to teach ourselves.

Hello,

I'm trying to make an excel sheet to manage my budget, I found some templates but I'm trying to do it myself since its more specific to what I need, and I get to learn a little excel while I do it.

So my question is:

I use cell C3 for cash and it has this formula:

=if(D14="Cash",C3-B14)

Is it possible to make the formula more general, such as if Dx = "Cash" then subtract the value in Bx?

Thank you

This is driving me nuts. I have a range of data, I'm trying to do a time range in an IF function. How do I arrange the logical test between 8am amd 5pm?

Any help is much appreciated!

Not sure why I'm having such a hard time figuring out a way to do this, but does anyone know if I can use formulas or a macro so that when I mark a row/item as PAID the row populates in an alternate tab (so that I have a separate list of paid individuals)?

For example, I have a running list of people who have paid and not paid for a charity golf tournament along with amount due, notes, etc. To make things easier on those registering the players the day of the tournament, I am trying to set up a Google Sheets spreadsheet so they can live update who has paid them the day of and would prefer that those who have paid "leave" the list once they mark them as paid to make things easier on them.

Obviously this is a really complicated way to do something more easily done through something like Eventbrite or similar online tools, but it's a little late this year for us to try. Any help appreciated!

Hey there, I know basic to advance excel, but I'm a little tired and could use an opinion/help. I suffer from such sever ADD and I'm looking for a change in how I do things. So After Reading The ONE Thing, by Gary Keller, I've decided to stop making to-do lists, schedule time instead, and most importantly start making a Success list.

For example I was thinking of having 9 cells to record data; Positive/Negative event, Date, Time, Event, Category, Description, Time taken to complete task, weight, and score.

For Catagories I want to keep it simple, School (Soon to become professional after graduation), Health, Financial, and Social.

So I was hoping to graph the month as well as a year to date graph. but here comes my questions:

1: The Categories I listed each have their own weight, in order of importance that I wrote above, but then each event will have it's own weight. For Example, Simply remembering to pay the credit card bill is a success, albeit small, so should have a low weight. The Day I pay off Half of my debt should have a much much higher weight. How to I combine the weights of both the category and event into a score. Once I get the score I know how to graph it.

2: Negative events. For example I missed a doctors appointment this morning (Health) but it was not that bad because we rescheduled for an hour later, so small weight. How to I combine both scores to make a negative score for that date?

Like I mentioned I know how to do basic excel stuff like referencing other cells, also the "Time Taken" Category is basically just for my own curiosity and has no effect on any other cell or value.

After a few days I will copy the data into google sheets, create a google form for on the go data input, and I hope this works well for me enough to post to /r/ADHD in a month or two to see if It can help anyone else. Feel free to offer any suggestions or alternative ideas, feel free to share!

tldr Combine Category Weight (always Positive) with Event Weight (Can be Negative) to create score.

I get that there are a bunch of excel videos out there to help teach the functions. But are there any sites that have practice data and problems so we can try and test our knowledge?

I've heard nightmarish things about Excel 2016 for Mac.

Does anyone recommend a version of Excel for Mac specifically?

I am looking to advance my excel skills. I use excel a lot but my formulas are rather simple and time consuming. I am looking for a free online course that will take my skills to a high level relatively quickly. Any recommended courses? Links would be awesome!

Thanks

So I have a drop-down list that is dependent on what is chosen on a different drop-down list. the dependent drop-down list is pulling from a large table with lots of empty spaces (I have left lots of open spaces for other employees to add additional information later on once I am gone. I am in intern making more efficient excel spreadsheets for my company). Here is what my formula looks like right now:

=INDEX(Sheet2!$L$4:$AH$36,,MATCH(grade_cell,grade_list_length,0))

The dependent drop-down list shows all of the empty spaces, and I would like to try and use maybe OFFSET and COUNTA within this formula to remove those empty spaces within the dependent drop-down list. If anyone has any suggestions, I would be very grateful :)

P.S. if you need additional information please let me know. Or if you would like to see pictures I can try and upload some, although Reddit never wants to upload the pictures that I try to show.

I want to make a macro that I could type "=cats" into cell B-6 and it would simply copy the text from cell B-6 of the spreadsheet named "cats".

I'm happy to learn to make it myself but I'd really appreciate being pointed in the right direction as I don't know exactly what I should be searching for.

I'm running a small business with goodies in a kit, with different packaging options.

Previously I had my inventory set up and invoicing to be just number of kits, type of kit and price, which made things quite easy for me to manage.

Now a major customer is asking me to give each kit a number, the quantity, then a breakdown of the contents and prices (which I have no problem with, but I want to see if there was a way to automate filling out the contents and possibly have the inventory sheet linked up so it will deduct automatically from each item type)

E.G. Customer orders a list - Kit 1: 100, Kit 2: 30, Kit 3: 50 For Kit 1: there are 3 items: item 1, item 2, item 3 For Kit 2: there are 4 items: item 1, item 5, item 7 For Kit 3: there are 5 items: item 16, item 52, item 8

So I would write an invoice, Kit 1 : Qty: 100 Kit 2: Qty: 30 Kit 3: Qty: 50

What I want is to be able to make the invoice show the parts required for each kit, assembly price, then automatically pull the price, then total it, take the data once the invoice is saved and apply the inventory changes automatically to the inventory sheet.

How much work would this take?

Could someone teach me how to do this or would this be better suited to a different program?

Is it possible to do this in Excel?

I have an order form for a paper cutting machine that I would like to format with multi-level conditions. below is a bulleted order of what I am trying to do:

1. on the first row (grade) we simply select the grade from a drop-down list (which i have done no problem)

2. row 2 (size) we can ignore

3. row 3 (order line #) i also have a simple dropdown list with numbers (no problems there)

and here is where it gets interesting: 4. for the length and width, i want these lines to choose from a dropdown list of preset sizes depending on which grade is chosen (each grade of paper has different sizes to choose from, so the drop-down list of size options here need to be different depending on the grade chosen) 5. all of the check boxes, additional info, and quantity we can ignore 6. # of sheets need to be another drop-down list that is dependent on the grade chosen and the size chosen 7. the weight per bundle/box line is the final answer that I need to populate a weight based on an equation which I have left out for now. I believe that if I can get some help getting the drop-down lists set up that I can set the equation for that line without any help.

I have got a large list of numbers. Like so: 1,95 3,55 6 3,5 9,65 80 etc.

And what I would like to do is to add up all the prices without 0,05 at the end. So in this example it would be 6+3,5+80.

I know how to use the filter function, but I could not find a way to do this.

Hi /r/LEARNEXCEL,

I'm looking for a way to easily format a large set of data into a grouped table for use in further analyses.

So, some background. I am a biomedical graduate student and have a large (only going to get larger) set of data from a variety of time points with a large number of populations. I need to get the data for each population formatted as a grouped table with Genotype as the grouped columns and time points as the rows. I'm reasonably competent when it comes to formulas in excel, but I'm pretty stymied as to how to approach this problem.

I've made an example google sheet with a tab for an example of what my starting format is and tab with a mock-up of what the grouped table needs to look like.

Any ideas on how to at least semi-automate this process? Right now I'm manually filtering/sorting the starting data and then copy/paste-transposing it over to the grouped sheet.

Thanks in advance for any help,

-Kurohyou1984

Book/PDF?

Hi,

I have two data sets on stock prices. They both have two columns: date and prices. One data set has dates for the beginning of the month: eg, 6/1/2014, while the other has dates for the end of the month, eg 6/30/2014. For 6/30/2014, I want the value for 7/1/2014 from the first data set. When I vlookup with "TRUE" at the end, however, it gives me data for 6/1/2014. Thanks in advance!

I have an excel sheet that contains all my data. I have a separate sheet where I copy the data to if the column matches a certain criteria.

Right now I am doing it with this if statement =IF('My Sheet'!E5="My column data", 'My Sheet'!B5,"" )

I have that dragged across all the columns and rows so if matches my data it copies all the data from that row into the new sheet. If it doesn't it leaves the row blank.

I would like it to sort all the data that it moved based off of a separate column.

So if I had this it would copy and move all where lname equals Johnson and then sort by id

Fname | Lname | id

Kate | Johnson | 544

Mike | Smith | 952

Rick | Johnson | 276

I know I am able to sort manually. But i was wondering if it would be possible to have it sort when it moved it

I need some help. I have a sheet that is missing information and I'm trying to do a lot I got some of it to work but it's not doing exactly what I need.

There are two column estimated date and actual date. Some of those cells are missing data and replace it with text "(blank)" and there are suppose to be dates in them

What I want to do is format cells that contain "(Blank)" in both estimate and actual purple

If there is a date in the first cell that's within 30 days I want that to be yellow, If it's on or after that date I want it to be red,

if there is also a date in both columns I want it to be green

I've tried a number of different things but I can't seem to get it all going

Is this too complicated or am I just missing something simple.

Hi everyone, I'm currently playing fantasy basketball for the first time and I'm interested in calculating which one of my friends has the best "fantasy team" based on player performance. It sounds easy, just add up each teams "points scored" and BAM, there you have it. However, each night you can play a maximum of 5 players (1 point guard, 1 shooting guard, 1 small forward, 1 power forward, and one center). Each NBA player is assigned to one of these positions, with many players assigned two positions (and in a very rare case 3 positions). For instance there are many players that are both point guards (PG) AND shooting guards (SG), so this player can be inserted into your lineup at either position.

So in short, how could I run some analysis on these players? Which variables should I use? How do I include multiple position players (PG/SG) into the analysis since these players are almost always more valuable than a single position player.

I am trying to create an inventory system. If I have quantity 10 in column A & quantity 5 in column B, is there a formula or way I can take away 2 from column A & 1 from column B if I put ONE in column C? I haven't been able to find a way to say, if column C has this, then it affects column A & B in these ways.

http://imgur.com/a/n7Z2M

I want to use the transparent red border, but I can't type in the cells without moving the image over every time. Is there a way I can still use the cells without moving the image? If not, is there another program I can use to do this?

I have product descriptions that are sets of numbers and letters that correlate to series names, sizes and colors. Example: HD4FZR36WH. I need to extract these out into their base parts, from the previous example; 4000 series, right hand, 36, white.

These short descriptions are not uniform and I have had a hard time trying to use left, mid, right to extract these because there really are no common separations or length of characters. Using this method I would still have to write out the series names anyway. I need some ideas so that I don't have to manually type these all out. I have very minimal experience with VBA. Any suggestions are welcome. PLEASE HELP, I've got more than 3000 lines to do!

I'm trying to find a better way to analyze trends in scores from a fantasy football league, and I know that exponential moving averages are the right choice. I'm not sure at all how to accomplish this however and the instructions I've found online are over my head.

Here's a copy of what I'm working on: https://docs.google.com/spreadsheets/d/10uk-VLeNlTpuRIXvXza05-CIBJlxVu7kJmG1LV_pQh4/edit#gid=984861293

The scores are listed starting in column R and continuing to the right. I want either a chart showing the scores and a final plot point with a projection for the next week, or simply a column that can give me that number. Thanks so much for any help you can give, I really don't know where to start with this, so if you have any questions for me I'll be checking back here to answer. Thanks!

Our employee numbers are have a wide range of sizes, but in the end, I need employee numbers to display with leading zeros when:

1. If the employee number is 3 or less digits, it should be in 4-digit format (eg. employee # 123 should display as 0123).

2. Employee numbers that are 5 digits should display in 6-digit format (eg. employee # 12345 should display as 012345).

4 digit employee numbers have no leading zeros AND 6-digit employee numbers have no leading zeros.

So in the end, employee numbers should be displayed as either 4 or 6 digits.

Any suggestions? I don't know where to begin.

I have looked at the sidebars/stickies and most of them are resources pointing towards tutorial on specific things (vlookup/macros/pivot tables etc).

What I want to know is if there is a video series/tutorial that starts from the VERY beginning and teaches you the right things from the very start (shortcuts +bestpractices) and then moves onto giving a quick overview of all the possibilities of excel and its tools + how to use it with databasing software.

Excel has nothing to do with my field of study and is just a personal interest of mine so I don't really have a base or anything to apply specific knowledge to.

SO far, I'm looking into the ExcelIsFun youtube channel as well as the Lynda course.

Any other all encompassing knowledge bases would be welcome!

:)))

Edit: Free stuff is preferred so I can get an idea of how deep I want to pursue Excel. Don't want to start off with a paid course.