Large Formula With Dates With Duplicates
May 26, 2014
In cell G1 I have my snapshot dates header and dates running from G2 to G31. Next to in Column H I have the sold items by Product A.
There are 6 different snapshot dates and I need them to be horizontal with a formula and none duplicated with the largest (newest) date first so I can SUMIF the sold item per date. If I simply pull a LARGE formula it will return 24-05-2014 in all rows is it is the biggest "value".
When I them add new snapshots (biweekly) then the horizontal snapshot line will automatically add the newest dates (i.e. find the largest or newest, the 2nd newest snapshot, the 3rd newest, etc. Hence, in K2 we would have 09-03-2014 with 672 sold items in K3 (via SUMIF), in L3 we would have 18-03-2014 and 7523, in M3 23-03-2014 and 1703, etc.
I'm not sure if I explained it well enough. I'm attaching a file if this makes more sense.
G1 G2
Snapshot DateProduct A - Sold Items
09-03-201423
09-03-2014354
09-03-201435
09-03-2014254
09-03-20146
[Code] .......
Latest Snapshot
09-03-201418-03-201423-03-201410-04-201424-04-201424-05-2014
6727523170310787201035
View 12 Replies
ADVERTISEMENT
Sep 13, 2012
I am attempting to use the LARGE formula to pull the top 3-5 percentages out of a field of 50-100, while using the INDEX/MATCH function to pull the corresponding "descriptor" that is associated with those top 3-5 percentages. (I need to do this across multiple data sets, but I can't get past this 'duplicate' issue) However, I am running into the problem when there are 2 percentages that are identical (WH 14 and WH 16 in pasted text below), then the INDEX/MATCH function only pulls the 1st "descriptor" and doesn't continue down to the Duplicate. how to tell excel to move to the next set of duplicate data and match the 'descriptor' to that data?
I have attached a file that should show what I am trying to do. These are the formulas I am using right now, pulled down into the 3 cells below them to get the top 3.
B1:
[Code]....
C1:
[Code]....
Warehouse S/S %
WH 1 50.00%
WH 2 57.14%
WH 3 0.00%
WH 4 50.00%
WH 5 100.00%
WH 6 60.00%
WH 7 33.33%
WH 8 66.67%
WH9 60.00%
WH 10 63.64%
WH 11 78.57%
WH 12 55.56%
WH 13 42.86%
WH 14 71.43%
WH 15 61.54%
WH 16 71.43%
Attached File: Book1.xlsx
View 9 Replies
View Related
Feb 10, 2014
I am looking for the largest TOTAL SCORE in each division, but need to display 3 placings. Current error: If the score is a duplicate, it is repeating that score with the same rider.ALSO - I need to add the 'PLUSES' as part of the tiebreaker (i.e. 940 with 24 pluses would be 1st, 940 with 4 pluses, 2nd...etc)
View 3 Replies
View Related
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
Nov 4, 2012
(Excel for Mac 2011). The size of my data means am crashing by usual methods of sorting - I need a formula for this rather than pivot tables.
I have a list of c. 40,000 rows. Col A is client name (largely different but some duplicates); Col B is Policy type (eg Home, Motor, Travel) and Col C is earnings on that policy (eg -60). An example would be:
Name
Policy Type
Earnings
Johnson
Home
[Code]...
So I need to figure out (via a formula) two things:
1) How to count duplicate names (eg appearing 3x = 1 customer; appearing 2x = 2 customers; appearing 1x = 1 customer)
2) How to build a simple table to show the relationship between policies. This would have Motor, Home, Travel, Motorcycle as columns and also as rows - the data would then be how many policies and in each cross point?
I have tried just working the data using Countifs etc, but as soon as I apply it to the whole data set the machine freezes, presumably due to its size
View 6 Replies
View Related
Mar 17, 2009
i have a spreadsheet with a certain data (rate of return) associated to a specific day of a year. (ex. 8-dec.-08 0.99865), and i have to calculate the rate of return (r) for every month, which is done by applying a formula (GEOMEAN) to all the rate of returns for the days of a month (ex. GEOMEAN(r[01 dec.]:r[31 dec.])).
i don't have any problems with that part. the hard part is that i have every single trading day from jan 00 to dec 08 (2000+ days), and i do not want to manually select the ranges. also, the dates do not include weekends (trading days per year = 252) which means that i cant (a) automatically determine a range or (b) automatically associate a number of days to a month (ex. oct 08 doesn't have the same amount of days as oct 07, because of the way week-ends and holidays are arranged..)
View 5 Replies
View Related
Apr 10, 2013
I have code that pulls data from multiple sheets in a folder and throws a time stamp in the row/col. The data can have duplicates when adding to the master sheet. What I would like is to delete the duplicate row that is furthest from the NOW date.
Criteria for duplicates are based off of Col A. Time stamp is in Col G.
Highlighted rows would be deleted:
Sheet1
*
A
B
C
D
E
F
G
1
Item
*
*
*
*
*
Imported
2
112
a
b
c
d
*
Apr-5-2013 08:53:19
[Code] ..........
View 2 Replies
View Related
Aug 28, 2013
I record all jobs that come into my department. Column A contains the Job reference. Column B Contains the date that the job arrived and Column C contains the date that the job was complete.
The job can be split into several sections and can arrive on different days and the sections can be completed in different days.
I am looking to create a table that will show how long the complete job took to complete, so I am looking for the earliest date and the latest date for specific job reference numbers.
An example of my data is:
Column A Column B Column C
Ref No Date In Date Out
2013-0055 01/03/2013 25/03/2013
2013-0061 01/03/2013 03/03/2013
2013-0061 02/03/2013 20/03/2013
2013-0055 07/03/2013 28/03/2013
2013-0061 08/03/2013 19/03/2013
From the above data I am looking to create a table that will show
Ref No First Date Last Date Number of Days
2013-0055 01/03/2013 28/03/2013 28
2013-0061 01/03/2013 20/03/2013 20
I am using Excel 2000.
View 4 Replies
View Related
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
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
Feb 25, 2007
I'm using a "Large" formula with a variable for the number of cells to sum and then later divide by. However, I want the Large formula to look at the X largest values from a column three to the right, then sum the values in the original column. This wouldn't necessarily be the X largest cells in that original column. I believe this would be some sort of offset, but I'm not very good with those.
View 3 Replies
View Related
Jan 22, 2014
I have a tracking template with a column listing dates, all i want to do is find all the missing dates from that column of dates.
Example:
Column A
1-May
2-May
4-May
5-May
7-May
8-May
10-May
11-May
12-May
14-May
15-May
I want to list the missing dates from this list.
View 4 Replies
View Related
Dec 9, 2013
I have attached an excel sheet for your reference. I have particular debit values that are to be added between the dates. And Dates are also derived by formula based of payment term.
The ones I need to modify is Highlighted in Yellow. The values to be added is in "Customer Statement" and in H Column
These dates also have formula by which there are derived
-------------------------Current Ageing-------------------------
Date Range
Bucket
Amount
Percent
Start Date
End Date[code].....
I am USing =SUMIFS('Customer Statement'!$A:$A,'Customer Statement'!$H:$H,"=" & E11) but does not work.
View 9 Replies
View Related
Dec 6, 2006
I wondered if there is a possibility to make this
Range("L3").FormulaR1C1 = "=SMALL(R[-1]C[-11]:RC[-11],1)"
Range("L4").FormulaR1C1 = "=LARGE(R[-2]C[-11]:R[-1]C[-11],1)"
more simple so i can get the range for my small and large formula's variable? What i try to reach here is:
Range("L3") = smallest date In Range("A2", Range("A" & Rows.Count).End(xlUp))
Range("L4") = largest date In Range("A2", Range("A" & Rows.Count).End(xlUp))
View 6 Replies
View Related
Jul 23, 2008
I am creating a summary financial sheet in Excel 2003, Win XP. It needs to show the top 10 sales variances from a large data list, along with the associated department.
I can bring in the top 10 largest variances using Large(array,k), but because variances are either positive or negative, I'm not really showing the true top 10. To fix this I can use absolute values via Large(abs(array),k) but then I don't know how to convert the value back to its original sign (positive or negative).
If I could somehow get the cell reference that the Large(abs(array),k) formula points to, I could do something like this:
=Large(abs(array),k) * (Large Cell Reference/Large Cell Reference)
I'm afraid to use the address function because of duplicate sales variances. I often have several variances of with same value, so I might not truly be pulling the address that the large(abs(array),k) is pointing to.
View 9 Replies
View Related
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
Dec 7, 2013
I want to numerically graph in a 2D stacked column graph the following:
Training Completed on "Time"
Training Completed but "Not on Time"
Training Not Completed
The training Due date was 10/30/2013.
Book1.xlsx
View 6 Replies
View Related
Mar 3, 2014
I am trying to use excel tools to clean dirty data and compare the two cells. The information is there but tainted with additional information that is not relevant. I have tried to use Left/Right tools to capture alpha characters leading an address number with no real success. Also, when I get the data it seems to have some embedded breaks that I can't seem to get rid of that cause my tasks to error too.
View 4 Replies
View Related
Jan 17, 2013
I've been unsuccessful in trying to write a formula that retrieves a single result based on two criteria (from a large set of data on a separate worksheet). I've tried various INDEX MATCH combinations but no luck.
A
B
C
D
E
1
DATE
TEAM
PITCHERS
RESULT
2
4/1
nyy
Sabathia
???????
[Code] ........
So this is a very simplified version of my real data set which is about 20 times this size. The first worksheet is where I want to store my retrieved results (lets say D2 for example). I want to retrieve data from the second worksheet that matches two criteria (exactly) originating from my first worksheet. The two criteria to be matched from the first worksheet are, for example, A1 (sabathia) and F2 (the date 4/8). The complicated part is the desired result should be from the corresponding K/9 column in the second sheet, which in this case (based on sabathia and 4/8 criteria) is I2 (result would be 3). It's complicated since I can't just tell the formula to look down a specific K/9 column, I need to search ALL the K/9 columns in the sheet (of which there are many). Is this even possible with some sort of nested INDEX MATCH? Any possibilities outside of VBA programming, or is that the only way?
View 8 Replies
View Related
Nov 2, 2009
I am trying to use a simple formula: COUNTIF($CT$2:$CT:$430749,CT2)
I am trying to fill this down all the rows (430,749 rows). The formula behaves as its supposed to up to around row 650. After this point, all resulting values are all the same, which happens to be the same value that was returned from the original formula in C2. However, this is not correct. It's like the formula just stops working after row ~650. I tried F9 without success.
View 9 Replies
View Related
Jun 16, 2014
simplifying a formula which gathers data from about 50 worksheets from within the same work book.
The data to be gathered is in the same cell on each worksheet and is simply a number but i want the SUM of theses numbers carried forward to another worksheet. Each worksheet is named by date i.e. sheet 1 is named "16 June 2014" and sheet 2 is named "23 June 2014" and so on until "30 March 2015" (Each sheet represents one full week Monday - Sunday).
View 5 Replies
View Related
Apr 18, 2013
I have my dummy data, and I have (what I think) is how I want the data to be shown. My friend uses Google Sheets, but I prefer Excel. I am trying to convert the code because I am a stickler for excel. Typically I can convert codes some easily, but this is way beyond me.
For Column A: I want to create a list on sheet 'Setup!' based on ids!D2:D="yes". If that list has duplicate entries, I would like only the first entry to show up, but for the next entries I would like the cell to be blank. (this is important for the next step) For Column C: I want to have the corresponding dates go with the name entry. For Column D: I want to have the notes go with the corresponding date entry. (I believe I can manipulate Column C's code to do Column D myself).
I am also going to upload a data sheet, and an expected results sheet.
unique list.xlsx
View 1 Replies
View Related
Apr 2, 2014
I have the following formula
=IFERROR(INDEX('Data - CURRENT'!$B$2:$B$2500,MATCH(LARGE('Data - CURRENT'!$BA$2:$BA$2500,ROW(A1)),
'Data - CURRENT'!$BA$2:$BA$2500,0)),"")
This worked wonderfully until a duplicate crept in to Column B. Now there is the potential for many more duplicates. Column B holds an 8 digit numerical number. I am just struggling to add in a parameter that ignores any duplicates that have been added
View 14 Replies
View Related
Mar 13, 2009
I have two columns of email addresses and I want to make sure that between these two columns there are no duplicates.
View 4 Replies
View Related
Dec 20, 2007
I have excel sheets that will show a customer account number an ammount they need to pay and there full name and a date when the payment is expected, is there a formula that will find duplicates, if the same entry has been put in twice
View 14 Replies
View Related
Jan 6, 2008
I am using the following formula to rank a list of data:
=SUMPRODUCT(--($A$2:$A$140=A2),--($B$2:$B$140=B2),--(M2
View 9 Replies
View Related
Apr 3, 2014
I have a list of what we call "model codes" which are is a similar format to "DFS41FC57DD728NCWRY3"
The list could be 6000 rows but may contain hundreds of duplicates and may only contain 50 different model codes
I am looking for a formula that will populate a new column (B in the example below) with only the individual codes (unfortunately the xls example I created wont upload)
COLUMN A COLUMN B
DFS41FC57DD728N3SRY3 DFS41FC57DD728N3SRY3
DFS41FC57DD728N3SRY3 DFS41FC57DD728NCWRY3
DFS41FC57DD728N3SRY3
DFS41FC57DD728N3SRY3
DFS41FC57DD728NCWRY3
View 5 Replies
View Related
Apr 10, 2014
The attached sheet is an example of a tracker sheet that I am trying to put together. No matter how I look at this I cant get my head around how to make this work
Basically It would work fine if all of the amounts were different. But in the cases where the retailers have been offered the same level of support YTD it is causing duplicates to pull through into the formulated tab.
I attached will clear up my query.
I have highlighted in red where the duplicates are showing
PS - It does need to be formula driven
TEST Manual Bonus Tracker 2014.xlsx
View 2 Replies
View Related
Apr 10, 2014
I have a set of text in rows which includes duplicates like this:
Column A
Text 1
Text2
Text1
Text500
Text500
I then need to remove the duplicates and put them in column B. I normally use the remove duplicates button in Excel to do this but seeing as I have to do this task every day, I was hoping there would be either a formula or VBA solution which automatically does this?
View 4 Replies
View Related
Feb 14, 2008
A1 = 3
A2 = 3
A3 = 3
formula: =A1A2 gives us FALSE....
Why does =A1A2A3 give us TRUE
What is the best formula to use to make sure no cells are duplicates?
View 9 Replies
View Related
