I have a huge list of accruals and payments. Accruals (positive) are entered, and at a later date are offset by the payments (negative). Im trying to make a schedule so i can determine which are left over.
This is easy to do manually for a small amount of rows. However im dealing with 5000 rows and i do not want to manually match it would take many days to do.
Ive tried a duplicate remover. To get it to work i made an absolute value column for the negatives, and compared it to the positives column to find the duplicates. This works to a point. However, If i have three accruals for 100, and one payment for 100, all are identified as duplicates which obviously is not what im looking for.
I need to get it where one accrual is matched to one payment. if there are 3 accruals, 2 payments, 1 is not a duplicate. if there is 3 accruals, 1 payment, 2 are not duplicates.
I have a list of serial numbers. There are many groups of 8 same serial numbers, where group consists of 2 projects with 4 SN per project (because of 4 different events). I want to create a formula that marks one project with 4 out of 8 SN for deletion based on set of 3 dates assigned to them. In short I need to count 4 rows per project as one unit.
Serial Project Event description Date1 Date2 Date3
Here is a list on what information matters when making decision if to mark project for deletion or not.
1. Project1 has no Dates entered compared to Project2. Mark Pr1
2. Project1 and Project2 has no Dates entered. Mark Pr1 (random, does not matter which should be removed)
3. Project1 has 2009 Dates, Project2 has 2011 Dates. Mark Pr1 because dates are older
4. Project1 has less Date entries filled than Project2 (same year). Mark Pr1 because less Date fields entered.
I can somewhat do it for separate rows, how I can make these rules apply for whole project as one unit related only to one SN at a time. Biggest problem is there is no pattern of dates entered. Sometimes one row can be filled another missing out info and etc.
I am attaching a file with an example of a spreadsheet that I am trying to sort out. In this example I have 3 samples (I could have many more). Each sample has 8 columns associated with it (N, M, I, F, S, MS, KM and KD). The length of the dataset is different for each sample. The MS column is the same as M but contains a few zeroes. What I am trying to do is:
1) generate one column (MSA) containing only unique values (no zeroes) from columns MS1, MS2 and MS3. The unique values should be selected within a specified tolerance (for example, 0.001, which makes 52.00706 from MS1 and 52.00701 from MS2 duplicate values although they are not exactly the same)
2) generate 3 columns ( named SS1, SS2 and SS3) with sorted columns S1, S2, and S3 so that for each value of MS in column MSA each of the three columns will list the corresponding value of S1, S2 and S3 (zero if there is no corresponding value)
I have five columns D3 to H3 all with different amounts/currency value. I'm looking to get a formula or use conditional formatting to scan those five cells and highlight one of them green when Excel has determined which one is the cheapest out of the five.
I publish a green building mag, and we run an enquiry system for readers on our website. This is the link: [URL] .......
The idea is that readers tell us some info about themselves, including what types of products/services they'd like to receive information about, and we pass their details on to advertisers offering those products/services.
When someone fills out a form, we get data in Excel on their enquiry. It's all in one row - name, contact details, and then the products/services they've enquired about are included in one cell, separated by commas.
So for instance, suppose a listing currently came in as follows (column headings in brackets:
(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) insulation, solar panels, windows
Essentially I want it to run like this:
(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) insulation (name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) solar panels (name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) windows
I have a long list of days and the flight hours of a helicopter in each day. The first two columns are the Date and Flight Hours, at the end of each month there is a space and a cell that says "Total" and may contain other words.
I want to delete every row in which the Flight Hours column contains a 0, a word, or anything that is not a number. This way only the days in which the helicopter has been active will show.
So far I have written a code that deletes every row where there is a 0 in the Flight Hours column, but i would like to delete everything that is not a number, such as a string value, a blank cell, etc...
While combing them into one cell, there will be no repeat of the same number. So the outcome would be
1,2,3,4,5,7,9
The main point here is to merge the two cells together and eliminate the repeated numbers and sorting the numbers in a cell. Or if anyone knows series of steps not just one function to get to this.
I am trying to extract some data from a cell leaving the rest of the cell untouched.
I would like to extract C:UsersDanMusicIan MusicKaraokeKaraokeBob Marley & The Wailers - Jammin'.mp3 from the cell leaving Bob Marley & The Wailers - Jammin'.mp3 in the. I have to do this to 3000+ rows.
I have a cell built into my spreadsheet that serves as an error check (i.e. returns the word 'ERROR' if certain criteria are fulfilled on the spreadsheet). I'm trying to write something into a Macro that will check this cell and not allow it to run if it states ERROR, returning a dialog box to notify this, is this possible?
Is there a way to modify the following macro (graciously provided by user Alkey) so that it simultaneously alphabetizes the list as well? Actually, I would prefer a second macro, because I don't always want to alphabetize my lists. So, I guess I would need a function called something like Concat_Range_Alphabetize.
I'm trying to get this workbook to alphabetize I have worksheets name A, B, C etc... and would like to creat a button when click on it, it would alphabetize the whole workbook for me...
The codes below was able to alphabetize each worksheets for me, but is there a way I can loop this? just encase I need to add more worksheets...
I have a Master Data sheet for the input of personnel information. This information is then used in different Data Validation formats to be referenced by other sheets. Column P, I use the Concatenate formula to put together a needed grouping (Last Name, Shift/Kelly) taken from the Master Data sheet. I would like Column Q to then alphabetize Column P by the last name and also drop the blank cells. I would like this to be an automatic function, so as personnel are added or deleted, the alphabetical list will be updated as the information is changed.
I hope I've explained it well enough. I've attached a sample sheet to help make it easier to understand. I would prefer to do this via a forumla and not a macro, but if the only option is to use a macro, then so be it. Is this at all possible?
I have a significant number of comboboxes on a multipage userform. Almost all of them are populated dynamically (it takes the user choice from one combobox, looks for that value on the spreadsheet and if it finds it, it takes whats in the next column and adds it to another combobox).
My problem is that all the items in these other comboboxes are not in alphabetical order. I'd like to find some code that will work for all my comboboxes so that I don't have to have the same code over and over again for each field.
I found the code below and that works to alphabetize the dropdown, but I still have to use it over and over again and change it for each fieldname (in red). I tried to use a variant and just make the value the same as each fieldname before it calls the sub and whats in red below was just my variable but that just crashes my excel.
I've got a bunch of worksheets that I'd like to re-arrange in alphabetical order. Anyway to do this quickly? Or do I just have to drag and drop them myself? (WXP, Excel XP).
I have a worksheet with 6,500 rows of data. In each row, there are up to 6 cells containing a name in "Last, First" format. I need to alphabetize the data from these 6 cells so they go across the row in order left to right, alpha by last name.
Example of my data layout:
Column 1 Column 2 Column 3 Row 1 Jackson, Bob / Anderson, Lisa / Cardinal, Mike Row 2 Tomlinson, Steve / Dodge, Sarah / White, Brian
I need them to be in this order:
Column 1 Column 2 Column 3 Row 1 Anderson, Lisa / Cardinal, Mike / Jackson, Bob Row 2 Dodge, Sarah / Tomlinson, Steve / White, Brian
Is there a formula I can apply that will pull from only the portion of the data before the comma in each cell? Or do I need to separate out all of the names so the first and last names are in separate columns for this to work?
Problem #1 - I would like to have several columns of alphabetized words one a page (no problem so far). I would like column "a" to automatically continue to column "b" and then to "c", etc... when the columns reach the bottom of the page. The resulting effect is that all columns on the entire page are filled before starting a new page.
Problem #2 - I would like to have a cell at the top of this page where, after I placed a word in it, excel would automatically place the word in it's proper alphabetical place in the above described list.
I have about 10,000 rows of product descriptions that I need to split. I don't want to cut and paste all 10,000 of those. Here is an example of how they look now(all in one column and I added red to show what I want to CUT):..........
I would like to cut the text in red and paste into the next column over. Is there an easy way to do that? Something like: -find the first digit in the cell and select that and everything to the right in that cell -then cut and paste that in the next column.
I am trying to find a formula as follows: Go down a column When you find a number put a 1 in the column to the right. Keep going down, if you find the same number skip it until you find a different number and put a 1 there. Keep going in that manner skipping any number you have already seen. I have attached a sample file of what I'm trying to achieve.
way to remove those highlight in red by any function with the following criteria.
When the period (i.e. date 1 and 2) are the same, -Keep the cheapest available (if price is 0, it means unavailable) -Keep all cheapest even when their price are the same
My spreadsheet has more than 200 columns and 5000 rows of data and blanks. When I select all columns and rows and remove the duplicates, 6 records are removed. I need to only keep and be able to identify those 6 records. I could use something like =IF(COUNTIF($A$2:A2,A2)=1,1,0) if I had only few columns that would identify dups but since there are so many columns it would be prodigious job to create one formula for each column and filter through 200.
Im using a table so I can sort diffrent catagories and I'm trying to put other functions to the right of the table I noticed as I change the sort on the table is moves the information outside the table as well is there a way to seperate that information or do I just need to move it above my table?
