1. One sheet (request type wise) contains request type and the phase which it belongs to. eg request type A belongs to phase 1 and request type B belongs to phase 2. (like wise there are 212 request types divided into 8 phases)
2. The second sheet contains raw data (request type) where the request types are randomly arranged.
What I am trying to do is:
Compare the raw data of sheet 2 with standard data in sheet 1 and paste the phase to which it belongs to with a help of macro/code, since the standard data (212 request types with 8 phases) is huge.
I have a spreadsheet containing donation data for a non-profit organization with over 25,000 rows. The spreadsheet contains a lot of data, and is sorted by donor name, donation date (when the donation was made) and the feedback date (feedback on how the donation was used).
I'm looking to analyze how many transactions were made by a donor within 10 days of receiving feedback for a previous donation. The condition to check for is
(donor name is the same) AND (donation date >= feedback data for a previous donation) AND (donation date <= feedback data for a previous donation + 10 days).
How do I best perform this comparison? For instance, a donor may have made 20 donations, so I need to compare these donations amongst themselves and flag the ones where the donation date within 10 days of the feedback date for any other donation. I need to perform this exercise for each individual donor.
I have 2 worksheets. One has Employees and the devices they have. Last name, First Name, Device each in their own colulmn. Many have more than 1 device so they have multiple entries on seperate rows.
Another worksheet has Employees and thier location. Last name, First Name, Location. Again, all on seperate columns.
It would look something like this
Sheet1 Lastname Firstname Device Johnson
[Code]...
So I'm tasked with combining them into 1 sheet with last name, first name, device and location. The issues I'm having are:
1) A team member could have multiple devices 2) A last and/or first name can appear many times, so a simple Vlookup against lastname won't work - it has to somehow also compare against both.
I need to find the total $ collected on repair orders that contain customer pay and warranty repairs. The problem is that C and W show up on differerent rows, if an RO has 4 lines 3 may be c and 1 line w. I don't know how to use pivit tables to get the info I am looking for, or if that is the best way to fnd my answer.
My question is about creating User-Defined Data Types. Is there ANY way possible i can create a User-Defined Data Type that declares a variable of another User-Defined Data Type instead of the Pre-Defined User Types like String, Integer, etc?
The following explains my problem in more detail.
I know to create a User-Defined Data Type at the top of the module before any procedures. Like this:
My question is about creating a hierarchy of Data Types.
I need to code 3 Data Types: 1.) MealPlan 2.) Meal 3.) MealItem
MealPlan represents a daily meal plan. It should have a certain number of meals. An example of MealPlan would be "Healthy" and have a total of 3 meals. Meal represents a single meal. It should have a certain number of meal items. An example of Meal would be "Meal 1" and have a total of 3 meal items. MealItem represents a single meal item. It should have a certain number of protein calories, fat calories, carb calories. An example of MealItem would be "Broccoli" and have protein calories of .2, carb calories of .8, fat calories of .1.
By using Data Types, I hope to create meal plans, which are made up of meals, which are made of meal items. The following code creates a Data Type hierarchy for two Data Types. I've examined it closely but i'm still having a hard time recreating it for my needs as stated above.
I have three items I would like to graph, on one chart. The first is %Complete. This is a number figure. The next is the date the item is due to ship. Obv, this is a Date. The third is today's date. Basically, I would like to display in the same chart so that we can see how far a long a project is, in relation with today's date and how much longer we have until it needs to ship.
This is the current display of the chart:
Is there a way to show the ship date, as the 100% complete mark? In order to make it display half-way decent, I had to format the left y-axis to go above 100%. I first tried to format the right y-axis, but was extremely confused to setting values. I figured out that the end date is somewhere in the 40,000 value mark? WTF is this about? Is 1 equal to 24 hours? I assumed that was the case, but then when I pick the value it should end with, it doesn't seem to react that way. I want the right axis to start with 12/01/2011 and end with 5/01/2012. How can I achieve this?
I have a csv file. When I open it by double clicking, all the finance figures appear as currency with the currency symbol. I can use the SUM function on these and it's perfect.
However, when I open this file using a macro, these same numbers look the same, except that they are now left justified and are text because the SUM function no longer works. If I select a range, I get the count of the number of items selected showing at the bottom of the screen but not the sum.
I have tried opening the file using the OpenText function which has several ways of setting a date format to the fields but nothing for numbers.
I have even tried, in the macro, selecting a cell with numeric 1 and then selecting the range and pasting the 1 as a multiply function but this doesn't work either.
I an newbie at VBA but I took some short programing classes back at my college days. I am trying to declare an array with different data types and since that seems to be imposible for what I gather then my other option is to declare what I remember as a structure.
Putting the final touches on a project I have and the last element doesn't work 100%. the scenario: I have a function that I wrote based on the user's input of the desired column to perform the operation. As a method of error-trapping and ease of use, I set instructions for the code to activate:
1) if the user highlights multiple cells of the desired column 2) enters the letter of the column (up to "z", not case sensitive) 3) enters the number of the column
Sub Button5_Click() Dim ColumnUsed As Variant ' find number of columns selected For MyColNum = 1 To Selection.Columns.Count Cols = Cols + 1 Next MyColNum 'find number of rows selected For MyRowNum = 1 To Selection.Rows.Count Rows = Rows + 1 Next MyRowNum..............................
I often find that it would be useful to have a data type for geographic coordinates (i.e. latitudes and longitudes) that would e.g. permit/facilitate formatting negative values as South and West, direct subtraction of one latitude/longitude from another, conversion of degrees-minutes-seconds to decimal degrees, properly formatted axis labels when using Excel to draw maps (at the moment, I am editing the axis labels in the PostScript file by hand, when I don't use chart labeller to paste axis labels in) and so on.
(Time formats could be used, if only they could be prevented from converting hours over 24 to days, which I have not found out how to do.)
Therefore: does anyone know of any Add-In out there somewhere that could supply the lack of a geographic coordinate data type?
I have a non-contiguous range of cells that contains Values, Formulas, Text or Dates. In some cases a field containing a value could instead contain a formula depending on who is filling out the form and/or what information is known. For example, a cell might contain a value, i.e. Utilities, and I might either know the total ($76,310), or I might need to perform a calculation right in the cell to obtain the total (=54236+9587+12487).
My goal is to be able to copy the information contained in this non-contiguous range to the right some 52 columns (this is a safe temporary storage location) and then be able to copy it back should I need to.
One other problem may be that the source cell might be a merged cell.
Since I cannot determine whether the information is going to be a value or a formula, and I have some text and date formats to copy as well, is there some way to copy this range and keep the data regardless of format?
My Range might look like: (F4, C5, D6:D7, D9, D13:D15, D17:D18, D22:D25, D27:D30)
F4 is a date field, C5 is a merged cell holding text, D6 is the Utilities field described above. The other fields are all formatted as dollars or percentages.
I'm trying to do is develop a formula to: - look at one column in sheet 1 and if a value in that column equals a value in another column in sheet2. then, display an X
I have two excel files. The data is arranged by column. I want to take 3 or more cells (from three different columns) from a particular row and match them up with three cells (in 3 different columns but in the same row). The three cells should be in the same row. However the matching row may appear in a different order in each of the two lists. When Excel finds a match, I want it to return true. How can I go about doing something like this?
I have attached a file explaining what I mean. I am trying to match the three cells in list one with three cells in one of the rows in list 2. However the number of cells in each list is different and there are some cells in list 2 which are not in list 1 and vice versa. There may also be extra columns, which I have not included for the sake of simplicity.
I have data in column A which is the correct data. I also have data in other columns that I would like to compare to column A. If the data is the same, then it moves beside it, if not then it leaves a blank cell.
I have looked for macros and formulas to do this for ages but i cant seem to find one. Here is an example
BEFORE
A B C D 1 1 2 1 2 4 3 2 3 5 4 4 4 7 5 6 5 - - 7 6 - - - 7 - - -
AFTER
A B C D 1 1 - 1 2 - 2 2 3 - 3 - 4 4 4 4 5 5 5 - 6 - - 6 7 7 - 7
See the attached spreadsheet. the range D5:D20 is dependent upon the value entered in D2. the range K4:K64 contains all values I would like entered into D2. For each value entered in D2, the range E21:H21 calculates 4 statistical values. I would like these entered into the 4 columns in Table 2 (L,M,N,O). The first value has already been done. Can I automate this somehow to plug in all the threshold values I have listed in Table 2 (column K)???
I am working on a survey analysis and I'm having trouble comparing two columns.
Ex. 100 surveys Column A being age (scale of 1-5) Column B being willingness to pay (scale 1-5)
I am trying to correlate age with willingness to pay by saying =countif(a1:100 = "1" and (B1:B100 = "1" and "2")) Basically checking to see if the 1st age group has a strong willingness to pay; I would repeat with B1:B100 = "4" and "5".
Is it possible to modify the sub "Continue" below so that the "If s.Name <> wsKeep" line compares multiple variables? The code is for the purpose of setting a workbook to a required state, i.e. hide all worksheets except ones stated in variables. I have further subs that would also benefit from me knowing how to do this.
I am trying to find compare the value in one cell with an array of cells. My problem is when I try to carry the formula down to the next line - my array changes as well. For example:
Before After 01915
[Code].....
So, if Before is Column A and After is Column G, the formula I have right now is =INDEX(A2:A3349,MATCH(G2,A2:A3349,0)). When I try carry the formula down to G3, G4 and so on, the array changes as well.
I'm am trying to take multiple column-groups and sort them by product ID number.
I am trying to put together a sales record for the past 26 months for around 2000 different items and sorting them manually would obviously be a nightmare.
I would like to sort them so that all the drugs with the same NDC (prod ID) have the correct data for each month displayed on the correct row for that NDC. Right now I have each set of data for that month sorted by product ID number but some months we used certain products and others we did not. Is there any sort of Macro that I could use? What I am trying to do I attached two files. The "before" and my desired "after".
[1] compare (row) ranges across two columns with an unequal number of rows (column A [number] to column C [number]) [2] save each result of [1] where there was a match in column B [3] for each row where there was a match (now stored in B), compare the value from the same row in column D (date) with the same row of column F (date), and store the result in column E [4] count and message box the final number of matches from column E
Data Example:
A B C D E F ----- ----- ----- ----- ----- ----- 12345 match 12345 11/1/2011 match 11/1/2011 77777 34345 ... ...
A and C numbers match, so check same row date in D that does match date in E, place "match" in E, then count and display.
Sub other() Dim range1, range2, range3, range4, range5, range6 As range Dim x As Variant, y As Variant, z As Variant, a As Variant
I need of a formula that will look through a list of text strings, spread out through two columns) and compare each one to a user defined entry. When it finds a match against this entry I would then need it to compare another set of text strings on the same row as the entry that it previously matched against.
If it was to find a match on only the original text string it would need to return a true result and if it matched on the original text string and also one of the other text strings on the same rowit would need to return a false result.
I currently have a formula to compare all text strings against the user defined entry and if it is contained in the first 2 columns it returns a true result and if it is contained in both ranges of text strings it returns a false result but i need it to apply the rules per row.
Below is the formula that I am currently using if this helps as a starting point. If you need a subset of the data I can supply this.
Current formula:
=IF(SUM(IF('HBO Frequency Projections.xls'!NOT_HOMEBASED"",IF(ISNUMBER(SEARCH('HBO Frequency Projections.xls'!NOT_HOMEBASED,C5)),1)))>0,"NOT HBO",IF(SUM(IF('HBO Frequency Projections.xls'!HOMEBASED"",IF(ISNUMBER(SEARCH('HBO Frequency Projections.xls'!HOMEBASED,C5)),1)))>0,"HBO","NOT HBO"))
I am working on formatting a spreadsheet report where the values will change in column A. Here is what I would like to do via a Macro. Compare the cells in column A (e.g., compare A2 to A3, compare A3 to A4, and so on). If the values between the two cells in column A are different, insert three blank rows and set the active cell to the next cell following the blank lines. Example:
if cell A5 is different from A6, insert three blank rows below row 5 and new active cell is now A9 and the comparison would start again. I have been trying to code the macro for this but with no success. Here is the macro I have been working on.
Sub Macro1() Const NumRow As Integer = 3 Dim StartCell As Range Dim RowNR, NewCnt As Long Dim RowCount As Long Dim Count As Long Dim intRow As Integer Dim bFmtComplete As Boolean RowCount = Application.WorksheetFunction.CountA _ (Range("A1", Range("A" & Rows.Count).End(xlUp))) bFmtComplete = False RowNR = 2 Range("A1:J1").Select ' Rows("1:1").Select Selection.Copy................
I have a parent spreadsheet with raw data(with errors) and a child spreadsheet without errors. I want to merge the child into parent. (:{). I am thinking of comparing multiple columns from each sheet to ensure maximum accuracy. And when those columns match up we paste the corrected column data from child sheet to parent one. I am using windows 7 and Excel 2010.
So I have this problem in excel with comparing 2 columns.
Basically, I have 2 columns(a &B) that I need to compare with one another and find out the matching data. I am trying to use that matching data to enter in our reports.
Both columns might have duplicate items because I am dealing with premium numbers.
Formula to compare these 2 columns and find the matching data and extract it to a separate column. Keep in mind, i am dealing with almost 20,000 lines of data.
I currently have 2 worksheets worth of data in the same workbook. Each data set has its own date and time stamp in columns A and B respectively with varying data then following in the row. I would like to write a macro that would look in worksheet 2 and find the matching date and time stamped row in worksheet 3. Then select the data from both worksheet 2 and worksheet 3 and paste them in the same row in worksheet 1.
I would like compare data using a certain column to key off of and compare whether data has changed.
I have 5,000 rows and 26 columns. Cell C1 is CALLED PMNUM, cell I1 is called CHANGEDATE
I want to find all of the same PMNUMs (C2-C5000), look at the last change date (I2) (sorted in descending order), compare it to the previous change date (I2) and if anything has been changed in columns D2 through G2 and J2 through Z2 compared to the last time the data was reported, list the changes in in AA2.
