With a set of data in colums by quarter and department names in by row I need to answer build a formula that answers simple questions like "what department had the fewest sick days per quarter?" and which department had the most sick days per quarter?"
View 4 RepliesI have two columns of items that need to be compaired. What I need is the items in column B that do not match any of the items in column A need to be put in Column C. How can I do this? The Data in Column A can not be re arranged. Column B can be sorted.
View 9 Replies View RelatedI have a spreadsheet and I'm comparing data from a room booking system and a spot check on the room to see whether it's actually used. I have a column which I want to show whether the information agrees.
column G - I have a group size recorded that should be in a room.
column H - I have "yes" or "no" as to whether there was anyone in the room when it was checked
column I - I have the size of the actual group in the room.
Is it possible to create a formula that will fill in column J with the answer yes or no depending on whether it agrees.
It doesn't matter whether the groups sizes match, it's just a case of yes, someone was booked into the room and there was someone in when it was checked
OR
No, there was a booking supposed to be in the room but there was no one in it when checked
OR
There was no booking but there was someone in the room when checked.
I have a database with 65536 rows and A-BZ columns
What I am wanting to do is, on another worksheet be able to select identifying data from one or more columns from the database but have it display the entire row.
For example
WCP28 SX
WCP28 TX
WCT36 SX
WCT36 TX
In this example I would like to, on another worksheet, be able to create a drop down where I could select WCP28 and it would show me both the TX and SX row. Or I would like to be able to select WCP28 SX and WCT36 SX and see the rows next to each other. If it were for me I would just use the auto filter and manually compare but this is for somebody even less inclined than I am.
I would like to insert a function in spreadsheet A that looks up data in
spreadsheet B based on _two_ comparison values.
The two comparison values in spreadsheet A would always be in the same row
and the corresponding match in spreadsheet B would also have to be in the
same row.
For instance, if the comparison values in cells A2 and B2 of spreasheet A
match with the comparison values in cells A30:B30 of spreadsheet B, then
show the value that is in column C30 of spreasheet B.
Excel macros. I'm trying to figure out a script that will look at alphanumeric numbers in column A (A2 --> for example) and compare it to the number directly below it and above it. If it matches either, do nothing, but if it is different, delete the entire row that the different number resides on.
An example of the data would be:
A
12A34A5
12A34A5
67F86F3 - when it encounters these rows I need to delete.
23G45G6
23G45G6
The sheet is a couple thousand lines in length, so I can't deal with it manually or even with sorts, due to the other data in the other columns.
I am trying to figure out a way in excel to graph some data I have in a new, sophisticated type of chart. My data is new square foot sales divided by new square feet (for a retail store). It is a dollar amount. I also have this data as a percentage change year over year. If I want to compare two stores with different data -- what would be the best way to present it on a chart. Is there a way to show this on a 3-D plane so that both companies have the data in the same chart up against each other?
View 2 Replies View RelatedI have some data stored in a database and when it is extracted into excel each entry's data is presented in both horizontal and vertical formats. e.g.
Entry1 value1 value2 value3
value4
value5
value6
Entry2 value1 value2 value3
value4
value5
value6
I have a macro that will transpose the vertical data into horizontal format but if there are not the expected number of 'value' items below the entry it will not work correctly. If in the above example Entry2 only had value4 and value5 below it the macro would fail.
What I need is a way to validate that each entry has the correct number of values below it before the macro is run and some way of highlighting or drawing attention to these problems.
I want to be able to do, however. I want to make a spreadsheet that has all the equipment items preloaded into it, along with the quantity that we're supposed to have of said equipment. Then, I would like to import the data from the text file, having it match items in the spreadsheet to their equivalent in the imported text file, and then bring in the quantity scanned. That way, I can use some conditional formatting to show when there is a miss match between "Required" and "On-Hand" quantities.
What I don't know how to do, is get Excel to only import the name of the item, and the quantity scanned, instead of all of the information that the scanner spits out. Also, I don't know how to make it match the name, with the one preloaded into the spreadsheet, so that the correct quantities are matched up. Here's an example of what the scanner spits out: FIELD WIRE,6145-01-155-4256,[Scanned Quantity Goes Here],N/A,N/A,RESPONSE TRAILER,,
The scanner creates records like this the first time you scan an item. So if you scan Field Wire first, it's the first record. However, if you scan it fourth, it's the fourth record. How do I make that match up with a predesigned spreadsheet?
I'm trying to compare three formula results and return the largest number. Ex.:
2217922
The first three numbers are formula results and the last is the following formula:
=IF(O5>P5,O5,IF(P5>Q5,P5,Q5))
Unfortunately, with the list below, you can see the last number (the comparison formula) isn't always the greater of the three:
2217922
2017920
01799
01399
01399
01299
01299
01299
01299
01199
01199
01099
0088
0077
0077
I've also tried other formulas, with no success:
=LARGE(O5:Q5,1)
=MAX(O5:Q5)
=IF(MAX(O5:Q5)=O5,O5,IF(MAX(O5:Q5)=P5,P5,IF(MAX(O5:Q5)=Q5,Q5,"Error")))
What's odd is that if I create the formulas above independent of the specific formula data (O5, P5, and Q5) and just type in numbers, those formulas do exactly what they should. Can I not compare data returned though formula calculations?
I'm interested in comparing the 2 columns of data as attached in the SampleData file, for eg. First customer bought 5 items and customer 2 bought 3 items. I'd like to show the difference which customer 1 bought and customer 2 did not buy. Will need to use that via VBA And also for loop if possible!
SampleData.xlsx
I have 2 Data tables in 2 sheets of the same workbook. I want to compare my column A of table 2 with column A of table 1 and delete any rows of table 2 where (column A of table 2 has a value which is not in the column A of table 1)
In Excel I used the Vlookup function and deleted any rows which had Error in result of the formula. May I know how to execute this in VB
I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
I have two rows of data, each row also have similar data. I need to find the number of matches that the two lines For example :
7,7,K,A,8,7
K,6,7,7,A,9
To reach the conclusion I need to build six such functions:
=1*(COUNTIF($A$65:$F$65,A66)>=COUNTIF($A66:A66,A66))
=1*(COUNTIF($A$65:$F$65,B66)>=COUNTIF($A66:B66,B66))
=1*(COUNTIF($A$65:$F$65,C66)>=COUNTIF($A66:C66,C66))
=1*(COUNTIF($A$65:$F$65,D66)>=COUNTIF($A66:D66,D66))
=1*(COUNTIF($A$65:$F$65,E66)>=COUNTIF($A66:E66,E66))
=1*(COUNTIF($A$65:$F$65,F66)>=COUNTIF($A66:F66,F66))
The result is 4
Is there a single formula that will give the result ?
Another question, is there a formula that I can compare to the principle of one line against the 20 lines ?
Compare 2 columns of data and have a new 3th column tell me the result of the query based on some conditional information. So, here's an example.
Column A = Programmer Assigned (Yes/No)
Column B = Project State (concept, plan, develop, qualify, rollout, etc)
I'd like to compare the values in A to B and have the formula tell me if the programmer should be assigned in that phase or not. So, in english.
If A = No, then look to see if B = "develop" or "qualify", if it does, populate C with "Update required". If it doesn't, populate with "Correct".
I hope I explained it well enough for you all to help. I originally looked into using conditional formatting to just format the cell differently if I needed to perform an action.
to compare the values of data on one sheet (sheet1) with that of another (sheet2) to see if there has been any changes between the sheets over the previous week. If a macro could go through my data on sheet1, compare cell-by-cell the data that's in sheet 2 and highlight in red font any cells that have different values that would be awesome.
The macro would need to leave the "fill color" of the cell as it was.
I have two spreadsheets, The spreadsheet #1 has the information of two years and the Spreadsheet #2 is a montly report. The Spreadsheet # 1 hasta on the column A a number of transaction, and at the column Q the invoice number. The Spreadsheet # 2, only has in the column A the Invoice number. I need to find in the Spreadsheet # 1 the Invoice numbers that match the Invoices that I have in the Spreadsheet # 2 by bringin the "Number of Transaction".
For example:
Spreadsheet 1:
Has on column A3 the Number of Transaction # 0123, and at the column Q3 the Invoice number 555. At the Spreadsheet number # 2 I have the INvoice # 555 located on A10. I need to know what is the transaction # by adding a formula in a new column (G) so I want to have the Transaction # 0123 in the new column added G.
If I do this manually it will take me hours since these report and the Master is so big, and right now I am doin it using Ctrl-F
I have two columns of dates. I want to see if there are duplicate dates and highlight them in the first column. What is the easiest way to do this?
View 9 Replies View RelatedThere are four lines of numbers, in all line 8 numbers.
1 1 2 2 3 1 1 2
1 2 3 1 2 2 2 1
2 1 1 3 1 3 1 2
1 2 2 2 3 1 1 2
I would like to find a function that will examine, if there are similar lines according to my definition, and several.
my definition is 7
For example that I gave, the result in the wanted function needs to be, yes (Line 1 and line 4) and several-One time.
How in VBA can you evaluate a cell in the "general" format and in the "date" format? I'd perfer do this entirely in VBA without use of a helper column in Excel.
View 9 Replies View RelatedI am trying to compare two cells to see if its two values are the same. Some of the cells have a space in between numbers or a symbol like "-"
Ex.
one cell's value is
1213180IN
and the other cell's value is
1213180-IN
the program I have written marks these as different because of the "-" in the second cell....is there a way, maybe with a string function to overlook that "-"?
I need to compare the MMDD in two cells. Example: If the Accrual date is 09/07/10, then I need to know if it falls in the payroll period date of 01/12/12. I just need the MMDD compare to put in my IF statement.
View 1 Replies View RelatedI have two tabs in a workbook. Each has a column containing a unique numerical identifier. What I want to do is create a macro that will loop through the column on tabA, verify it against the column on tabB and when a match occurs delete the row on tabA. The end result should be that only the new items on tabA will remain. Let me know if this is sufficiently clear or still a bit vague.
View 9 Replies View RelatedI am writing some code to do a comparison between two cells X2 or AB2 with an X which checks to:
1. make sure one of the two are filled, or
2. both are not filled.
Below is an extract of the spreadsheet I am working on. I need to check to make sure one of the boxes is checked. If both are empty or both filled with an ("X"), I want a message box to tell me to correct the error and stop the macro from continuing. If only one is marked with an ("X"), I want the macro to continue but don't know how.
RSTUVWXYZAAAB
2IN PROGRESS: COMPLETED:
I have to do monthly comparisons of two excel worksheets, one being a vessel timesheet and the other being an excel download from manpower software that we use. The comparison that is being made are days of the month, whilst the unique identifier between the two sheets (To make it easier to understand I will call them sheet1 and sheet2) is an employee ID. Both sheets, alongside the name and employee ID, list all the days of the month that employees have worked. If they have worked, there is a "1" under that day, if they have not the cell is blank.
What I currently do manually is:
1: Search for the employee ID (and/or name) in sheet1 and compare the days with the employee ID in sheet2.
2: Highlight the differences
3: Move onto the next employee ID
All differences are highlighted in sheet2. Differences highlighted are in the days only, where if any days are missing/extra in either sheet, then the particular cell(s) are highlighted in yellow. If an employee is missing from sheet1 but is in sheet2, then all the days are highlighted (in sheet2). If an employee is missing from sheet2 but is in sheet1, then that particular employee is copied to the bottom of sheet2 (after last row).
As this is horribly complicated to explain, I can attach an example if this is sounding rather inexplicable? Is it possible to attach a xls to a thread?
I am constructing a spreadsheet which is intended to highlight Just in time and late jobs. I have headings job number/Customer/Delivery Date/ Revised Manufacture Date. The first three are already populated by extracting data from our erp system and we enter the revised run date ourselves.
What I am looking for is a formula which shows if the revised run date entry is a day prior to the delivery date then populate the last cell with JIT and colour the cell yellow. Or if the revised run date entry matches or is beyond the delivery date, populate the last cell with LATE and colour the cell red.
Can you use a variable as an operator? For example:
Dim op
op = ">"
If Var1 op Var2 Then MsgBox "It Works!"
I have parameters stored in a database and each parameter includes an operator. When I compare a given value to the parameter, I was hoping to do that dynamically by extracting the operator from the parameter and using that in the comparison.
Otherwise, the only way I can see to do it is create a Function with a Select Case list for all the operators to check the parameter, do the comparison, then return the value to the Function.
I have an issue in comparing 2 dates. It gives a data type mismatch error
Private Sub TextBox12_Click()
Dim siebApp As SiebelWebApplication
Dim siebBusObj As SiebelBusObject
Dim revBC As SiebelBusComp
Dim isRecord As Boolean
Dim sRep As String
Dim sCompany As String
Dim sLocation As String
Dim sStep As String
Dim sProb As String
Dim sDate As String
Dim CurDate As Date
Dim RetDate As Date
Dim datmins As Integer
CurDate = Now()
Const DateCol = 9
RetDate = ThisWorkbook. Sheets("Users"). Cells(DateCol)
r = ThisWorkbook.Sheets("Users").Cells(65536, 1).End(xlUp).Row 'Get next blank row
ThisWorkbook.Sheets("Users").Cells(r, DateCol) = CurDate....................
I have highlighted in bold where i do the date comparison. Here are the date formats [Last Updated] = 2002-11-08 13:19:03.000
[RetDate] = 2/13/2008 15:59:18
I am trying to use the advanced filter with a not equal to operator, where the requirement is that the output not match an exact text string. I have tried <> and <>"???" (where ??? is the text string) and neither filter is working.
View 2 Replies View RelatedI have a column of cells with compound if statements like this:
VB:
=If(And(N10,K10<>""),"X","")
I'm trying to conditionally format this column of cells so that if the cell changes from this formula it'll turn orange. I've tried different variations of the following:
VB:
=N10<>"=IF(AND(N" & ROW() & ",K" & ROW() & "<>""""),""X"","""")"
=mid(N10,1,len(N10))<>"=IF(AND(N" & ROW() & ",K" & ROW() & "<>""""),""X"","""")"
=left(n10,len(n10))<>"=IF(AND(N" & ROW() & ",K" & ROW() & "<>""""),""X"","""")"
Basically, I'm trying to do a string comparison of the formula contained within a cell (N10) to a string that is assembled on-the-fly. If they don't match, then the cell should fill orange. I think I'm most of the way there, but can't quite get it to work. While this is simple to do with VBA, I'm rebuilding a lot of the functionality of this sheet using the functions available through the Excel UI -- users always forget to enable macros and always save copies as xlsx.