Get LARGE Value And Worksheet Name Along With It

Apr 29, 2014

I have 5 sheets all with different data within the H column. I want to find the top 10 highest results across all 5 sheets and then pull the worksheet name where those results come from. It would be two separate formulas, one for the LARGE value and the second for the name.

This is all I have.

O2:O11
=LARGE(Sheet1:Sheet5!$H$3:$H$102,1)
to
=LARGE(Sheet1:Sheet5!$H$3:$H$102,10)

I then have this which was the best I could find after about 30 different formulas.
=MATCH(TRUE,COUNTIF(INDIRECT("'"&{"Sheet1","Sheet2","Sheet3","Sheet4","Sheet5"}&"'!$H$3:$H$102"),O2)>0,0)
It gives me the right worksheet but it only gives me a number. I'm assuming it's because of COUNTIF but I'm not sure what else to use.

Most examples I see have the names they want right beside the values. So in my case H3 is 22 (and the largest value within H across all sheets) and if I followed their examples G3 would be Sheet1. So because it's side by side it'd be easily pulled. But the only name references I have are the worksheet name itself or A1 of Sheet1:Sheet5 so I'm not sure how to rework their formulas to suit mine.

View 4 Replies


ADVERTISEMENT

Transfering Large Amounts Of Autofiltered Data To New Worksheet

Feb 10, 2009

I have a problem with my current macro that uses a basic autofilter to auto filter from the parent database to extract the correct rows and then copies the query and pastes it into a new worksheet to further proceed with the macro.

I have run into a problem because my database has become very big and now when I try to autofilter the query and click on copy, an error regarding the data range reference is too complex - use data that can be selected in one contiguous rectangle

I tried a few things such as to autofilter out everything I dont need and hit delete - this does not work either, same result

I got help here previously in which the code deletes All Hidden Rows and this is very time consuming, I have not tested all my methods but it took 15 minutes to delete hidden files for one method and theres roughly 5 in total

I have to end up running this code on the parent worksheet multiple times because I use the parent worksheet to extract different parameters into different worksheets!

I have noticed that if I manually copy the data in smaller blocks, by halving the data seems to work,but I do not know how large of a partition I am limited to copy because my database is very large and the size varies month-to-month so I cannot put a number on the max range. I think if I could get a macro to do it by thirds or preferably quarter range should be safe.

So just to summarize, I am trying to devise a method in which I would auto filter on the active parent sheet "sheet 1" and I would copy the auto-filtered query to "sheet2" instead of copying the whole worksheet in one instance I would like to split the autofitlered query into four equal parts with respect to the range of the worksheet and then to copy the first quarter of the query and paste in sheet 2 and then the second quarter to sheet 2 and so on untill all four quarters are done one after the other, so sheet 2 should be a series of all four parts combined into one series on sheet2

View 9 Replies View Related

Excel 2013 :: Saving Large Multiple Worksheet File

Aug 11, 2014

I have an Excel file that's updated monthly. when it does save its around 16mb and can take up to 12 hours to save, and sometimes just doesn't.

I have tried saving as binary, I have made sure exact size of area to be saved is required, I have tried save with no calculations.

Basically the only reason I need to save it is so that another analysis spreadsheet can pull data from it. The file is heavily formatted, charts, vlookup tables etc, none of which is needed when analysis spreadsheet links to it.

View 1 Replies View Related

Large CSV File: Too Large To Open. Split

Aug 6, 2003

I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).

Is there a way to split ( ) this file so I can open two files instead?

View 9 Replies View Related

LARGE With IF Statement?

May 1, 2009

I need to create a "Top 5" list based on two columns. The example is as attached (the real list is approx 22 000 lines):

I need to find the top 5 largest quantites (column A) for each flag catagory (column D) and include the part number and description. I have tried a pivot table and had some issues. Also tried a LARGE(IF...) type formula and still no luck. Does anyone have any ideas of what is the best way to do this?

View 12 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

Vlookup/if/large

Sep 4, 2009

I don't know if this is possible but thought i'd check. Could I put together a look up that pulls through the 5 best performing categories' so say i have 200 categories, just to pull on to a report page the top 5 and their result, and produce a report weekly so if the top 5 change it updates this?

View 2 Replies View Related

Workbook Is Too Large

Oct 7, 2006

I am having a problem with a workbook, I have copied all cells and pasted Special (Values only). But for some reason the workbook is 38 Meg's. The workbook only contains 6 worksheets with about 60 rows and 32 columns with figures and no formulas. I have tried to find formulas by Goto/Special/Formulas and Excel has found nothing. What shoud I do?

View 4 Replies View Related

Copy Worksheet In Workbook With All Formulas On New Worksheet Referencing Previous Worksheet

Apr 21, 2012

I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.

How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?

View 1 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 PRODUCT For Large Dataset

Jan 6, 2014

I have a set of 5,800+ data points between 0 and 1 that I would like to multiply together. When I use PRODUCT for the whole set, the formula returns 0. However, I can use a smaller subset of the data to return a very small number. I'm curious if Excel has a closest-number-to-0 or number-of-cells-for-PRODUCT limitation. Is there another way to perform this calculation?

View 4 Replies View Related

Concatenating Large Row Of Values?

Aug 20, 2014

Say I wanted to concatenate a row that had 10,000 values in it. How could this be achieved without having to click 10,000 cells while typing a comma in between each one?

View 5 Replies View Related

Large Number Count

Aug 17, 2007

If I have the numbers 1-50 repeating in columns, 1-50, 1-50 etc is there a way to determine that number "1" falls 1st, 51st etc in the sequence without doing a manual count? Or that the number 2 falls 2nd, 52nd etc?

View 10 Replies View Related

Amending A Large Formula

Mar 24, 2008

i have this formula below which works but if the criteria is not there it says FALSE because of the B22:C45,2,0) bit i would rather it says something like "no dissertation" is that possible? ...

View 10 Replies View Related

Concatenate A Large Range.

Jun 19, 2009

I have a column of numbers in A1:A200 that I need to concatenate with a ";" in between each into one cell. Is there a way to do this without clicking in each cell individually? =concatenate(A1:A200) just returns the value in cell a1.

View 2 Replies View Related

LARGE Vs SMALL- Array

Jan 8, 2010

I'm having a difficulty using LARGE and SMALL.I want to return the 1st to 5th value of an array (each in a different row) choosing months as criteria. While the LARGE formula works fine, when I replace the LARGE() formula for the SMALL() leaving everything else unchanged, the formula returns ZERO and there are no ZEROS in the original data! Here is the function:

View 4 Replies View Related

File Too Large To Open?

Mar 16, 2013

my colleague did something to one of our files and it is now 845918 kb. I transferred it to a flash drive and then to my hard drive but now I cant open it to see what is wrong. Is there a trick to opening this up? When it was opened on her computer it wouldn't let me add any tabs or move tabs. I think I unprotected it but that didn't change anything.

View 1 Replies View Related

How To Sort Two LARGE Columns

Oct 11, 2013

I have a list of domains (about 200,000) that looks like this:

I have a list of urls (about 1 million) that looks like this:

I want my results to only show this (since this is the only thing in the second file that does not contain anything from the first file):

I have tried programs designed to do this but they either (a) shorten everything down to the domain or (b) wont remove anything since the domains are part of a longer url in the second file.

View 3 Replies View Related

Macro - Procedure Too Large

Oct 15, 2008

I am using following event macro and it repeats itself more than 300 times. I am at a stage where I am getting a message "Compile Error, Procedure too large", I tried to break it into two but that's not working.

Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
Dim lng As Long
Application.EnableEvents = False

Select Case Target.Address
Case "$E$3"
If Target.Value = "Yes" Then
SelectNumber:
lng = Application.InputBox("Please enter number 0 to 100", , , , , , , 1)
If IsNumeric(lng) = False Or lng < 0 Or lng > 100 Then GoTo SelectNumber
Range("G3") = lng
Else: Range("G3") = 0

.................................................(above statements repeat more than 300 times for different cells)..............................
Application.EnableEvents = True
End Sub

View 15 Replies View Related

How To Input Formula Down Large S/s??

Nov 25, 2008

I'm using Excel 2007 and the sheet has 238536 rows containing 26412 ranges.
The first 5 rows in each range contains data. I wish to input into the sixth cell of every range a formula. Not essential but I'd prefer it to be formatted blue.
I attach short s/s to illustrate my need.

View 2 Replies View Related

Sum Large Database Using Criteria

Jan 4, 2013

I am building a database in excel where I have the number of days across the top (horizontal) and in Cell A2:A300 I list our product lines (that is repeated based on different divisions). Beside each product line in row b3:iv3 I have sales data for each working day.

In a new tab I want to formulate the total of ProductA on day 240.

A B C D E F G H
1 Region Product 235 236 237 238 239 240
2 RegionA ProductA 25 31 15 18 10 5
3 RegionB ProductA 21 13 10 5 28 10
4 RegionB ProductB 21 13 10 5 28 10
5 RegionC ProductB 21 13 10 5 28 10
6 RegionA ProductA 25 31 15 18 10 5
7 RegionA ProductC 25 31 15 18 10 5

In another Tab summary of Product Totals:

Day235 Day236 Day237
Product A 76 75 40
Product B 42 26 20
Product C 25 31 15

I tried many formulas for example:
=SUMIF('2012 Sales'!$C$3:$IS$113,$C1&$F$4)

View 9 Replies View Related

Combining Two Formulas Into One Large

Jun 6, 2013

i was wondering if there was a way i can combine these two formulas into one large one

=IF(AF8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($L$3:$L$103)-ROW($L$3)+1)/($L$3:$L$103=AF8),COUNTIF($AF$8:AF8,AF8))))

=IF(AG8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($M$3:$M$103)-ROW($M$3)+1)/($M$3:$M$103=AG8),COUNTIF($AG$8:AG8,AG8))))

View 2 Replies View Related

Getting Data From Large List

Jan 14, 2014

I have a list of IDs that recurs over a time period. It consists of a Start Data an ID Number. These IDs recur over and over again through one month with different start dates.

See Below:

Date ID
12/1/2013 10:00:00 AM 67890
12/6/2013 12:00:30 PM 67890
12/18/2013 06:30:05 AM 67890

From Another List I'd like to pull a max enddate that is within 24 hrs of the start date. There will be multiple end dates. Here is what the other list would look like.

End Date ID
12/1/2013 1:00:30PM 67890
12/6/2013 4:00:45PM 67890
12/18/2013 9:30:00 AM 67890

Seems like using vlookup with the ID as the lookup wouldnt work because it would just pull the first date it found over and over again.

View 9 Replies View Related

VBA- Large Number Of Lines

Jan 10, 2007

I am trying to copy part of one line of pasted information in to a certain place on the line below.

i.e I want
ABCabc

to look like

ABC
abc

This needs to happen for a large number of lines. I have already added a space in between all the original lines.

View 9 Replies View Related

Export Large File

Oct 30, 2002

I have a database of 75000 rows in text format which has to be converted to excel, but the limitation in excel is only 65536. hence, I want to a macro that will copy the 1st 65536 rows into the 1st sheet in excel. The remaining rows should be copied into the second sheet. I know that it can be done with access. but i dont want to use access.
I have got a code to do this, but it takes much too long. if the code could be modified to make it faster,


Sub LargeFileImport()

'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g.

View 9 Replies View Related

Paste Area Too Large

Jun 7, 2007

i am trying to first of all find the column that Alias appears in the first 3 rows and then paste that entire column to a new sheet.
most is working.

however i get an error 1004 saying the area i am pasting to is not large enough or the wrong shape.

Private Sub CommandButton5_Click()

'
' merge Macro
' Macro recorded 6/7/2007 by Ray O'Connell

Range("A1:K3").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False...........

View 9 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

Picture Is Too Large And Will Be Truncated

Jan 3, 2008

I had an Excel program set up on my 2 year old computer and recently purchased at Toshiba laptop with Alta Vista on it. When I transferred the Excel program from my older computer the message "The picture is too large and will be truncated." appears. I don't know how to overcome the problem. I can't operate my program on my laptop,

View 9 Replies View Related

Using INDEX & MATCH With MAX And LARGE

Mar 31, 2008

I have an Excel sheet on which I need to calculate the largest, 2nd largest and 3rd largest volumes from the range of data.

I've done this using an INDEX and MATCH formula to return the Status # for the largest, 2nd and 3rd largest volumes. So the formulas are:

Rank 1
=INDEX(B3:G3,MATCH(MAX(B3:G3),B3:G3,0))

Rank 2
=INDEX(B3:G3,MATCH(LARGE(B3:G3,2),B3:G3,0))

Rank 3
=INDEX(B3:G3,MATCH(LARGE(B3:G3,3),B3:G3,0))

However when the volumes are the same the formula returns the same Status # for both. For example below, for Site 1; Status 3 is ranked 1st and then Status 1 is ranked 2nd, for the 3rd rank, the formula is returning Status 1 again.

Is there any way of changing the formula to return Status 4 as the 3rd ranked instead of returning Status 1 again?

Status 1Status 2Status 3Status 4Status 5Status 6Rank 1Rank 2Rank 3Site 18655802Status 3Status 1Status 1Site 241818691Status 2Status 2Status 5Site 389126220Status 5Status 3Status 2Site 43591872Status 4Status 3Status 5Site 51650015Status 1Status 2Status 2

View 9 Replies View Related

Large, Slow Workbook

Aug 31, 2008

I have a large Excel workbook (about 70,000KB) and it takes ages to load and is slow to operate.

Is there anyway of compressing it or making it run faster and not slow down the computer?

View 9 Replies View Related







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