hello honorable excel profesionals.
here is what i want the macro to do:
must start from the top of the colum and count cells with specific data in them:
if ''X'' or ''Xa'' or ''Xb'' -> start counting how many cells in a row have the value specified.
from here two things can happen:
if a cel with a value ''Y'' is met -> stop
if a cell with value '''Ya'' or '''Yb'' is met -> countinue counting untill Y is met
when I say stop I mean that the macro should imput, ona separete table:
1.total number of how many counts where made.
2.how many counts there where with a lenght of 1, how many with the lenght of 2 and so on
here is an example of data line and a table I want to have after macro is done.
Hope that this isn,t a problem to write otherwise il have to do this manualy - about 5000 rows of data...
I have a difficult one I think? I have a workbook in Sheet 2 I have a page with data.
I would like to create a Sheet 3 that would look at Sheet 2 and copy all rows (Range A to V) with the asterisk displayed in Column B... IF MORE THAN 30DAYS AFTER THE DATE ENTERED IN "Column C"....
I am desperately seeking a better way of transposing a large amount of data. By the looks of other posts, my objective is possible with a bit of VBA. Sadly, I am VBA illiterate.
My data looks like this (simplified) [ data is separated by columns "|".
A | May 1 | Jun 25 | Aug 9 | Dec 12 B | Apr 1 | Oct 25 C | Jan 6 | July 7 | Nov 11
I want to make it look like this: A | May 1 A | June 25 A | Aug 9 A | Dec 12 B | Apr 1 B | Oct 25 C | Jan 6 C | Jul 7 C | Nov 11
I have accomplished this in the past, but it involved importing the table into access, then creating a query for each of the "date" columns, then copying and pasting the results into one table. It was a painful experience considering there are over 2,000 lines on the original table (resulting in 25,00 lines in the combined query).
I need to use the Advanced Filter tool to allow the user to filter (in-place) the Guests worksheet while providing the following summary stats: total revenue, ave. revenue, max/min revenue and total number of tours ie the user should be able to enter any criteria below the database to to show only those records (and summary stats) that satisfy the criteria.
To automate the operation of the advanced filter tool, I need three macros. "SelectRoom" and "SelectGuest" macros should prompt the user to enter a value through an input box to filter the data according to a client's name or language tour. The third macro, called "ShowAllGuests" should clear the criteria row and dispay all clients in the database.
The first two macros should include an error message to prompt the user to rerun a macro if no clients satisfied the criteria while the "ShowAllGuests" macro should include a message box statement at the end to display a short message giving credit to the macro author. These macros also require a button each (three in total) in the Guests worksheet!
This is quite a easy one. I am a begineer so I don't know how to do it. I keep getting a runtime error. I want create a macro that does the following. I have this data(it is made up).
What would be the code for this? I want use the code for bigger data?. Also if you have a file which is 100 lines long and your macro does a series of steps for that file. Then you have another file and you want the same steps done but it is 500 lines long. How would you write a macro thatrun the steps taking in the last line and not just a 100 lines.
the macro mentioned below has been assigned to a combo-box form. I have 2 different datasets in the same sheet, they have the same headers (but named differently, myhead and myhead1) and the same adv filter criteria range (named as "dcrit") is applicable to both datasets. So, if an item is selected from the combo-box, both the datasets should get filtered according to same criteria. how i can combine the 2 IF LOOPS mentioned in the code?
Sub myfilt() Application. ScreenUpdating = False If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData If Not (Range("indsignal")) Or Not (Range("countsignal")) Then Range(Range("myhead"), Range("myhead").Offset(1, 0).End(xlDown)).AdvancedFilter _ Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False End If If Not (Range("indsignal")) Or Not (Range("countsignal")) Then Range(Range("myhead1"), Range("myhead1").Offset(1, 0).End(xlDown)).AdvancedFilter _ Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False End If Application.CutCopyMode = False Application.ScreenUpdating = True
if it is possible to write/record a macro that will automatically add a summary/total row immediately below the data that is created from an advanced filter. (XL00)
Assume cell A1 is "ageioslop315555555138lkeameox". Is there a formula that I could use that could match 31555555138, and if it matches put the 31555555138 in cell A2. You can probably see where I am going with this, there is several thousand lines that has sporadic descriptions that I need to find out which ones have certain #'s that correspond with info. on our end.
with a complicated average function. I’ve made a tender comparison sheet which compares different suppliers and their prices. The suppliers are assigned a region (with data validation when they are added and I want to get an average on the specific article (on every row) on all suppliers in the same region.
I wish to perform a VLOOKUP on my spreadsheet. I have my table to be called upon all setup and can get the VLOOKUP to work, but I need some help finishing it.
I want the VLOOKUP to take the information from A1 AND C1. Can I do this in a VLOOKUP?
So instead of the VLOOKUP just taking the Lookup_value from just A1 or C1, I want it to match BOTH before it shows me the result.
I am working on a large Audit project for one of my many bosses and I am a little stumped so I came to the experts. I have a list that is organized randomly and we need to have 95% of the cells deleted. I don't want to have to go through each time and calculate that out each time.
I am sure that I would have to find the last cell used in one of the columns and then multiply that by 95% and then delete from that row down in the sheet. (Since the sheet is already organized randomly it can delete the bottom 95% of rows.) I am not sure how to put it all together.
Column J1 is called: Resource Name which has 1,000+ staff other columns have corresponding Hours, Project names etc
I want an advanced filter where I select COLUMN J:J and filter this whole sheet based on say 25 names (in the format they're in) e.g
Frank, James Wilkonson, Paul, etc
In the Advanced Filter, Ive selected J:J as LIST RANGE, but how do I input an OR statement in the Criteria, as above i.e where name is Frank, James OR Wilksonson, Paul OR
Alternatively, I have the list of the 25 names in Sheet 2, can the Advanced Filter do a VLOOKUP then filter entire Sheet based on the names provided?
I have a condition in advanced filtering as >90%. However, I would like the "90%" to be calculated via a formula in another cell and this condition to reference it. Is it possible?
I have a spreadsheet that currently includes the following formula:
=VLOOKUP($C42,$A$42:$B$61,2,FALSE)
Col A is numeric output Col B is staff names Col C specifies top ten output numbers from Col A
The above formula goes in column D and It returns the staff names with the top ten output. My problem is that if two members of staff have the same output it formula will repeat the name only one staff member in both rows. Is there any way of altering the above formula so that it will show both staff names in each row?
My excel workbook has 3 sheets, payments, invoice data and invoice. Payments and Invoice Data contain a table that has been exported from access, I have used an advanced filter macro, to filter records based on payment id, the button for this macro is on the "invoice data" sheet, the same button will filter records even on the "payments" sheet. You will notice in the payment sheet, the total payment field is blank, i used cell referencing to equate the cell C21 in the payment sheet to the cell H20 in the invoice sheet, so basically, once the sort button is clicked and advanced filter is done, the total payment will show in cell C21 in the payment sheet. I would like to create a button that runs a macro that will enable me to save the data i have clicked on cell C21 on the table above it, so i could import that table back to access.
I have a file that has a column of dates. I would like to use advanced filter to filter anything with a date that is <today()-1 but I can't seem to make the fomula work. I can make it filter on =today()-1 but when I use the < it doesn't calculate today's date and gives me nothing.
I've been looking around for a way to use the countif function in excel with a wildcard, I have a excel list with a column containing data which looks like: ....
I know we have the V and H lookup functions but... what I had invisaged for my colleagues sheet to do was to have excel look down the column (V) and at the same time across the rows (H) to return a value but in the same formula (a bit like school times table grid squares).
i.e. for 7 * 8 it would scan the left most column for the number 7 then at the same time, pan the first row for the number 8 and see where they met (for standard simpleness, column A is labelled 1-10 and row 1 is labelled 1-10, so the result for 7*8 would be in cell H7)
Basically I have set up a list on sheet1, this is now on a drop down in sheet2 and my raw data in sheet3.
I want the table in sheet2 to just display the product items I select in the dropdown but it is displaying all the raw data (although is updating when I update the raw data)
Granted I copied the code whilst researching but I'm just not quite there.
Could someone possibly look at the attached and let me know where I am going wrong. Also as you can tell I am very new to teh VB application in Excel, could anyone reccommend a good place to start learning the very basics?
why the Advanced Filter works in the macro but not in the UDF?
Code: Public Function strUniqueVal(rng As Range) 'rng variable must include sheet name Dim c As Range rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
I have a large amount of raw data that I import via a text file into Excel. Here is an example of the data in order for me to illustrate my question.
USD COUPON ENTRY PREVIOUSLY DELAYED S/N 0243**** dfssdfsdf S 9.37500 07/02/11 USD REC. 23/07/07 DUE 07/08/07 CPN.AMNT USD 23.437500 Q NAR. FUNDS RECEIVED
SCA. USD 10860000 ----------------------------------------------------- NET USD 254531.25+ 07/08/07 ----------------------------------------------------- DACE REFERENCE IS *******
On a seperate workbook I have a list of money amounts which I need to find the S/N # for. So if you look above, there is a money amount and a S/N within the raw data. My question is, what can I do to take the money amount from the first workbook and search for that amount in the raw data, then return the S/N number that is within that same area? And do it via a macro or advanced formula so that I can do I many times over?
Keep in mind, even when I delimit the data, it's all in different columns and rows, and never consistently spaced.
At my job, employees accrue X hours vacation per 2 weeks worked. This makes it difficult for everyone to plan vacation (IE - will I have enough vacation in January to go on vacation for 2 weeks?) I'm trying to create a calendar in my spare time to help out, but it's turning out to be quite a bigger task than I've ever attempted in excel. Therefore, I'm coming here hoping someone will point me in the right direction.
I found this: [url] It does the majority of what I need. However, as it costs money, I'm afraid they've locked down editing of things and I won't be able to add functionality to it. It'd be annoying paying them and then finding out I can't add vacation days earned, as the spreadsheet is locked.
Features I'd like added to the above program: - Add another checkbox (or similar) to show hours of vacation accrued, as well as an original date and hours to start counting from. - Highlight days corresponding to what kind of day it is. Weekends = red, holidays = green, vacation = yellow, etc.
What I started with: [url] This gives me a calendar with the dates in the right place. I've created macros to identify holidays dates. It's starting to get pretty annoying using conditional formatting on everything to highlight days. Especially since I have to do it for each of the 12 months, as it has a hard-coded month cell in there.
I have a spreadsheet where a charity has a list of donors (about 500 rows) and the amount given over a number of years (1999-2007 columns B to I). Say for example, one donor gives in years 2000, 2002, 2003, 2006 and 2007 I need the conditional formatting to realise what number gift it is and apply the appropriate colour to the cell with the value in it (e.g. 1st donation white background, 2nd donation yellow background, 3rd green, 4th orange, 5th purple). Obviously different companies donate in different years so the code needs to apply to the whole sheet when the donations occur.
I have a worksheet that conatins multiple columns that are populated from a sql query.One column is a parts list.This has 1705 rows.
I have another worksheet that contains a list of parts in one column.This list is varying in row length.
I want to be able to produce a list that only contains matching data , including () around parts and if not too difficult an indication of data that is not in first list but is in second list.
When I do an advanced filter using the second list as the criteria i am not recieving all of the data from the filter , ie in the first list there is parts in (1234XYZ ) but in the second list the part is 1234XYZ , this part is not being resulted in the advanced filter unless I put brackets around the part in the second list . There is no way of knowing which part is in brackets in the first list.
I have two worksheets that are formatted differently. One has a list of employees and the other has the same list of employees but with associated employee IDs. I need to pull the employee IDs to the other sheet.
Let's call the two worksheets A and B. On Worksheet A (the one I'm working with), here's the format (column A):
Employee Name Doe, Jane Doe, John Roberts, George
In worksheet B, here's the format (column A | B | C):
Last Name | First Name | Employee ID Doe | Jane | 1105 Doe | John | 1106 Roberts | George | 1107................
