I am using Excel 2010 and am looking for a formula that can solve this matix in column below. The yellow is where I would like the answers.
A
B
C
D
E
1
DEP
SPS
SUB
2
123456
[Code] ........
This is what it would look like when the formula were applied
A
B
C
D
E
1
DEP
SPS
SUB
2
123456
[Code] .........
In an Excel 2010 worksheet I use three columns. I need a user to be able to input a word or phrase into a cell or input box, and have Excel highlight that word or phrase every place it occurs only in column C.
Can I do that with conditional formatting? Or do I need to use a function? Or do I need a macro?
I am trying to count the distinct times a value shows up in a column, if another column has a 2 in it.
For example:
Columns
A, B
2,P25
2,P25
3,P5
3,P6
2,P5
The results shoud be: (2) Because I only want to count the P25 one time.
I'm using Excel 2010
I have a file which has in excess of 12,000 rows of data in 5 columns (sample file attached with fake data). The five rows are:
"First Name" "Last Name" "Name" (uses CONCATENATE to combine column A & B) "Email" "Date Attended"
There will be duplications in the list as people will have attended more than once over the years.
What I want to do is search through the email addresses (Column D with D1 being the header) and where there is a duplicate email address copy the cell to the right of the duplicate (F#) into the next available cell to the right of the first occurance and then delete the row with the duplicated email address.
I am on Windows 7 and Excel 2010
I have an excel 2010 file that we export (save) to HTML so we can upload to our website.
1) Can I add a search box to the top of the page?
2) Can I freeze the row headings?
I have various spreadsheets that get into infinite loops sometimes when I search for things. Hitting break breaks it, and the search window (find all) shows the same exact entry hundreds of times.
This is Excel 2010.
I really like the slicers in Excel when working with data scenarios, but I don't like having to scroll up and down through the list of options.
Is there a way to add a "Search" box at the top like the traditional drop-down auto-filter? Or, does any loophole to making one?
I have my data arranged in a table on sheet1 as shown below.
Columns B to H contain the hours each person normally works on the days of the week Sun to Sat. In columns I to O are the hours that each person has booked as holiday for that week.
On sheet2 I would like to be able to enter a holiday day in C1. In A3 to A15 I would then have a list of all the people who will be working on that day and in C3 to C15 have a list of the people who are on holiday so the output on sheet 2 would look like this.
Basically I'm after the formulas for A3 and C3 that can be copied down the column depending on how big my table gets. I am using Excel 2010.
I have sheet 1 that in cells V5:V20 is a data validation drop down box. In cells W5:W20 I have another data validation drop down box. On sheet 2 I have a table that in column A matches the data in the drop down box's in column V on sheet 1. Row 1 on sheet 2 data matches the data in the drop down boxes in sheet 1 cells W5:W20. What I'd like to do is on sheet 1 Cells Y5:Y20 have a VBA code to lookup the data in columns V and W from sheet 2 and return the value.
Windows 7
Excel 2010
I want to use the find function on a range and search for a "," character. If I get a "," I want to return a True otherwise false.
I tried just recording a macro using the find function but no code shows up.
how to use the find feature in a macro, can this be done?
excel 2010
I am after tips on creating a macro in Excel 2010 where it will search a list of folder paths in a column on a spreadsheet and insert a jpeg within the sheet from that folder if it exists. If more than one jpeg exists i would like it to insert the first jpeg only. i have found tips where it will insert images if you know the filename, however I don't have this luxury as file naming routines vary from folder to folder the only constant is that the file is a .jpg format.
View 1 Replies View RelatedI have a range of cells each containing different values. Each of these different cells has a related date contained in the row underneath.
I want to be able to search the range of cells and find it in the contents of a cell and return the corresponding date from the row below.
Excel 2010
A
B
C
D
E
F
G
[Code]...
Row A4:O4 is the range of cells to be tested. Row A5:O5 are the corresponding dates. Cell B1 is the value I want to search for and B2 is where the date I would like to appear.
In the example shown I'd like 01-Sep to appear in B2.
I'm using Excel2010 and Windows 7.
Search upwards in a column find the first cell with data and give the date in column A on same row as data?
View 9 Replies View RelatedI made a quick little spreadsheet that explains what I am trying to accomplish. Basically I need to search a column for a known number, when it is found, I need to look at another column on that row, and if the cell is a specific item , count it.
View 9 Replies View RelatedI have been using a macro to search and highlight customer addresses for me, however I have changed my worksheet and now have the addresses in two columns instead of one (D for the numbers, E for street names) for sorting purposes.
What I would like to do is either; find a way to search using data from 2 cells to get a result, or have multiple results highlight and popup.
Using: Excel 2010
Code:
Sub SEARCH_FUNCTION()
Set FoundCell = Sheets("Route").Cells.find(What:=Sheets("Intro").Range("G15"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not FoundCell Is Nothing Then
With FoundCell
[Code] ...........
I have two sheets with data. I wont excel to look for a value in colum A in sheet 2 the value to look for is defined in colum a in shet 1. If value is found it should insert in sheet one the value that is in the cell next to the found value. The data is not sorted. I have attempted and failed with using different functions.
View 1 Replies View RelatedAs per the attached sheet Count If is not operating.
Effective Reading Capability
Effective Reading Capability
Effecting Writing Capability
Application of Basic Mathematics 0.4
Effective Listening Skills 0.3
Effective Speaking Capability 0.3
Attitude to Cleanliness 0.3
Idea Generation & Creative thinking 0.1
Decision Making Skills ( Upto the mandate)0.2
[code].....
here If I say Countif(B4:B20, >=0.5) But it shows Zero.
My difficulty: I have an Excel 2007 DB xlsx Workbook file (mydb.xlsx) made up of several (8) Worksheets. I can't modify the content of this DB. For this reason I 've created another Workbook (MyGUI.xlsm) implementing a user interface for db querying.
The userform let the user making searches using the find method (criteria: "Text contains...") In the DB file there is one particular Sheet with thousands of entries. This makes the searchs too long (30 seconds). During this period the entire form is blocked.
My question: when the user realizes the search is too long I'd like to stop the current search
introducing for example a new button "stop current Search" to click it in these situations.
I want to count the number of cells in column C with one forumla that answers the question, "How many of the cells in column C contain the words, APM, BPPM, CAPM, and NMS while exluding any results that have "OUTLOOK" in Column K?
View 2 Replies View RelatedThe sheet shown, Complaints, lists on each row information on a single complaint. This information extends from Col A to Col Y (though not that way here, the data actually begins in row 3). I want to highlight a Customer cell, say H227 for Customer A, then activate a macro that (1) recognizes the active customer (here A), searches the list, identifies each row where A is the customer and then writes each row into sheet "Event" beginning in row 4. I should then see the same A to Y cells of information, beginning in row 4, filled in sheet Event for the six customer A complaints. Note, the actual data in sheet Complaints involves 300 rows of data, and over 100 customers can be on the list, some up to 15 times. I know that I could do this by filtering the data, copying and pasting, but a simple macro would be quicker for the group of individuals involved. Note, I then use this information to populate a chart.
Excel 2010ABCDEFGHIJKLMNOPQRSTUVWXY226#INITIATION MonthINITIATION DATECRM Transaction IDSales OrderSalesSHIP PlantCUSTOMERCITYST/PROVPRODUCTProductLbs InvolvedPROBLEM Complaint
[Code]....
I have a spreadsheet with 6 months of data in one row. the dates are from A3 (Jan 1) to FY3 (Jun 30). The data is in range A4:FY4. The data is a "$" (without quotes). The $'s are entered randomly during the 6 month period. I need a formula to determine the days between the last occurrence and the next to last occurrence of the $. Example spreadsheet shows the $'s on Mar 28 and May 5 as the last two. The spreadsheet is attached. I am using Microsoft Office Excel 2010.
View 6 Replies View RelatedI have attached an excel 2010 spreadsheet to show the issue.
I was shown a formula to rank a series of numbers =RANK(A2,$A$2:$A$19775,0).
However, the ranking figure that get's generated, appears to be counting the cells or something like that.
Ranking Example.xlsx
see the attached 'Example' excel sheet.
I'm not the best at explaining, but I think that the excel sheet is pretty straight forward.
In Sheet2, cells C3 and D3 are manually entered by the user. I am looking for a formula for B3 that will take the information entered into C3 and D3, find the row in Sheet1 in which column C=Sheet2!C3 and column D=Sheet2!D3, and give the unique corresponding result from Sheet 1 for that row in column B .
So, for example,
if C3=A and D3=102, then the result would be 444
if C3=A and D3=104, then the result would be 111
if C3=B and D3=102, then the result would be 111
The combination of C3 and D3 is always unique and will never have more than one possible result in column B of Sheet1.
My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.
View 14 Replies View RelatedI have data arranged in a worksheet (see attachment) that has hours of work broken down by day. What I need is a formula that will find the number of times a record occurred in Column F that is greater than or equal to 12 hours each day. So for March 1st there would be 9 times. I can do that now with no problem using "=COUNTIF(F4:F14,">=12")" However, the real thing that I need is how many days of each month were there only 1 count (of 12 hours or more). So it needs to look at the range of data that goes from 3/1/13 to 3/31/13 and find the total number of days that had 1 count (of 12 hours or more) each day and return the number of days it found.
View 6 Replies View RelatedI have a simple pivot table that is types by state. I want to know how many types are in each state. In the attached image the answer for Alaska is 5, for Arizona 5, for Arkansas 1, etc. How do I get Excel to tell me that for each state?
I'm using Excel 2010. I have PowerPivot installed but really don't know how to use it yet.
The formula I found is close but I need it to count, not sum.
=SUMIFS($C$36:$C$51,$B$36:$B$51,$B36)
Example data: [972 1] [972 2] [972 3] [214 1] [214 1] [765 1] [765 2]
I want the formula to count the different numbers in the second column based on the first column. The results should show, 972 equals 3, 214 equals 1, 765 equals 2.
I am trying to separate this out but it keeps putting this paragraph.
I want to count a range of cells and if the value of those cells is 5 and over, i want it to give a 1 and if its under 5, i want it to give a 0. I've attached a file of what i'm after, row 13 is where the formula would go, i have just put in the values.
View 1 Replies View RelatedI have data set up like the example but then for 1797 lines.
D39BKYes
D39BKYes
D39BKYes
D39BKYes
GHFLBNo
R80FANo
R80FANo
[code].....
What I need to get in a pivot table is: two columns or rows (Yes / No) and the unique count of the code.In this case is should show Yes: 2 and No: 3
I'm doing a study of when people are most productful in their day. I just need to count how many units a person made within say 4 hours of starting work.
My data is similar to the excel I've attached
Count completes.xlsx
I'm using Excel 2010 too.