I'm currently having a hard time creating a formula to verify if the contents of 2 cells match.
Example from Spreadsheet -
Column C: MATCH / NOT MATCH
Column D, Row 4: MSG
Column E, Row 4: SMSG
When I attempt to create a formula for Column C, it registers the "MSG" within "SMSG" and lists the result as "MATCH".
Formula(s) to do as explained in the attached example.
Example_formula.xlsx
I am new to Excel macros and programming, and am running into a problem that I can't figure out how to solve. I have read through the forum and tried various examples, but I am still not getting the result I am looking for.
I have a spreadsheet that has a variable number of cells in-between blank rows (the data can be 1 to 6 rows). I need to sum up the rows of two columns and see if they equal 0 or if there is a difference. If there is a difference, I want to make the cell red. The exact problem I am having is I can't make it check the # of rows and run the formula based on that number of rows. I am attaching an example of my raw data followed by an example of what I am looking for as the end result.
I have a sheet with some survey data. the data covers about 4 months. There are about 2200 rows and 8 columns.
The "code" could be in there more than once as the person took the survey multipule times, but all other data is different. How can i pull out the whole row when the code is there more than once.
I want to know all the "codes" with multipule entries that took the survey more than once then trend there scores.
CentercodeRecommendReasonEnvironmentTraining ManagerOverall LHQTR27909415Learning effect4444LHQTR28844652
Center environment2222LHQTR45614375Service5555LHQTR96944292Service2222LHQTR144769543
Center environment4433LHQTR144769543Learning effect3433LHQTR155258791Service3213LHQTR168772563
Center environment2232LHQTR168772563Center environment3332LHQTR168772565
Learning effect4414LHQTR173991905Learning effect4445LHQTR192966385Service5555LHQTR193282534
Qualified teachers3344
in writing a macro that does the following.
Right there data in cell A1 lets say LTP BY001 and then the next data in column A is LTP BY002 in cell A9. What I need to do is copy LTP BY001 from cell a2-A8 and then LTP BY002 From A10 until LTP BY003 appears lets say A15. Therefore need LTP BY002 copying from A11-A14. I need that to go right the way down the workbook at everytime their is a number change.
Having a bit of trouble trying to get excel to pick up text in one sheet (sheet 2) and populate cells in another (sheet 1) if the row (row 1) labels and columns (column a) in both sheets match. hope that makes sense? I've tried googling this to no avail, i've also tried index-match however i keep getting errors.
View 3 Replies View RelatedI have a table with 4 columns where in column 3 I have part numbers and in column 4 I have the number sold. In column 3, the part sold can be listed several times with a different number sold in the column 3. What I need is code to go through the part numbers and return a total number sold for that part in a new column. I have sorted the table by part number, then by Column 1 which is for the date. The new total should go in the last column with the oldest date for that part.
View 2 Replies View RelatedI've got 3 pairs of columns and I need to sort through them and align the cells in columns E&F with those in A&B and C&D. The cells I need to match up are the times (columns A, C and E)
Example - convert this:
A...............................B..........C...............................D.........E...............................F......
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:56.....6.14.....30/07/2014 14:22:56.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
Into this:
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.................................................30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11........................................................................................
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:56.....6.13
............................................30/07/2014 14:22:56.....6.14............................................
............................................30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
I don't know VBA so hopefully there's a way of doing this with a basic Excel function.
table1.jpg
I am looking for a formula that will satisfy the following:
1) find all the values in column "A" that match
2) In column "G", sum up all the values in "F" that go with the matching values in column "A"
3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16
i have stores numbers in column a and the sets and cartons in column b but there is gaps between the data i want in column c a list of stores with the set and car displayed next to it, some information in column b is on 2 rows for 1 store please see attached, how i would like to see it on a row is b345 car011119 etc
View 1 Replies View Relatedsee attachment below for easier comprehension of what I need to be done. Note that I have around 20 subjects (2 in example) and about 15000 values per subject (5 per subject shown) Basically, I want matching rows to be aligned (see grey) and non-matching rows to be deleted, throughout the 20 or so subjects.
Exemple.xlsx
I am trying to write a formula where the column header of the row in which a value other than 0 exists, will display for each instance (row) where a value exists in an array spanning 3 columns. So the result cell could be any of the three column headers, or a combination thereof.
I started the formula in P2 of the GL Detail-2012 tab. File attached.
Here is what I started: =INDEX($M$1:$O$1,SUMPRODUCT(COUNTIF($M$2:$O$67756,))). Not working.
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A
B
C
1
Cat 1
January 1, 2014
John
[Code] ..........
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
The best try I had for the formula in C7 was
{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
Creating a formula to match two columns (D & H) and if there's a match, I wnat it to produce the value that's in column E, in column J (where the fomula will go).
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
I currently have a spreadsheet that refers to a different spreadsheet, with different expenses each month, with the months going across from A,B,C,D etc.
Because the formulae on the expenses sheet refers to cells such as O13 O15 O27 etc, at the start of the next month the 'O' has to be changed to a 'P' to give P13 P15 P27
This doesnt sound much but there are over 10 different spreadsheets that are all currently changed manually. Is it possible to have a function referring to a cell containing 'O' which when changed to 'P' changes all the formulae to refer to the cell next to the original.
EG Instead of O13 it would be [variable]13
Essentially this will mean that a master sheet will be able to change all the excel spreadsheets in one go.
I have 2 lists. One list consists of the names of agencies as they would appear in our company's data base. The other list is the official business names. I would like match our company list with the official business name list by matching at least 2 words from the name with a name that shows up in column C (Official agency name).
If possible, I would like to ignore preposition and article words since they are used often in agency names.
I am looking for a formula to do this
I have a problem with excel matching fields. I have a list of cities in column A, a list of state in column B, and then on worksheet 2 i have another list of cities in column A and states in column B. I then have dates in column C on the second worksheet.
The problem is that the list on worksheet 2 do not repeat the names of cities and states but on worksheet one there can be repeats.
I wanted to know if there was a command so that if Column A and Column B on worksheet 1 matched Column A and Column B on worksheet 2 then excel will copy the date in column C on worksheet 2 and paste it onto column C on worksheet 1.
I need a formula to automatically transfer data in a column into another column, omitting cells in the 1st column that do not have data in them.
So, for example, transfer the data in column "A" below to column "C" below omitting any blanks when the formula automatically copies data over:
Example Spreadsheet.xlsx
I'm dealing with forecasting invoices. The main column 'BALANCE' has a formula referencing the previous cell and then adding or subtracting invoices (ENTRY). The problem I have is that I'll put values in ENTRY that I expect to come in at some point but as they arrive I need to change the order of the cells. So I'm constantly changing the order and then it changes the formula in BALANCE. I tried absolute cells (and even found a work around way to flow absolute cells by flowing then viewing formulas then using the replace function but that didn't work.
So my formula in BALANCE is:
=$I$173+$E$174-$G$174
but if I move G174 to another cell I'd like to keep the BALANCE formula to stay G174 instead of following the move.
I browsed through the forum looking to find a way to solve my problem, but could not find it. Maybe someone here can help me out.
I have lots of data, and it would be impractical to do it manually. Here is an example: ...
In the attached example I paste in a report from another system into Sheet2 (rough data layout in example).
In column B in Sheet1 I have tried using Match and Index formulas to try and return only the Local Shipment figures from on Sheet2 for each staff member.
The issue i'm having is that the data in Sheet2 can change based on how long the report is run. There is also no way I can change the way the raw data is set out in Sheet2 as it's an external system.
Basically i'm looking for a formula that isn't locked down to certain cells and can work with data moving up and down columns at different stages. The key data never changes from column A and F in sheet2. But what does change is whether each person has one or both of Sales & Local Shipment data in the reporting. This obviously changes based on what is completed each day.
So I need a formula that matches the persons name in column A in Sheet1 with Column A in Sheet2, then looks for the first mention of 'Local Shipment' below it and returns the cloumn F value which is in the same row as 'Local Shipment'.
I've been struggling with dependencies, so I have two sheets.. the first sheet contains a simple table:
EmployeeUsernameStatusStatus Note
Alex
Josh
Sarah
David
This is the second table:
Employee Username ActivePassword Change Days Since Change
Alex al2 Y Y 20
Alex alex Y Y 11
Alex aa2 N N 0
Josh josh2 Y Y 10
Sarah ssarah N N 0
Sarah sarah Y Y 1
David David Y Y 4
The problem is in the first table as I don't know how to populate the "Status Note" column which depends on these two columns The column Username which is a dependent list from table 2 that contains the username associated with each employee selection The column Status which is a dropdown list that contains (Active, Password Change, and Days Since Change)
For example if the following is selected for user Alex: al2 and Active how can I make Excel display Y in the status note column
EmployeeEmployee UsernameStatusStatus Note
Alex al2 Active Y
Josh
Sarah
David
I've been trying to find this on the web but haven't a clue now, I have a database query that I am pulling from an access database and now I am trying to match the last four characters of a referral string to a current location, let me show you what I mean. Example,
A(location string) B(current location) C(last location)
car/kitchen/house kitchen NO
kitchen/garage/car car YES
kitchen/car/garage car NO
I want to write a function in column C that matches the last four characters of the location string (column A) with the current location (column B) , and if they match have a YES in column C, or a conversely a NO.
Table 1
Code:
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June 232350$0.19 http://www.jyxsaw.com/blackrm/index.php?t202id=2676&t202kw=AMWG16=01_0210-3
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June
[Code]....
I've got two tables of information here, if you scroll to the far right of table 1 and look at the left of table two, you'll see a common code like:
AMWG16=01_0211-20
AMWG16=01_0212-16
and these entries will appear multiple times as well
AMWG16=01_0212-16
appears twice and the data is different and will need to be added up
How can I:
1. Add up the data on each table with the common string such as "AMWG16=01_0212-16"
2. Take all the data that has been added up and combine the statistics, for example,
when its all done it should show
AMWG16=01_0212-16
in the first column, and then all the statistics from the two tables in the columns to the right.
I'm guesssing...
Since some entries appear multiple times, you will need to sum up all the unique entries for that value. Say:
AMWG16=01_0211-20
Shows up three times, you'll need to sum up all the values and this needs to be done on both tables
THEN
You can combine the data
I'm familiar w/ vlookup, index, match functions, but I'm not quite sure how to put this all together
I m trying to match the values based on the Coulumn B
[IMG][URL]....
;base64,iVBORw0KGgoAAAANSUhEUgAABVYAAALYCAIAAAAYRj5jAAAgAElEQVR4nMy9Z1RU+Z73Oy/uWs9aM
/eZmTtzn5nT3doGoNLOu3KmCEXOQUURUcCAomIgmXOgDYCK5JyhcoQiQ5HNWcE2dNt9uo8dTve
ZM3PCfbF3RYJo95m5rs+qVb0bpMoO8P3s7+/3/zvrwYOL5cCB4dnk5bmTm2tnKCeHYDAnZzA7m2AgK2tg
/347/fv29e/b1793bx
[Code]...
I have an application that on the "Main" sheet, is to extract two numbers then search for them on my "Listpoint" sheet and finally return the text to the right of the search data (e.g. K3)
Working left to right - the user pastes upto 12 lines of code into C3-C22. Formula in E3 extracts object Nos. Formula in K3 substitutes "first" number if it is a zero (with number from A3).
Left to do - Uses data (H3) to search Listpoint sheet colums C and B for a match. then returns text from Column S.
Note Listpoint has 1000 rows to search.
Main
ABCDEFGHIJKL1 2Osn Number Prog Point Text Extracted
ObJNos Zero replaced Name of Object 1 38 10 IF POINT 0|199 ON OR POINT 8|191
ON THEN RETURN FALSE 0 1998 191 8 1998 191
Example found Text 4 20 IF POINT 0|106 OFF THEN RETURN FALSE "HOLD OFF"
[Code] ............
I am trying to make an attendance sheet where if you open the file up on a specific date, the matching date from the columns is selected only and the sum of the column is put beside the cell that says available. I have attached the file I am trying to make.
View 3 Replies View RelatedI have a spreadsheet where I have two different sheets; a summary sheet, and a more detailed sheet that lists out items on invoices.
The summary sheet shows invoice number, amount invoiced, and total paid against that invoice.
The detailed sheet goes into more details, and shows whats on the invoice, and any reasons for them not being paid.
I want a formula on the summary sheet that picks up the invoice number from the details sheet, and then brings through the figures. The details sheet may have more than one line for an invoice number, so the formula needs to be able to add up too.
I've tried a vlookup, but it doesnt seem to work, and I dont think it will do what I want.
