1. willow1985

    Index Match Aggregate - find largest or 2nd largest value

    I have a formula that compares 2 columns and returns the largest value: =INDEX(AH2:AH500,MATCH(MAX(AI2:AI500),AI2:AI500,0)) I would like this formula modified to return the 2nd or even 3rd largest value instead of just the MAX but am not sure how to modify it/apply Aggregate. I am hoping...
  2. M

    Compare and count similarities 2 measures

    Hi Guys, I cant get this done and some help would be awesome. I've got 2 measures. 1st measure calculates count of ID's based on certain filters 2nd measure does the same I want to know if there are same ID's in both measures and count the similarities. I already tried to do something...
  3. H

    largest, smallest, 2nd largest & 2nd smallest

    Posted on https://www.excelforum.com/excel-general/1294641-largest-smallest-2nd-largest-and-2nd-smallest-2.html#post5221789 but could not got complete answer I need 4 formulae for: largest, smallest, 2nd largest (smaller than largest & non duplicate of largest) & 2nd smallest (larger than...
  4. smide

    Assign an index to each group identified by date

    Hello. In column A (A1:A600) I have a dates and time in format dd.mm.yyyy - hh:mm (example. 07.10.2019 - 21:00). Somehow I need to find an 'average' date for all those dates in column A (split data/dates in column A into two parts) and then to assign an index to each date (column A) according...
  5. R

    Excel - non vba code - Calculate Prize Money by Place

    Prizes are award to individual players by Flight; 5 places are paid, in the event of a tie, the prize money is distributed between the players and rounded to the nearest whole dollar amount; i.e., Player 3, 4 and 5 are tied for 2nd place – the prize award for 2nd place is $6, 3rd place is $5 and...
  6. R

    Return result for 1st, 2nd, and 3rd Row

    Hi Everyone, I'm a bit stumped on a formula I'm building. I am trying to return the close date for the 1st, 2nd, and 3rd Sales deal for all of my sales reps. I built out the following formula, which works if the sales rep has actually closed 3 deals. However, if the sales rep only closed 1...
  7. T

    Counting data

    Assume I have this column of data: a b c d e a b If I type in cell D1: =COUNTIF(A:A,A1) and copy the formula down, I get: 2 2 1 1 1 1 1 which is correct. But what I am seeking is: 1 of 2 ' because it's the 1st a of 2 1 of 2 ' because it's the 1st b of 2 1 of 1 ' because...
  8. M

    Removing #VALUE! Cells Based on Number Value of Another Cell

    Hi all, I have two columns. One is listed as such: 40/E 32 55 110/E 75/E 95/E 34/E These numbers are pulled using a formula, and whoever wrote the code for it, wasn't supposed to include the /E. The second column uses the function: =NUMBERVALUE([@[Wattage (bulb)]]) This pulls anything with...
  9. S

    VLOOKUP with multiple hits

    Hi - I'm looking to do a VLOOKUP that will pull the hit that is not blank. In column C I have the name I will lookup, and in column N the numbers I want. e.g. Dog might be listed 10 times, but if the 1st line of Dog is a blank cell, how do I let VLOOKUP know to move to the 2nd line; or if the...
  10. G

    Macros not running properly. Can anyone assist?

    Hi I have a workbook and run this macro first OK Sub Split_Extract() ActiveSheet.Name = "Extract" Range("A1").Select Cells.Find(What:="WFJ", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False...
  11. N

    Selecting cell from another sheet based on text value

    I have one sheet with the range of cells with text in them, and another sheett with the range of cells with text in them and the number value next to the every cell. I need to choose the number value from the 2nd sheet based on the text from the 1st sheet matching some parts of text from the 2nd...
  12. L

    VBA - How to invert/sort line breaks within a cell

    Guys, how can we change the line breaks order within a cell? For instance, in my cell I have the values: A1 Cell <tbody> 1st Comment 2nd Comment 3rd Comment </tbody> I want to invert to: <tbody> 3rd Comment 2nd Comment 1st Comment </tbody> For a more complex case, how to Sort based on...
  13. G

    Double Column Sorting does not sort for the 2nd level - Any help?

    Could anyone assist on this? Not sure what happened. I sort based on Column B then 2nd level Column A but it does not sort Column A https://filebin.net/ncwuhi8hv72gmlyn
  14. E

    Update 1 pivot table in a file but not the other

    How do you update data in 1 pivot table in a file without updating the data in a 2nd pivot table in the same file? When I try to change 1 it changes the other pivot table, but I want the data in the 2nd to remain the same. I thought pivot tables only updated when you Refresh the data, but...
  15. V

    Moving one workbook to another workbook

    Hi Everyone, I am utilizing the following code I found online to automatically group rows based on a "export criteria" and save them into a new workbook. This is the code: Sub ExportData() 'Declare variables Dim ArrayItem As Long Dim ws As Worksheet Dim ArrayOfUniqueValues As Variant Dim...
  16. S

    Is there a Formula that will fill data into every 2nd cell?

    Hi I am trying to take data entered from say B3:D3 and use a formula so that it will post into every 2nd cell on a different worksheet. So it would post to C7, E7, G7 etc Is this possible Thanks Sinead:)
  17. S

    Formula to Copy Data from one workbook and paste to every 2nd column in a different workbook

    Hi All I am hoping someone may be able to help me with the below I am trying to figure out a formula that will allow me to take data from 1 spreadsheet and fill into every 2nd column on another I want to copy data from lets say cell A1:C1 from one sheet and fill into cells A8, C8, E8 on...
  18. S

    VBA to remove entire row when duplicate is found

    I have two workbook with D column having unique number. How can we remove the duplicate in 2nd file based on 1st file data? Vlookup cannot work, as when i do Vlook up it picks up the data of 1st record as well on the 2nd record which should be the 2nd vlook up data.
  19. J

    Count characters in a string

    I want to count in cell B1 how many times the character ";" exists in Cell A1 In cell C1 I want to calculate which number character the 2nd ; is positioned Cell A1: yes;no;maybe;google;uk
  20. B

    how to change the numbers in a vlookup

    I have a vlookup in col B, that looks at the 2nd column in the source data (ie Jan). That works fine. But I'd like to drag this vlookup to the right so that it looks at the relevant columns. At the moment if I drag my vlookup to the right it'll keep looking at the 2nd column. I'd like the...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back