0

I'm looking to get maximum and minimum values of numerical values for ticket logs

This is over a historical excel sheet and I've been instructed not to edit the order or formatting in any way.

In a new sheet I have created a list of Ticket Type names and time + min and max In the original sheet (among others) is the Ticket Type name "time to complete" in days (1.1, 2.5, 30.0 etc.)

Due to the nature of the data, I cannot list it here

There are over 100,000 rows, not in a useful order (for me)

Sheet 1 Ticket Name Time to Complete Ticket Name 2 1.2 Ticket Name 4 5.6 Ticket Name 2 62.6 Ticket Name 16 38.1 .... (x100,000)

What I would like in the second sheet is something like this

Sheet 2 Ticket Name Count Min Max Diff Ticket Name 1 135 0.2 12.0 11.8 Ticket Name 26 44 11.8 182.0 170.2 .... Ticket Name 26 22 1.6 6.3 4.7

I get the idea of using a VLOOKUP, but that (as far as I'm aware) will only get me the first value - every one in a list of over 1,000, that I can also "click and drag" down for every Ticket Name

Any help will be greatly appreciated!

Improve this question
3
  • 2
    use a pivot table. Commented Aug 8, 2018 at 13:28
  • 1
    This is of course excel and a thousend ways will get you to rome, so if you want vlookup because for some reason pivot table does not work for you, do it in a makro where you create columns with vlookup which then copys only the values into new columns and then deletes the vlookup columns. So ppl can look without excel dieing because of too many vlookups but you can still refresh it. You can use the makro recorder for this ;) Commented Aug 8, 2018 at 13:35
  • Thank you both. A Pivot wasn't requested, so I never really thought of it - great solution, but the formula should help if I need to live edit data etc Commented Aug 8, 2018 at 14:13

2 Answers 2

Reset to default
1

Count: =countif(sheet1!A:A, A2)

For Office 2016 and newer

Min: =minifs(sheet1!B:B, sheet1!A:A, A2)

Max: =maxifs(sheet1!B:B, sheet1!A:A, A2)

For those older than Office 2016, use Array Formula

Min: =min(if(sheet1!A:A=A2, sheet1!B:B, "")), then press ctrl+shift+enter

Max: =max(if(sheet1!A:A=A2, sheet1!B:B, "")), then press ctrl+shift+enter

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

As commented before, you might want to look into a pivot table.

However, if your way to go is using formulas then try this:

1). Put this formula in Sheet2 cell C2:

=MIN(IF(Sheet1!A:A=A2,Sheet1!B:B))

Confirm with Ctrl+Shift+Enter, and drag your formula down.

2). Put this formula in Sheet2 cell D2:

=MAX(IF(Sheet1!A:A=A2,Sheet1!B:B))

Confirm with Ctrl+Shift+Enter, and drag your formula down.

Improve this answer

1 Comment

Thank you! Marking the other as answer, just because it has the minifs

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.