Find Function Within Large Data

Aug 1, 2006

I have a large amount of data, in columnA a list of cars, columnB a list of engines, in columnC the city built, in columnD the country to ship, in columnE distance, in columnF the time.

Now some makes, city's and destinations match and sometimes the engine, but i would like to be able to say cellX = (time to ship) where cellA = Ford AND cellB = 1.8 AND cellC = London and CellD = Ireland

The key data is the city built and shipping destination, so i'd like to say if cell A3 = London AND Cell B3 = Ireland AND Cell A5 = Ford AND CellA7 = 1.8 then CellA10 = (distance) & CellA11 = (time)

Vlookup's are of no use due to the large amount of data. But is there a way of writting this command in VB?? or am i missing something in Excel?

View 6 Replies


ADVERTISEMENT

Find Value In Large Range Of Data

May 29, 2014

I have enormous amounts of data to sort through (as we all do) and I am trying to link them to unique identifiers for easier data maintenance later.

What I want to do is vlookup from the 'solved' sheet a code, which will search the 'data' table and return the 'DFU' code.

Example 2905.xlsx

View 5 Replies View Related

Find Top 3 Value From Large Number Of Data

Apr 20, 2014

I am facing problem to make summary sheet of top 3 value from large number of value of my excel sheet like-

Sheet1

A
Group
B
Name
C
Value

1
Nashid
200145

[Code] ......

Now I want to make summary in sheet2 top 5 value among the list of sheet1 separately from group 1, 2 & 3.

If group number is 1 in sheet1 then top 3 value will be like this-
Group1
Name
Value

Nashid
200145

Jabed
184198

Eman
156622

If group number is 2 in sheet1 then top 3 value will be like this-
Group2
Name
Value

Rubel
172569

Hasin
152304

Monem
104463

If group number is 3 in sheet1 then top 3 value will be like this-
Name
Value

Romel
168251

Nasim
136357

Hasan
120410

View 1 Replies View Related

Find Large Value If List Is Having Duplicate Data?

Dec 30, 2011

I am facing problem in finding the second large value while my list is having duplicate data.

For Example

my numbers are:-
12,65,45,12,87,90,65,87

if I want to find out the top four value by using the formula:-

=large(A1:A8,{1;2;3;4})

the result is coming like: - 90,87,87,65

instead of this I want the result as: - 90,87,65,45.

View 6 Replies View Related

Method For Find Large Number Of Data

Jan 19, 2010

Each sheet contains 30,000 rows of data in column A, C, E, G and I.
I want to find each cell of column A, C, E, G and I to another sheets column A, C, E, G and I respectively.
If match found then it should return "True" in column B, D, F, H and J.

I have tried VLookup, but it takes too much of time for calculation (1,50,000 vlookup Formulas needs to calculate in each sheet with If condition) some time calculation gives wrong results.

As well as I want to do filtering on this all the data... so that time calculation gives the problems (I have used application.Calculation=xlCalculationManual)

So now I am using Range.Find method, but that loop excecutes for 30,000 times and each ittration contains 5 Find methods.

View 9 Replies View Related

Excel 2003 :: Column Function Insert Within Large Amount Of Data

Nov 27, 2012

I am trying to insert three columns within a large amount of data. I am using Excel 2003 edition. The three columns need to measure max, min, and standard deviation of month long ranges and the data goes all the way back to 1993.

Currently, I have a column that has the correct ranges but finds the average for each month

(=AVERAGE($H7214:$H7243))...

And many more ranges as it dates back all the way to '93. Is there a possible way to insert these three columns with their respective commands (=MAX... =MIN... etc.) while keeping all the ranges from the AVERAGE column.

In effect, I am looking to solely switch the begining of the column command

(=AVERAGE($H7214:$H7243)) to (=MIN($H7214:$H7243) etc...

While keeping all of the specified ranges from the AVERAGE column.

View 2 Replies View Related

Large Function And Corresponding Name?

Jul 14, 2009

I'm trying to create a mini-table that will give me the 3 highest and lowest values in a range (I know how to do this using LARGE and SMALL functions), but I also want to get the corresponding name (in column A) for each number. This last part I don't know how to do.

I've attached an example sheet that I hope gives a basic idea of what I'm trying to do. I'm looking for a method to fill in the data for cells A13:A15 and C13:C15 in the attached sheet.

I could modify my worksheet a bit and probably get the result I'm looking for, but I'm hoping to come up with a nicer solution, and hopefully learn Excel a bit better in the process.

View 2 Replies View Related

Use Large Function With Sum Criteria

Aug 24, 2014

I was doing this task using sorting then adding column today morning one of my friend told i can do it with formula only, no need to using sort and adding new column

I just attached the sheet : without sorting.xlsx‎

View 5 Replies View Related

Using Large Function In An Array

Jun 8, 2007

Column C contains the names of real-estate brokers.

Column E contains the colors of the homes for sale.

Column Q contains the values of the homes.

I want to return the sum of the 50 most expensive homes that are yellow and for sale by Century 21.

I'm familiar with array formulas, and with the large function. I just do not know how to incorporate the large function within the sum/array formula.

View 9 Replies View Related

Large Function With Multiple Ranges

Feb 1, 2010

I am trying to use the large function over two ranges. Even though incorrect I will include my original formula that doesn't work to help illustrate.

if(large((L20:L500,W20:W500),1)=I20,$P$4,if((large((L20:L500,W20:W500),2)=I20,$P$5,0)))

Does anybody know of an easy way around this?

View 6 Replies View Related

Hash Function For Large Strings

Mar 7, 2006

Excel vlookup function returns #VALUE when the lookup value exceeds 256
characters. I need a hash function to transform large strings into a value
that does not exceed the 256 character limit.

View 14 Replies View Related

Large Function - Logic To Handle A Tie

Apr 5, 2012

I use a large function when ranking numbers in an array each month. I'm only interested in the top 5 numbers. However, there are occasions when the top 5 numbers contain a tie. How do you build into a large function, logic to handle a tie. Here's my function, which is very simple:

Code:

=LARGE(C$6:C$40,1)
=LARGE(C$6:C$40,2)
ETC.

View 1 Replies View Related

Large Function Combined With Sumif

May 23, 2012

I know how to use the large function when looking for a specific criteria as the example below illustrates:

Code:
=IFERROR(LARGE(IF('2012'!$D$1:$D$7="Green",'2012'!$C$1:$C$7),1),"-")

What I'm trying to do is take this one step further and not only find the nth largest numbers in a set of data based on a particular criteria, but also sum those numbers because they repeat in a table: for example a sample table is below:

Account Accout # Store # channel $ sales
A 1000 10001 green $100
B 1001 10011 green $230
A 1000 10002 green $120
C 1002 10021 brown $145
A 1000 10003 green $100
D 1003 10031 red $20
B 1001 10012 green $50

So what I'm basically attempting to do is bring in the nth largest accounts within the "green" channel. Now if these were the top 5 stores I was looking for, the formula from above would suffice. However since this deals with accounts and the account # repeat I need to bring in the total sum of those repeating accounts instead of just one of the unique stores. So if I was seeking the largest account (NOT store) within the "green" channel the correct values this formula would be:

Account A $320

I would imagine we would need to combine a sumif with a large function or maybe involve sumproduct somehow.

View 6 Replies View Related

Large Function With Multiple Cells

Feb 9, 2009

I'm trying to use the Large function with multiple cells to get the highest value of the cells. Excel won't let me use the following formula.

=large({a1,f1,j1,o1},1)

Any other ways to get the largest value from multiple cells with or without an array.

View 9 Replies View Related

Use SMALL() & LARGE() Function Without Duplicates

Sep 3, 2009

I have a column of number that I want to use the Small & Large function on, skipping duplicates.

Example:

Column A has the following numbers;

1
6
1
4
3
2
4
6
17
8
21
8
10
13

I want the Small function to identify the first lowest number (1) and then the second lowest number (2), not (1) again.

View 9 Replies View Related

Large Function For Multiple Worksheets

May 6, 2007

I am trying to use the 'large' function to input data from multiple spreadsheets, but am uncertain how to do so other than to use the same rows/columns in each and to use only continuous worksheets (that is, I want to enter data from worksheet 1,3,5 and different columns in each, for example).

View 4 Replies View Related

Large Hexadecimal To Binary Function

Jun 9, 2007

I read the thread Converting from Hexadecimal to Binary and what I need to do is convert the following hex number to binary; 9E6799CF. The function in that thread doesn't seem to handle that large of a hex value. Is there anyway of doing that with a HexToBin function, short of chopping it up converting the smaller numbers and recombining it in binary?

View 2 Replies View Related

Large Function Is Returning To Duplicate Values?

Jun 14, 2014

I have been facing few problems while automation my production sheet through formula.

1-- I want to sort data in the descending order through below formula.

=INDEX($A$23:$C$29,MATCH(LARGE($C$23:$C$29,ROW(B3)),$C$23:$C$29,0),MATCH(H$22,$A$22:$C$22,0))

when two candidates have the same percentage then this formula does not work.

View 1 Replies View Related

Create User Input For LARGE Function

May 18, 2009

I want to create a user input for a "LARGE" function so that I can then return the specified # of "large" items. i.e. user wants to get the top X number of users, user then enters number either into a dialog box or cell, code then looks at the number and populates a range of cells based on the input value. The current code that I am using simply refers to an existing table (r9:r30) that I then used the auto fill to copy to cells below it.

=INDEX(Sheet2!B$20:B$961,MATCH((LARGE(Sheet2!$F$20:$F$961,$R9)),Sheet2!$F$20:$F$961,0))

I really want to be able to just get a user dialog box going that will automatically populate both the range r9:rx (where X is the user input) and then also copy the above formula into the x number of cells below it.

View 2 Replies View Related

How To Get LARGE Function To Ignore Hidden Cells

Oct 2, 2012

How to get the LARGE function to ignore hidden cells? Is it possible?

I have a filter in a data set and am using a macro to pull in the top 5 largest values.

View 3 Replies View Related

Using Large Function To Sort Column Of Numbers

Dec 17, 2012

I have 3 columns of data: col. A = name (random order), col. B = Net #, Col. C = Gross #.

I am using =LARGE(C$1:C$4466,ROWS($D$1:D1) to Automatically sort col C in decending order.

I would Like to do another decending sort but only the values in Col C that corespond to a particular name in Col A. Can I imbed a index match function combination within the large function to do this?

Name
Net
Gross

All Sort
SortA
SortB
SortC
SortD

A
508
-200.129

101.685

[Code] ..........

View 6 Replies View Related

If Function To Skip Large Piece Of Macro

Jul 23, 2013

I need to add something to my macro to delete a sheet if it doesn't have more than 40 rows of data. If it has more than that, it needs to do a whole bunch of stuff to it. I've already got the whole bunch of stuff written, I'm just not sure how to do the if function. I took a stab at it, but it wants the count to be an object. I am not very good at writing code.

Code:

Dim count As Integer
Set count = "COUNT('33M & 33RUL'!A:A)"
If waffle Is "

View 1 Replies View Related

Using Lists To Find Information In Large Db

Nov 20, 2008

I have a sheet with many columns of data relating to different companies as shown below (obviously my actual sheet has much more data):

NOTE: the Mr. Excel forums display the data in the correct form. I've tried to fix it here but obviously that didn't work perfectly, so if you want to view it correctly, click on the link above.

Comp Name Facility Name Product Name clean date
Comp A
Plant 1A Prod A1 11/11/2008
Prod A2
Prod A3
Prod A4
Plant 2A Prod A1000 12/1/2008
Prod A1001

Comp B
Plant 1B Prod B1 12/5/2008
Prod B2

In a new worksheet I want to see only the information relating to the Facilities, specifically the clean date of each facility, such that the information looks something like this:

Facility Name clean date

Plant 1A 11/11/2008
Plant 2A 12/1/2008
Plant 1B 12/5/2008


I know that I can use a simply vlookup formula such as the one below:
=VLOOKUP(C21,Info,3,FALSE), where C21 is the Facility Name and "info" is a list of the last 3 columns of my initial datasheet. However, I need to remember add each new facility name to the 2nd list when it is added to the main datasheet.

Is there a way to have the 2nd sheet automatically add the new facility name every time I add one on the main sheet, but without showing the many empty rows inbetween?

View 4 Replies View Related

How To Find 1 Value In Range Of Other Large Values

Mar 24, 2013

I'm looking for a formula that would tell me if a value in colume E can be found in column M while the values in column M may have characters before and after the value I'm looking for. The values are alpha and numberic in many cases.

Find E2 in M:M where E2 = 0123 and is in M:M as a15012388. The "yes/no" result is marked in red where I would like to have the answer.

Description
Looking For
Yes/No

[Code].....

View 4 Replies View Related

How Do I Find The Median Of A Subset Of A Large Group

Apr 10, 2009

So what I have is a large list of items, and each item has the price of the product, and a product type.

What I want to do is find the median price for each specific product type, using a formula, without having to sort or filter my large list of products.

View 6 Replies View Related

Large SumProduct Array -- Original Title:Loop Function

Dec 19, 2007

could provide me a loop function in VBA for these calcultions.

Cell A1*E1 + B1*F1 + C1*G1
Cell A2*E1 + B2*F1 + C2*G1
......till
CellA100*E1 + B100*F1 + C100 * G1

These are the calculations for E1-G1.
The process must be repeated till E50-G50

So the second step is:
Cell A1*E2 + B1*F2 + C1*G2
Cell A2*E2 + B2*F2 + C2*G2
......till
CellA100*E2 + B100*F2 + C100 * G2

View 12 Replies View Related

Find The Top 5 /bottom 5 Values Using The Large/small Functions

May 28, 2009

I understand how to find the top 5 /bottom 5 values using the large/small functions, but the question I have is: How do I get associated fields. For example I attached a sample sheet with values (Fields: Ticker,TE,Sector,Return). How do I get the Ticker,Sector that the return is for. I'd like to show all the associated fields that relate to the retun, but I can't find the solution on how to do that.

View 3 Replies View Related

Sumif Function To Find And Collate Unqiue Data On Different Worksheets

Apr 9, 2007

Most excel/vba books say not to use the sumif function.
I have a very large workbook (9meg) and cant use VBA code as it slows down the interation calculations required.
I have used the sumif function to find and collate unqiue data on different worksheets.
Is there another function that i should be using?

View 9 Replies View Related

Find Row Of Large Value In Array And Return Values In Different Named Range

Feb 28, 2012

I have four named ranges (Segment, Keyword, Impressions and Dropdown) and I would like to create a formula-based ranking of keywords by impressions and clicks. Using the following array formula, I am able to return the correct values for impressions or clicks:

{=LARGE(IF(Segment=DropDown,Impressions),$H7)} where $H7 is the number ranking 1, 2, 3 etc.

My question is what array formula could be used to find which row in the array returned that number and then pulls the data from the same row in the other named ranges?

Essentially find row of {=LARGE(IF(Segment=DropDown,Impressions),$H7)} but return Keyword and Clicks on that row.

Other Notes: I cannot use pivot tables and some values might be the same which would make Vlookups not accurate for duplicate values.

Link to an example document to clarify this. [URL] .......

View 9 Replies View Related

Import Large Text File Across Multiple Sheets. Could Not Find Installable ISAM

Apr 19, 2008

I found following code on this site to extract txt files. As mentioned this code can extrack huge txt data to multiple sheets.

Sub ImportLargeFile()
' Imports text file into Excel workbook using ADO.
'If the number of records exceeds 65536 then it splits it over more than one sheet.
'If a file in a different format is to be imported amend the extension at line 10
Dim strFilePath As String, strFilename As String, vFullPath As Variant
Dim lngCounter As Long
Dim oConn As Object, oRS As Object, oFSObj As Object.............

However I am getting Run time error '-2147467259(80004005)': could not find installable ISAM for section 'Open an ADO connection to the folder specifiedin above code

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved