Matching 2 Column Entries Returning Third?
May 7, 2012
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] ............
View 9 Replies
ADVERTISEMENT
May 12, 2014
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'.
View 3 Replies
View Related
Apr 26, 2014
I have 2 columns:
VAL0085597220EXP0000207731
VAL0122473124EXP0000207864
VAL0169412496EXP0000207879
VAL0173317027EXP0000208330
VAL0172476750EXP0000208703
VAL0173317028EXP0000209148
VAL0047743340EXP0000209666
VAL0176241996EXP0000209668
VAL0169594337EXP0014327602
VAL0047743872EXP0014327702
VAL0158185837EXP0014329002
I am trying to match where these are located in another column
View 1 Replies
View Related
Apr 4, 2014
I am using Excel 2003. I have attached a data file here. getting the values in Q3, R3 and S3.
Scenario:
Q1 has the number = 1. So I want the cell Q3 to return 2/11/2013 as that is the cell corresponding to the Item1 (value specified in P3) with the value 1(value specified in Q1) in the cell. Basically, I need the date corresponding to cell which has the value of Q1 for the value of P3.
Similarly, R2 must have the value 2/12/2013 and S3 must have the value 2/14/2013 returned.
View 10 Replies
View Related
Jan 30, 2009
I've tried to amend the formula's posted above but to no avail so am hoping someone can have a look at the attached.
I would like to be able to use Vlookup or similar to complete a table (starting in cell A19) based on the surname added in B16.
View 2 Replies
View Related
May 1, 2013
I am doing a list which has the same products returning several times, but with different values. Want to filter/make a new list, with only one of each product and the summed amount of that specific product. Summing the specific amount is not that big of an issue, but the creation of the list is, least in a smart way i have tried this:
[Code]......
This being the last possible entry for the summed list.
My problem is that the formulas is getting too big for my computer :S, since this formula is copied more or less 10 times.
Here is an example of what i want: Product list.xlsx
View 5 Replies
View Related
May 7, 2014
What I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.
Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).
the function I'm working with right now is
=IF(ISERROR(FIND($C:$C; A1))=TRUE;"0";A1)
View 13 Replies
View Related
Feb 18, 2010
=ISNUMBER(MATCH(X1,A:A,0)) where X1 is cell you are checking for a match to check and see if there are duplicates in 2 rows. Is there anyway to check to see if a cell contains a string of numbers. Example: Cell A1 has 0000402502LK and Cell A2 has 402502. Is there anyway to get this to show up as true since the 402502 in contained in the string in A1?
View 2 Replies
View Related
Apr 20, 2006
I have run across a problem that I am unable to solve with a combination of index, match and countif funtions. What I am trying to do is have the formula return disctinct symbols. I have a list of currencies on one sheet: audusd, eurusd, usdcad etc, more than one from each. Id like to return one from each onto another sheet...
View 9 Replies
View Related
Mar 18, 2014
I run a bowling leagues which as 7 divisions and 10 teams per division, some of the clubs have up to 4 teams entered. I keep a spreadsheet for each division which keeps records of each teams performances as well as individual. All the clubs have to register their players for which I keep a data base for each club, the clubs having 4 teams register quite a number of players. My problem is I have to manually check the data base against players being entered on score cards and then on to the spreadsheet. Down columns B & C on the spreadsheet I have the Forename & Surname, when I enter a name in the cells I would like a formula to check against the clubs data base and return the name or false
Players Reg.xls
2014 stats - Copy.xls
View 3 Replies
View Related
Mar 29, 2012
I have in my 'Daily' sheet I9 which needs a formula to look at the date in C9 and the scan range 'Weeks' F6:AN41 and find the cell with the matching date and then whatever text is in Row 2 of the column the date was in will be returned.
View 2 Replies
View Related
Feb 25, 2010
I have an Excel spreadsheet (XP - 2007) listing Job Nos. in the first column, with several columns of Station assignments and dates.
Both planned dates and actual dates are included, adjacent to each other. The dates are not necessarily in a straight ascending or descending order. Separate arrays exist for: Plan Nametags, Actual Nametags, Plan Dates, Actual Dates.
Example:
Job No.Sta1 PlanSta1 ActSta2 PlanSta2 ActSta3 PlanSta3 Act1A10001-Feb-101-Feb-106-Feb-106-Feb-101-Mar-101A100116-Feb-1016-Feb-1019-Feb-1022-Feb-105-Mar-101A100225-Feb-1025-Feb-102-Mar-104-Mar-1010-Mar-10@
@
I need to capture two pieces of information for each Job No. on a daily basis:
1- The 'Planned' Station for the build, based on a match of the 'Plan' date fields to a pre-populated 'Report Date'.
I've been able to do this (using INDEX-MATCH function).
2- The 'Actual' Station location for the build, based on the Maximum 'Actual' date entry in the row for each build.
(In the example above, Job# 1A1001 would have an 'Actual' location of 'Sta2 Act'...)
I need to figure out how to accomplish step (2) above. I've made several attempts with INDEX-MATCH and LOOKUP functions, without success.
View 10 Replies
View Related
Jun 18, 2008
I have a worksheet with a data sheet that has the values for a number of portfolios over a time period (one month)then I have a corresponding sheet for each portfolio (in the attached example I have included only portfolio 1). What I am trying to do is to make a formula in the sheet Portfolio 1 that matches the date in the Portfolio 1 sheet with the data sheet and by matching the date returning the value for the Portfolio 1 for the date ie date 2008-06-01 should return for portfolio 1 (from the data sheet) 500.
I have tried with varying Index and matching formulas and I do believe that this is the way to go.
View 3 Replies
View Related
Mar 14, 2009
I am having a problem in using lookup formula. Unfortunately for some entries it does not give the exact matching pair but one upper. I have attached the excel sheet. Please correct me where is the mistake. More about the sheet:
One column contains the alphabets from a certain language and second column contains corresponding unicodes. I want to search the unicode of a particulat alphabet using "lookup". Cell C4 is the key value. D4 uses lookup to get unicode value
View 2 Replies
View Related
Jul 24, 2007
Q:How to delete reversal entries?
I have debits & credits in the same excel column and i want to delete the matching amounts but with opposite signs.
Example:
A B
Name Amount
1)Mr. A 2000
2)Mr. B 6000
3)Mr. A -2000
4)Mr. D 4000
5)Mr. A 2000
Now i want to matching amount of Mr. A of row 1 & 3 as these two entries are reversing each other. I am poor in english but hope that i have clarified the problem
View 7 Replies
View Related
Feb 5, 2013
I have two workbooks at the moment, one which holds a whole host of customer and accounts details and another which holds only the customer details. What I'm trying to do is cross reference the two to determine if a customers details appear on both and if they do highlight this, ultimately I'd like it to pull the customers account number back but even if it highlights a match that would be sufficient.
The only unique criteria I have to do this are customer surname and customer postcode.
In the Customer details spreadsheet I'm attempting to enter a formula which will look at the specific surname & postcode held on the row in which it is entered. It will then look at all of the rows on the other spreadsheet and determine whether any of them meet both criteria.
If they do I'd like it to display "match" if they don't I'd like it to display "no match"
View 2 Replies
View Related
Dec 24, 2013
I have a set of sheets on a workbook that contain addresses, which are all combined on the first sheet (as you can see in the attached file). I would like to use a formula to compare the addresses on the Main sheet with the other sheets, then return in the D column of Main a colored cell indicating on which sheet the address was found on; different colors for different sheets. I tried to figure out some conditional formatting, but to no avail. Also, on occasion I have spreadsheets with 5 or 6 sheets containing these mixed addresses. Is there a solution that won't be limited to just a few sheets for comparison?
Excel Help.xlsx
View 8 Replies
View Related
Feb 8, 2014
This follows on from my previous posting [URL] ..... which produced a solution using an ActiveX Combobox that unfortunately does not work on Mac PCs!
I tried to replace the ActiveX with a Form Control Combobox but could not make it work.
So I am trying to use the alternative of "find, copy and paste" the relevant information.
As shown on the attached 140207 FINDALL test.xlsm, I need to find all records containing whatever string is entered into the "Search" cell, and copy data form three columns onto the Entry sheet.
The User will then select whichever of the entries they want to use, which will populate the relevant cells.
Problem: The following Code is not recognising any of the data in the Column being searched.
VB:
Option Explicit
Sub FINDPARTS()
Dim ws As Worksheet, i As Integer, k As Integer, z As Integer, CL, myFind, CHOICE As Range, lr As String, lrG As String,
[Code] ......
View 2 Replies
View Related
Jul 9, 2012
want to match column A to column C and if they match take whats in column D and put it in column B.
Column C has multiple instances of Column A and I would like each instance's, which is the following column, value put into column b
View 2 Replies
View Related
Jun 8, 2014
I am attempting to place a formula in cell b3 in the attached spreadsheet on the "sheet to pull data into" tab 1 (cell is highlighted yellow) to retrieve value in the yellow highlighted cell in the "Sheet that contains data" which is on a seperate worksheet within the same workbook.
The cells highlighted red in the "sheet to pull into" tab (cells: a1, a3, b1, and b3) are the criteria that needs to be matched to those cells on "sheet that contains data" tab in order to ensure the value returned is correct.Sample of problem.xlsx <----see the attached sample spreadsheet.
View 2 Replies
View Related
May 29, 2014
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
View 9 Replies
View Related
Jan 27, 2014
see 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
View 6 Replies
View Related
Mar 25, 2008
my existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame.
Sub Allmacros()
Dim WorkbookRust As String
WorkbookRust = ActiveWorkbook.Name
ChDir "C:Documents and Settings vogtMy DocumentsRüstplausch"
Workbooks.Open Filename:= _
"C:Documents and Settings vogtMy DocumentsRüstplauschCH_Revenue_2008.xls"
Sheets("Main_Overview").Select
Windows(WorkbookRust).Activate
Application.run ActiveWorkbook.Name & "!UpdateEntries"
Application.run ActiveWorkbook.Name & "!FilterMain"
'not ask to overwrite existing file
Application.DisplayAlerts = False
Workbooks("CH_Revenue_2008.xls").Save
Workbooks("CH_Revenue_2008.xls").Close
End Sub
Sub UpdateEntries()......................
View 7 Replies
View Related
Aug 15, 2008
I have a list of eastings and northings for specific incidents, then in a seperate sheet I have a list of eastings and northings for postcodes.
What I want to end up with, is which postcode the incidents are in!
So it means getting Excel to find the closest match of two values to two other values and return a corresponding value.
Here's what it looks like!
Incident Easting Northing
01 437302 113607
02 437340 113673
03 437366 113701
04 437366 113701
05 437474 113581
Postcode Easting Northing
SP410TQ 430449 91321
SP410PY 429158 91339
SP410QB 429258 91375
SP410PZ 429288 91407
SP410EA 428940 91444
I want to end up with
Incident Postcode
01 SP7185H
etc etc.
View 9 Replies
View Related
Aug 15, 2008
I have a list of eastings and northings for specific incidents, then in a seperate sheet I have a list of eastings and northings for postcodes.
What I want to end up with, is which postcode the incidents are in!
So it means getting Excel to find the closest match of two values to two other values and return a corresponding value.
Here's what it looks like!
Incident Easting Northing
01 437302 113607
02 437340 113673
03 437366 113701
04 437366 113701
05 437474 113581
Postcode Easting Northing
SP410TQ 430449 91321
SP410PY 429158 91339
SP410QB 429258 91375
SP410PZ 429288 91407
SP410EA 428940 91444
I want to end up with
Incident Postcode
01 SP7185H
etc etc.
View 9 Replies
View Related
Jun 7, 2014
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.
View 2 Replies
View Related
Dec 20, 2013
Formula(s) to do as explained in the attached example.
Example_formula.xlsx
View 9 Replies
View Related
Oct 9, 2009
I have a Listbox that outputs data to Column A. Is there a way to auto populate Column B with 4 items for each Entry in Column A?
The 4 items that will populate in Column B will always be the same.
I have attached an example to better explain what I am trying to describe.
View 6 Replies
View Related
Feb 20, 2009
Let's say I have a dataset with blank or X in column A, and a dollar value in column B. I want to sum the dollar amounts for column B only if there is a corresponding X in that row in column A. The way I currently do that is by creating a new column C, making a if statement to display the value of B only if there is an entry in A, and then doing operations on that new range. I figure there has to be an easier way. I don't know how to use VLOOKUP, despite trying to read the helpme a bunch of times. Is that relevant?
View 8 Replies
View Related
Feb 17, 2009
If I have a column full of data is there a way to copy every nth row to a new column? For example, I may have dates and corresponding days of the week. Can I copy the date of each tuesday (i.e. every 7th row) to a new column?
View 2 Replies
View Related