List Matches In A Table?
Aug 12, 2012
I have a large table that gives me the intersection points of many lines. I have used conditional formatting to highlight the intersection points that match by turning the text red, thus showing me when 3 or more lines intersect. Manually scanning the whole table is inefficient as I wish to expand it.
Due to size limitations, I've uploaded a shortened version of the workbook, just a copy with one example of a match.
How do I get excel to locate the matches (currently highlighted with red text) and list them in column I "Intersections" all neatly sorted?
I thought Advanced Autofilter but I don't think the criteria range can search for red text alone.
View 6 Replies
ADVERTISEMENT
Jan 28, 2009
is it possible to have a cell return all matches from a list and have the list of matches reduce as you type, then be able to select one item from the list? this is a typical feature on internet sites, but can it be done in Excel?
View 3 Replies
View Related
Jan 29, 2010
In my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":
1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List
2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List
3.) Report In Column D: Identify and return all unique values in "Column C" as a List.
Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"
View 4 Replies
View Related
Jun 25, 2008
I have a chart of accounts that is probably 30,000 accounts long. I have another list with say 500 accounts. I need to check to see if the 500 accounts exist anywhere in the 30,000 accounts. The next step is, the 30,000 accounts is separated into different categories, so say it's 30 blocks of 1000 accounts. I need to check the 500 list in each of the blocks. I'm just trying to see which accounts are missing from the 30000. So if they exists in each block then all is good, if they don't, we have problems.
View 3 Replies
View Related
Nov 27, 2006
I need to return all the unique matches from a set of data.
For example, when a product is selected, I need to list all the possible venues it is sold from. I will use this venue list to populate my drop-down listbox, as opposed to having a 100 venues in the list, I just wish to have the applicable ones.
Example of data set I need to lookup in:
prod 1 venue1
prod 2 venue 2
prod 1 venue 3
prod 1 venue 10
prod 2 venue 6
I would like to use a formula if I can (not a macro), to list that prod 1 is only sold in venue 1,3, 10.
It would be great if one could use a vlookup type of formula and it could return a list of each occurence of the look-up'ed cell.....
View 10 Replies
View Related
Nov 15, 2011
Macro that would copy a row if the value is found in separate list. For example, in column B of sheet "223" I have values. In another sheet "DATA" I have a list of values, which if these values match the value in column B of sheet "223" it would copy the entire row from "223" to another tab "output".
View 9 Replies
View Related
Feb 13, 2008
i am trying to do a lookup on a data column (B) which contains time data on a worksheet which is a constantly growing list from a DDE data link macro- the data i want returned is in column A,
so for example
column A, column B
0.8970 13/02/2008 21:58
0.8965 13/02/2008 21:58
0.8990 13/02/2008 21:58
0.8995 13/02/2008 21:59
0.8982 13/02/2008 21:59
0.8983 13/02/2008 21:59
i am looking for the highest and lowest values where the time matches a certain hour of the day, so where another cell on the worksheet 1 equals 22, do a lookup on all the rows which is in time 22:00 and return the max no in column A (0.8983),
and the min in another cell(0.8977)
think my limited excel skills are not up to it i'm afraid despite searching through the forums and trying out a few lookup and max combos as array formulas.
View 9 Replies
View Related
Nov 30, 2006
I am looking for a way to check for the presence of a member of a list in a field. Not for an exact match, but for the text to be somewhere present. I know I can use the "search" function to find text within text, but I don't see how to find the presence of any member of a range of values (a list) within the text.
View 9 Replies
View Related
Aug 13, 2008
I have two excel sheets, one loads up in Listbox1 and the other in Listbox2, i would like to be able to see if any entries in listbox2 match with entries in listbox1 and then to display the matches in listbox3 with a click of a command button. Is this possible? If so, how would i go about it?
View 3 Replies
View Related
Jul 2, 2008
I have a selection on a worksheet that has 4 consistent columns (A, B, C, and D) each with a variable number of rows all containing string type data. With this data I want to use Conditional Formatting so that when I have a string value of X in column D for any row, that whole row with the X in column D has it's color change to say grey, this should happen regardless of what strings are in Columns A, B, or C.
Below is the way I've been trying to do this thus far and failing, when I run this code below I can only get it to grey out the cell with the X not the whole row.
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""x"""
Selection.FormatConditions(1) . Interior.ColorIndex = 16
View 4 Replies
View Related
Dec 20, 2012
An example will be as follows. List all possible outcomes for 3 matches. That will be 27 possible outcomes.
I would like results for my request of 50 matches to be displayed as follows.
HHH
HHD
HHA
HDH
HDD
HDA
[Code] ...........
Where:
H=HOME
D=DRAW
A=AWAY
Is there a way i can have the possible outcomes listed as above for the outcomes of 50 football matches? I do know that the outcomes will be hundreds of millions if not billions.
View 9 Replies
View Related
Jul 16, 2009
1) Background Info
We are trying to summarise some data that has been exported from an ancient database into a poorly delimited csv file. My colleague has imported the csv file into Excel ( attached), and we are trying to work out what to do next.
The dataset contains approx 300 records - each record being called a "sample" and having a unique sample number. NB: I have had to attach a cut-down version with only 3 samples, due to file-size - but it will hopefully give an idea.
Each sample contains 2 types of information that we are interested in (and a lot of irrelevant data besides). The relevant bits are:
- predicted occurrence of various species (given as a percentage for each species)
- observed occurrence of various species (given as positive/negative for each species, where positive is indicated by an asterisk)
2) What We're Trying To Do
We are trying to compare this dataset with a master-list of 80 species names. For each species on the master-list, we want to:
a) check whether it has a "predicted occurrence" value in each of the 300 samples (and if so, record the value in a summary sheet)
b) check whether it was observed in each of the 300 samples (and if so, record this in the summary sheet)
3) The Problem
Unfortunately, the dataset has imported into Excel as a gigantic list: 48000 rows (including loads of blanks) and only 3 columns across. To find the relevant data, it's necessary to:
a) open the attached workbook, and go to the worksheet named "Data"
b) Scroll through the rows, looking for the string "RIVPA" in column A. This tells you where each new sample begins. (The sampleID is stored in the same row as this, in column C. It is mixed up with a load of text, which we will need to separate out at some point, but that's a secondary consideration at the moment.)
c) Scroll down further until you find the text string "Predi" in column A. This indicates the beginning of the data we're interested in, for each sample (i.e. for sample 1, I'm talking about row 58). Count down a further 2 blank rows, and then you find the data itself:
- Column A contains the observed occurrence (a positive result is indicated by an asterisk)
- Column B contains the predicted occurrence
- Column C contains the species name
My colleague was trying to build a summary table, in the worksheet named "Summary". He was using the LOOKUP function to extract the data, but because there are so many samples, it's beyond unwieldy. He asked me if I could write a macro do do the job, and this is where I'm stuck.
How could I set up a macro that can identify where one sample starts and finishes, and where the data is within each sample? What is the most sensible shape for such a macro? If I could get the overarching logic worked out, I could make a start on writing the individual bits of code, but at present I just can't figure out how to begin.
To make things more difficult, the samples are not all the same length, and do not all contain the same list of species as each other. (However, at least there should be no species in the samples that are not in the master-list). The first sample begins on row 5, the second sample begins on row 173, the third on row 340, and so on.
View 8 Replies
View Related
Mar 14, 2008
Most likely another very basic on...
2 worksheets.
Worksheet 1, holding all data in column C.
Worksheet 2, the value in column B.
In need for a function that list all exact matches of the values in worksheet 2 column B that are found in Column C of worksheet 1.
I got this VBA code that checks for the string in worksheet 2, column B, but it is a Instr, and I need a exact match and only the exact match. Is this difficult to modify and how?
Or would this be a regular array function?
(I would not need a macro, since the data change constantly and I prefer function as they update easily)
Function matchArray(ByVal testString As String, ByVal dataRange As Range, Optional IndexNum As Long) As Variant
Dim outRRay() As String
Dim rangeArray As Variant
Dim xColl As New Collection
Dim xVal As Variant
Dim rIndex As Long
View 9 Replies
View Related
Sep 15, 2014
I have used INDEX/MATCH/ROW/SEARCH functions, in different permutations, but I am unable to get the result. The data set is something similar to the below:
Car
Region
Own
Use
Color
Honda
North
Yes
I use it to go for work
Green
[Code] .....
I want to be able to do following (2 separate tasks):
Task 1 (if in A1 on a new sheet, I had Use, i want to list all the items in an adjacent column, skipping the blank rows)):
Use
I use it to go for work
Family trips
Weekend fun
2nd car
Work
Task 2 (if i had Honda (A2) and North (B2), I want to have the colors listed in Column 3):
Car
Region
Color
Honda
North
Green
White
I know this can be done by an auto-filtering or manual sort, but I have work with thousands of similar data on a regular basis, and i want to find a formula that will allow me to list the items based on different criterion.
View 2 Replies
View Related
Mar 5, 2014
I am having trouble with my macro. I have attached excel sheet for reference.
Basically, I want a Macro that Looks up columns B & C of current sheet and matches it with table in the charges tab, and then returns hourly pay in column D.
I have created the macro but it's giving me Run time 1004 error.
Also, my formula is incorrect. Maybe that's why my macro isn't working?
2WayLookup_Macro.xlsm
View 4 Replies
View Related
Mar 24, 2014
I'm looking for a macro to jump to and select a cell if it matches the text in another cell based on a drop down list. So I have a drop down list in cell c57 and a form control search button right next to it. I want the user to be able to select from the drop down, click search and then jump to the cell that matches what they selected. I would like the macro to search from B:60 - B629.
View 4 Replies
View Related
Apr 29, 2009
I have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK
View 3 Replies
View Related
May 14, 2009
Hi Guys, This has been bugging me for a bit now and I just can't sus it...
I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.
I have beside that a column for holidays, etc and then a another column for other events.
When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).
The formula I have been playing with (no success) is:
=MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.
The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...
Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...
I have attached the spread sheet that I am working on.
View 2 Replies
View Related
Jan 9, 2007
I have 1) a list of 10,000 customer records with customer numbers (including duplicates) in field A, and 2) a separate list of 25 customer numbers that are "red" accounts.
I want to add a new ID field to the larger customer list, and using the 25 red IDs, I want to mark any of the 10K records that match the list of 25 with an "x".
I thought about using multiple nested-function formulas and concatenating the results, but then, I always do things the hard way! ...
View 9 Replies
View Related
Feb 20, 2007
I am just learning to use VBA and this may be the most simple task ever, but I can't figure it out. I've searched for all of the keywords I can think of, but can't find a solution...
I have a list of names in a sheet. Other columns in this sheet contain data like amount charged, amount paid, etc. This sheet must be manually updated (because the other program won't export the information I need) periodically to ensure proper billing/payment application in the original software (all transactions are handled by other people that I don't trust).
I sort the list so that the all names that are the same (ie John Doe) are together.
A short example list looks like this:
Jeremy Apple
John Doe
John Doe
John Doe
Jimmy Kravitz
Jimmy Kravitz
In updating my sheet, I set up a macro that will input todays date in one of the columns for all occurences of that name (so, every row that contains John Doe in column B, column V will have todays date in it).
Currently, in order for my macro to work properly, I have to manually make the activecell the first occurrence of 'John Doe'. When I'm ready to update 'Jimmy Kravitz', I have to select the first occurrence of 'Jimmy Kravitz' and so on.
Here's my question - Is there a way to use a VBA macro to find the first occurrence of 'John Doe' (and automatically 'know' which name I am updating)? Basically, I need a macro that will take the information that is in the cell in column B in the active row, find the first row that has that same name, and make that cell (column B) the active cell...
View 9 Replies
View Related
Jul 29, 2008
I have a set of accounts (general ledger accounts) that the accounting group posts expenses to. every once in awhile a new account is added. This is captured through a pivot that i have built (sheet A).
on another sheet (sheetB) i want to display the accounts that are shown in the pivot so that i can forecast their future activity.
What i am trying to figure out is how can i make the list on Sheet B change when new accounts are added to the pivot on Sheet A (without simply referencing the pivot table making one cell equal the other)? I am not looking for a data validation pull down, i want a full list of the accounts.
View 9 Replies
View Related
Nov 7, 2008
I have data in a list of sales people and dates of their sales calls. What I want to do is transform the list into a table as shown in the attachment. Then I want to calculate the number of days between the first and last sales call. This part is easy using date formatting and min/max I guess. What I can't do is get the table arranged. I have used a pivot table to count the number of calls per sales person but pivot table wont just display the dates in a table format, it will only sum or count etc.
View 3 Replies
View Related
Aug 28, 2009
I have a long list (almost 17,000 rows) with two columns (ID and Code). There are about 1400 IDs, each with multiple codes:
ID Code
1 ab
1 mn
1 hh
2 mn
2 hh
3 ab
etc.
I need to turn the list into a table that looks like this:
ID Code Code Code ...
1 ab mn hh
2 mn hh
3 ab
Each ID has a different number of codes (anywhere from 1 to 89).
View 5 Replies
View Related
Nov 12, 2007
I am wanting to manage my inventory and need to convert spread sheets with tables specifying style, size color to lists with skus specifying style, size color. please see attached spread sheet with one sheet with table and second sheet with list.
View 11 Replies
View Related
Jun 14, 2012
I have three columns of data starting in row 5, the headers of these columns show Origin, Destination and mileage. Is it possible to have a macro that converts the data into a table?
View 2 Replies
View Related
Jun 3, 2014
I am trying to convert a table into a list, please see the example, I want to convert this table
10547
aaa
140x200
160x230
300x400
10549
bbb
140x200
150x260
[Code] ........
View 1 Replies
View Related
Sep 18, 2008
I need to convert this kind of table to a list (like in below example)
Can someone give me an instruction or macro to do this ?
Table
X Y Z
A 3 5 7
B 2 4 6
C 8 9 10
List
Column1 Column2 Column3
A X 3
A Y 5
A Z 7
B X 2
B Y 4
B Z 6
C X 8
C Y 9
C Z 10
View 9 Replies
View Related
Apr 14, 2014
I have a file and it has a table on it with 2 categories.
what i want to do is select a color in the first drop down list and than in the second only select the items that match the color i have previously selected.
Is there anyway to do this with named ranges because i would like to index everything in a table.
View 3 Replies
View Related
Jun 19, 2008
I'm trying to create a Pivot table with various columns comparing information of executives from various Private Equity Firms. Some of the additional columns are Industry Specialty, Harvard Grad Year, My companies contact, etc . . .
The goal is to allow me to then query this information so if I wanted to see all Bain Capital employees who graduated with an MBA in 1990 and their specialty.
My question comes in trying to set up my source list for the data under Industry Specialty and my companies Contact. Is there a way I can set a value for these cells and mark them as an "AND" in the source if there are multiple contacts or industries that one of the Private equity executives is linked too, that would then allow them to be displayed as so when I create the pivot table?
Attached is .zip for further clarification.
View 9 Replies
View Related
Feb 24, 2010
I'm trying to create what is essentially a report card for our staff. I have all the data in a pivot table and then I am using 'GETPIVOTDATA's in the report template to extract the information, linking the sheets via a cell that contains the staff members name. Easy enough. However for reasons of efficiency and practicality I would like to be able to select the names from a drop down list on the sheet rather than having to type it in manually or maintaining a separate sheet for everyone. The operators will change from month to month and there are approximately 90 staff at any given point in time that will need to be reported on so it really needs to be sourced from the pivot data.
View 7 Replies
View Related
