I have a long lists of values (product numbers) from our inventory records. Now, we've been given a new list of product numbers, some are the same and some are not.
I want to isolate (spit out) the values from column A, than don't match column B. Column B is the list we SHOULD be using, column A is what we're currently using. See example below.
Old Products New Products
34544 34544
34545 34546
34546 34547
34547 34548
34548 34550
34549 34551
i have weights entered in A1 , B1 , C1 and I manually enter almost same weight in D1, E1, F1 . Now I need the result as " OK " in G1 if the weights A1,B1,C1 match with D1,E1,F1 if it doesn't match then result should give "Please check again".
Having a strange issue running an autofilter on a set of data - trying to isolate a particular date...
I have four variables :
wsDataSheet (Worksheet) specifies the sheet containing the datargAllRange (Range) specifies the range of data to be filteredinValueDateColumn (Integer) specifies the column in which the dates are held (entire column is Date formatted)dtDate (Date) is a date value specified by the user on the main sheetFor test purposes, I've filled the column with the same date (19/07/2011) and specified the same date on the main sheet (19/07/2011) - and checked that all variables are holding their expected values in the debugger.
But for some reason, when I run the code...
Code: With wsDataSheet .AutoFilterMode = False .Range(rgAllRange.Address).AutoFilter Field:=inValueDateColumn, Criteria1:=dtDate End With
The autofilter finds no matches??
Very puzzled as I've gone to great lengths to ensure the date formats are consistent.
I've been using a Index and Match Functions to locate values,
What I'm trying to do is as follows:
Column A contains x values and Column B contains another value
A length is entered at the top of the worksheet, when the x value is greater than half the length the values will be mirrored about the centre point.
To do this i'm doing x-(length/2) to give the corresponding value of x on the other side of the length
I can do the If functions etc to sort all this out, but the match function isnt working, see attached, it isn't finding values which are in the list, when i use 0 for exact match, i've tried using 1 but it still isn't finding the correct values.
Now I'm trying to improve the functionality of the file attached above.I've attached the file again with another drop-down menu. What I'm trying to do is to have a sum of all the months between the two months indicated in the drop down menu.For example by picking two months from the drop down menus, I want to know the sum of the values between Jan-Mar (Jan, Feb and Mar), or for example Jul-Oct (Jul,Aug,Sep and Oct)...
I've tried to combine SUMPRODUCT with INDEX and MATCH but it doesn't work. I want to build upon this formula:
[Code]....
I forgot to mention that I have multiple strings (column E) which are NOT unique. Basically "VLOOKUP way" won't work, rather it should be "SUMIF way".
In the attached file I've added one additional row (r19), which is the same as r18, just to try how the summing will work.
I have a column of cells (Column I2:I1063) with zip codes in it and I want to keep the row if the zip code matches one on the list in a column B2:B100 on a separate sheet (Sheet1).
I've been asked to assist with modifications of an excel spreadsheet. One of the features is a column that has a formula of what the total order should be. For example, 1 base order of 25.00, plus 1 bonus order of 18.00, plus 2 bonus orders of 15.00 would be $73.00 due. Then data entry persons enter the actual amount collected. Is there a way to highlight either the cell or the result if the amount collected does not equal the amount due? I know I can accomplish this with another column, but they want to keep the columns to a minimum.
This is for a non-profit agency and the orders are now at 1000 that must be entered within a day or two period, so they want to keep the fields to a minimum so as to make the entry easier for those doing the input. And they would like the data entry person to see an immediate flag if what he or she enters as collected does not equal what is due.
I am using subtotals for a large spreadsheet showing various sales by person and area. I have sorted the data by area then by person and insterted subtotals. All columns are fine, except two. On two columns, the grand total displayed does not match the sum of the areas.
If I remove the subtotals and sum the columns, they are all correct. It is only when using the subtotal function that the error occurs, and only ever on these two particular columns.
In a column in which there are repeating numbers is there a formula I can enter into conditional formatting that will highlight a cell when the number doesn't match the value from the value above it?
So in this example the values with an * would be highlighted.
Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?
However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?
Code below. I need it to NOT run if the sheet week2 doesn't exist. Currently it gives a runtime error '9' out of range. This is due tot he sheet not being present because sometimes it is not generated.
Code:
Sub RemoveColWeek2sheet() Dim ColNo As Integer Dim rng As Range Set rng = ThisWorkbook.Sheets("Week2").UsedRange
I need some way that can identify when the item in the description column doesnt match the first item of the same number- for example, here the 1-blue and 3-orange would be flagged because they should match the 1-yellow and 3-green.
I need to do this on a much larger scale (approximately 20,000 data points), so I wanted to create a formula or macro that could do this for me.. I thought making a reference page with would work but I keep getting an error.. I haven't done VBA in a while, so I may have syntax errors.
If Range("A2:A9").Sheets("Sheet1") = Range("A2:A6").Sheets("Ref") And Range("B2:B9").Sheets("Sheet1") = Range("B2:B6").Sheets("Ref") Then Range("C2:C9").Sheets("Sheet1") = "x" End If
I have an array formula: {=SUM(IF(MATCH(Group,Group,0)=(ROW(Group)-ROW($C2)+1),1,0))}
where Group is: OFFSET(Groups!$C$2,0,0,Groups!$D$1)
and the array formula is entered in Groups!E1 and returns an #N/A error.
If I select the formula in the Formula bar and press F9 it evaluates to 48 which is the result I am looking for.
If it calculates OK using F9, why can't excel resolve it in the cell??
When I enter the match part as an array {=MATCH(Group,Group,0)} in an appropriately sized range, it works fine and returns the index of the first occurrence of the elements of the Groups array as it should.
The Groups array in column C is an array formula entered over 388 rows and contains: {=IF($A2:$A389="","",INDEX(XCorel!$IC$2:$IC$232,$A2:$A389))} where XCorel is another sheet in the same workbook.
Can anyone suggest why this might be? If I replace the array formula in column C (Group) with its values the problem goes away, but I want it to be dynamic.
Cells C9, C10 & C11 contain variable values so the user can choose from Groups A, B, C, etc using a data validation list. For any one variable this works fine. What I'm struggling with is when the user selects "All" from the list. "All" doesn't appear in the data so the formula as written doesn't recognise it and fails. What I'd like it to do is recognise "All" as meaning all entries.
I need the data "pulled down" into the subtotal row, so to get this after I subtotal, I'm sorting by C, and I've got some VBA deleting all rows where COLs A & B are blank (this is the longest part & the part I want changed the most - this gets rid of the non-subtotaled rows), extended replacing "Total" with "" in COL C and then inserting a lookup in A & B to get the data back next to the subtotals.
This takes really long and I'm sure there's a faster way to do this that I haven't thought of. All in all, I'm looking for something that will ONLY keep the subtotal rows, and will fill down the data to them while removing any non-subtotal rows.
what do i need to do when the fill tab doesn't go on to the next set of data. It is just repeating the data in the same column from the first calculation--it doesn't go on to the next set..what do I need to change when inserting my calculations...
Finding last empty cell on a column and inserting, copy paste the last non empty column. Now, I do have same problem again but this time I have to copy the last 3 non empty columns and paste it on 3 inserted column. Here is the piece of successful code with my previous problem.
Currently in Column B for all the rows I have a basic math formula which adds the last three entries for that row and divides them by 3. Currently, I constantly am needing to readjust the formula to include the correct columns for each row every time a new column entry is added. I am looking to set it up so it automatically sets the formula to read the last 3 entries without having to manually adjust the formula to D+E+F= to E+F+G= ect... when a new entry is added.
I am a relatively light Excel user. I mainly use it when working with the .dbf files that make-up GIS shapefiles.
My problem at hand: How can I compare/isolate the rows from spreadsheet #1 to #2 to determine which rows are unique to spreadsheet #1?
What I'm doing: I have a spreadsheet of addresses which I joined to our parcels shapefile to select those parcels. I am trying to determine the success rate of my join operation by isolating the rows which weren't joined. This would be determined by highlighting which rows from spreadsheet #1 aren't in #2.
How can I set up my sheets so that the background and grid are isolated, in other words only the background, the grid, and my data shows on the sheet, the rest will be blacked out. I need to email only the certain area of the worksheets.
I have a worksheet that tracks errors (10 different error types are tracked) that may occur on anywhere from 20 to 200 different jobs. What I need to be able to do is find all the jobs for any given day where a specific error has occurred two or more times for that day. Often a specific error will occured many times on the same job on the same day. What I need to end up with is only one row for each job that shows the Date/Time (m/d/yyy h:nn), JobNumber (number), ErrorNumber (text field) by either hiding or deleting those rows that do not meet the criteria.
(scroll down for example) I have 142 countries and 6 numbers beside each country starting from Column A, Cell 2 (A2) . I want to seperate the numbers into columns B,C,D,E,F,G
The formula I thought to usein B2 was =Right(A2,11) to give me the 3 next to Afghanistan in the ELICOS Column (B2) , but this gives me all the numbers
Question is How do I seperate each number so each one sits in the right column??
ELICOS Schools VET Higher Education Postgraduate Research Non Award AusAID/Defence
513 BLUE RIDGE Kansas City, MO (39.104810141000485, -94.47983043799968)
I need to isolate the coordinates in separate cells. IE "39.10481014000485" in cell J2 & "-94.47983043999968" in cell K2. The problem is that some of the cells don't have 14 numbers behind the decimal, and my formula will sometimes grab either the comma ( "," ) or the end parenthesis ( ")" ). Is there a formula I can use to isolate the two coordinate plots?
The good thing is every cell is formatted exactly like this. I had been using a formula that looks like this.
=MID(F2,FIND("(",F2)+1,18)
I think it's close. I just cant figure out how to tell the formula to stop at the first comma, and to stop at the end parenthesis for the second set of coordinates.
