Duplicates Pulling Through From Index Formula?
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
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
May 25, 2006
I am trying to retrieve the top 10 or so data from a large list from mutliple columns and if there is a value the same I will recieve a #N/A error by using Index and Match. Is there anotherway of sorting the most rated without using Autofilter which is not pratical considering the setup of the spreadsheet. I have attached and example of what happens with duplicate values from a file I found on this forum and adapted.
View 7 Replies
View Related
Jun 11, 2009
This formula returns the count of all rows in Sheet1 that have Donation in the first 1000 rows of Column G AND Student in Column T.
=SUM((Sheet1!$G$1:$G$1000"Donation")*(Sheet1!$T$1:$T$1000="Student Time"))
I need to do the exact same thing but instead of looking for the word Student as the value of the cell, I need to count the cells that have the words "Student Time" somewhere in the cells in Column T. These cells also contain lots of other text including sometimes carriage returns.
View 9 Replies
View Related
Jun 24, 2006
I am working with a tab delimited file, and am needing to have a column of its own that will pull the first letter of another column. i.e. in A will be the title of the book, and in column C I need to have the first letter of the title of the book appear for other uses.
View 2 Replies
View Related
Nov 21, 2006
Im currently working to speed up my monthly reporting process. Each month I pull data from the ledger by account & office. Below is an example of what the format of the ledger looks like.
My next step is to report the amounts by account number for each office. At the moment I do this manually, which takes a very long time. My goal is to be able to paste the ledger data on a worksheet and have my report populate automatically.
In regards to the account numbers from the ledger, we summarize the accounts using the first 3 digits of the account. For example, account number "1113454" would be considered a "111" account. Account number 3335454 would be considered a "333" account.
************************************************************************>Microsoft Excel - 3Q Global Variance Analysis.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB16C16D16E16F16B22E22B23E23B24E24B25E25B26E26=
ABCDEF1Ledger2******3*Office*1Office*2Office*3Office*4Office*54*SEP-06SEP-06SEP-06SEP-06SEP-065Account*Number*****6111234127.00766.004,446.007,865.005,509.00711135433,000.0034,566.0099,433.00100.00471,612.76811167645,677.009,898.002,212.0034.00877.00911190932,445.00466.0043,435.00343.005,665.00101118881,876.00433.006,566.00900.0020,398.4411113454121.0032.00900.0075,329.3222.001211345517,678.005,443.00434.00233.0064,556.00133331115,658.00875.00222.0032.00654.001433350090,932.007,634.00122,246.002,134.0055.0015333009123.00543.00990.009,005.00888.0016Sum227,637.0060,656.00280,884.0095,975.32570,237.2017******18My*Report*19******20111*Balance*113*Balance*21OfficeBalance*OfficeBalance*22Office*1113,125.00*Office*117,799.00*23Office*246,129.00*Office*25,475.00*24Office*3156,092.00*Office*31,334.00*25Office*49,242.00*Office*475,562.32*26Office*5504,062.20*Office*564,578.00*27******Ledger*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Aug 21, 2009
I'm looking for is to repeat this formula within the cell, so that it can pull the information of not only the first match, but all matches proceeding it. Right now it only pulls the first match it finds, but I would like it to add every time a row matches the criteria of the formula.
=(LOOKUP(2,1/(('Paste SFHS'!$A$2:$A$9=A2)*('Paste SFHS'!$B$2:$B$9="08-Wave/Stu. Goals")),'Paste SFHS'!$C$2:$C$9))
View 5 Replies
View Related
Mar 27, 2009
Attached you will find the spreadshhet in question. If you click on the click here button input the following info:
8.0
3508949
80
0
0
25
the sheet will auto populate all numbers. this part is working properly. MY issue is if you look at the Bad Debt number, the correct response should in cell f21 should be $636.46 because in order to hit the next level of pay, you must at least hit $3,424,082.00. I can not figure out how to get the correct formula that will put the right pay out. Basically in order to hit a certain level of payout you must hit that next number, even if you are a $1 higher you can not get the pay out. So for example if your bad debt is $3,424,083 you would be paid out at 100%, not 120%.
View 3 Replies
View Related
Jun 3, 2014
I would like the following columns to show on a new worksheet if column Z and AA (which show a date and time) are the same as todays date, the columns of info I would like it to pull through are Column C,D, Z, AA. The row numbers could be different daily? is this possible?
View 7 Replies
View Related
Jul 25, 2014
I'm trying to pull the earliest and lasted times out of a list of time stamps. Say I have a column full of clock in and clock out times and wanted to find the first clock in time and the last clock out time (just an example). My lookup_value would be a Store number. I can't post it here due to legality reasons, but I have a column a store numbers that will be my lookup values and I need to find the earliest and lastest time in a list of data on another tab.
View 9 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
Jan 13, 2014
I have the following data and i want to get the highest value over average using if, i wort this formula
=IF(INDEX(D3:D12,ROWS(D3:D3)>AVERAGE(D3:D12),"Greater","Lower"),
But it doesn't work.
View 4 Replies
View Related
Jan 10, 2008
The first sheet is called Sites and the second sheet is called Call Log-Jan-08
On the Call Log in cell A3 we put the division # and then we want the information to automatically fill out for the cells B3, C3 & D3. We want this information to come from the Sites list. In the sites list the information starts in A2 thru K2 (A1 thru K1 is the header info) and goes thru A645 thru K645.
I can't figure out how to get my Index formula to work.
View 9 Replies
View Related
Feb 18, 2010
in B2 want to have value of cell T1
in B6 want to have value of cell T2
in B11 want to have value of cell T3
in B16 want to have value of cell T4 etc etc..
so actually in every 4th cell in B row need to have formula which will show data from T row as in example above!
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
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
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
May 26, 2007
I know it can be done cause I've used it before but I can't seem to make it work this time. I want to use the conditional formatting to compare two ranges of cells within one worksheet I know it's something like =match(A2:C12) the two ranges are sitting one on top of the other. . .
View 4 Replies
View Related
Dec 2, 2008
I am trying a simple Index or Array formula and have been trying all day to get this work to no avail. The data is freight costs for shipping different size containers (20' and 40') to different ports (Lyndhurst, Port Coquitlam and Seattle).
I will have the port name plugged into one cell and the container size plugged into another cell and want to pull the corresponding freight cost out into another cell. The red text in the file are the data that I will plug in. The box below is where I would like the result.
View 5 Replies
View Related
Jun 18, 2009
Current I am using two spreadsheets for similar press and am combining the two. What I need help with this time is currently in cell c9 the formula is =ROUNDDOWN(17.7/C7,0) or =ROUNDDOWN(39.3/C7,0) depending on which press I am using. The presses are shown in cells d34 - q34. Indigo =17.7 gilboa = 39.3. I have added these numbers to a table in the scales tab a24:e26. Is there a way to write a formula in cell c9 on the input tab to combine the 2 above formulas and insert the correct 17.7 or 39.3 when required?
View 14 Replies
View Related
Jul 24, 2009
I'm trying to construct a formula which contains an =IF and what I think needs to be INDEX, but cannot be 100% certain as I've never done.
I can't post a sample of the workbook due to company policy, but here's what I'm trying to do:
In Cell b2
View 6 Replies
View Related
Aug 12, 2009
i'm trying to search throgh multiple sheets using an INDEX formula.
=INDEX('12.31.09:11.15.09'!$AF$14:$AG$75,MATCH(E$2,'12.31.09:11.15.09'!$AC$14:$AC$75,0),MATCH($B61,' 12.31.09:11.15.09'!$AF$12:$AG$12,0))
In short, these are timesheets; in a separate sheet i'm using the above formula to find how many hrs were worked on all the other sheets for a given job# within a given date range. (in the above example, i'm only searching in timesheets between 11/15 and 12/31, but I will ultimately change that to include the entire year '12.31.09:01.15.09'). E2 is a referenced job number; B61 is a referenced date.
I'm getting a !VALUE error.
Can a 3d reference work inside an INDEX formula?
View 10 Replies
View Related
Oct 15, 2009
Guys im stuck on the fact of how to pull the correct information through for a named person.as from the example if i place say steve in the named box i would like all of steves scores to appear in the designated boxes.and so on.
View 11 Replies
View Related
Nov 22, 2007
Need to place an Index(Match) formula in Sheet 2 B2 (as for example). The lookup values in Sheet 2 to be matched with Sheet 1 will be A2, B1 and J1.
In Sheet 1 (Edit) I'm only interest in the subtotal and Sheet 2 J1 represents the subtotal for the week that ends with J1 date.
The Index should be the whole Sheet 1 (if possible) for further expansion (and I do have way more than what is presented below).
Edit
HTML removed by RAM and replaced with a new HTML further down.
View 9 Replies
View Related
May 17, 2006
I have attached a formula I have used to extract a value from another sheet (I have also attached where the formula is mean't to extract the value from) . My problem is that this formula is not returning what it should?
The value that should be returned is "416"
View 9 Replies
View Related
