The Seemingly Impossible

[Danny Lewis]

Hello all

I posted yesterday, when Leo was very helpful but I'd like to try and post
it again and see if there are any geniuses out there...

I have a table, with the following columns:

Incident Number, Date, Incident Description, Minutes 1, Minutes 2, Function,
Area (and a few other irrelevant columns).

This table has a few thousand records in it, with each column varying data.
There are about 6 different functions, and 30 different areas.

Say for example one function was FINANCE, and the area was CREDIT CONTROL,
is there a way I could produce the same table on another spreadsheet for the
top 5 incidents for these two variables, descending by Minutes 1?

e.g. on another worksheet

Incident Number, Date, Incident Description, Minutes 1, Minutes 2, Function,
Area
1 01/01/01 ikgjnsgosgn 78 120 Finance Credit Control
2 23/09/00 jsdgofnyof 65 103 Finance Credit Control

and so on????

Thanks

Danny

 

[Arvi Laanemets]

Hi

I think the easiest way will be an ODBC query. Define your table as named
range
MyTable=MySheet!$A$1:$G$10000
(row 1 must contain headers), and save the workbook.

Now, on empty worksheet, create an ODBC query with SQL-string like this (on
fly)

SELECT TOP 5 MyTable.IncidentNumber, MyTable.Date,
MyDate.IncidentDescription, MyDate.Minutes1, MyDate.Minutes2 FROM MyTable
MyTable WHERE MyTable.IncidentNumber IS NOT NULL AND
MyTable.Function="FINANCE" AND MyTable.Area="CREDIT CONTROL" ORDER BY
MyTable.Minutes1 DESC

In query properties, set it to be refreshed on open (, and maybe after some
time interval too). Set data to be overwritten on refresh, and unused rows
to be cleared. Additionally, you can always refresh the query manually too.

 

[Max]

Here's one play using non-array formulas ..

Assume source table in sheet: X
cols A to G, headers in A1:G1, data from row2 down
col F = Function, col G = Area, col D = Minutes 1

In a new sheet,

Paste the same headers in A1:G1
Let's reserve I1:I2 for input of the Function & Area
Input in I1:I2, eg: Finance, Credit Control

Put in H2: =IF(AND(X!F2=$I$1,X!G2=$I$2),X!D2-ROW()/10^10,"")
Copy H2 down to say, H2000,
to cover the max expected extent of data within the source table in X
(Leave H1 empty)

Then place in A2:
=IF(ROW(A1)>COUNT($H:$H),"",INDEX(X!A:A,MATCH(LARGE($H:$H,ROW(A1)),$H:$H,0)))

Copy A2 to G2, fill down as far as required to extract the top N
Eg: If top 5, fill down say 10 rows? to G11
to cater for the possibility of any ties in the "Minutes 1"
Tied lines if any, will appear in the same relative order that they appear
in X
(Col H's criteria caters for ties in "Minutes 1", with an arb tiebreaker for
a descending sort)

Format col B as date to taste
Cols A to G will return the required results

 

[Max]

Danny,

Dang! Was sure that earlier response would have been one way to make the
"impossible" here possible. Perhaps you tried to contact me telepathically,
but I'm afraid if so, your signals were lost & garbled in cyberspace noise.
Drop a line or two here in reply. This is supposed to be a discussion group,
not a dartboard. Don't just issue a challenge and fade away.

 

[Harlan Grove]

Max wrote...

Here's one play using non-array formulas ..

Why? This is easier using array formulas.

Assume source table in sheet: X
cols A to G, headers in A1:G1, data from row2 down
col F = Function, col G = Area, col D = Minutes 1

In a new sheet,

Paste the same headers in A1:G1

So far, so good.

Let's reserve I1:I2 for input of the Function & Area

....

Why? Why not just use the F2 and G2 cells?

Put in H2: =IF(AND(X!F2=$I$1,X!G2=$I$2),X!D2-ROW()/10^10,"")

....

Not necessary to use col H for ancillary calculations.

If the incident numbers in X!A:A are distinct,

A2:
=LOOKUP(2,1/(X!$D$2:$D$101-ROW(X!$D$2:$D$101)/2^20
=LARGE((X!$F$2:$F$101=$F2)*(X!$G$2:$G$101=$G2)
*(X!$D$2:$D$101-ROW(X!$D$2:$D$101)/2^20),ROWS(A$2:A2))),X!A$2:A$101)

B2:
=VLOOKUP($A2,X!$A$2:$E$101,COLUMNS($A2:B2),0)

Fill B2 right into C2:E2. F2 would hold the entry for Function and G2
the entry for Area. Fill A2:E2 down into A3:E3. Enter the formulas

F3:
=F$2

G3:
=G$2

Fill A3:G3 down into A4:G6. None of these are array formulas.

This doesn't handle the possibility that there could be fewer than 5
incidents for a particular function and area. If that's a possibility,
then the col A formu

 

[Harlan Grove]

Max wrote...

Here's one play using non-array formulas ..

Why? This is easier using array formulas.

Assume source table in sheet: X
cols A to G, headers in A1:G1, data from row2 down
col F = Function, col G = Area, col D = Minutes 1

In a new sheet,

Paste the same headers in A1:G1

So far, so good.

Let's reserve I1:I2 for input of the Function & Area

....

Why? Why not just use the F2 and G2 cells?

Put in H2: =IF(AND(X!F2=$I$1,X!G2=$I$2),X!D2-ROW()/10^10,"")

....

Not necessary to use col H for ancillary calculations.

If the incident numbers in X!A:A are distinct,

A2:
=LOOKUP(2,1/(X!$D$2:$D$101-ROW(X!$D$2:$D$101)/2^20
=LARGE((X!$F$2:$F$101=$F2)*(X!$G$2:$G$101=$G2)
*(X!$D$2:$D$101-ROW(X!$D$2:$D$101)/2^20),ROWS(A$2:A2))),X!A$2:A$101)

B2:
=VLOOKUP($A2,X!$A$2:$E$101,COLUMNS($A2:B2),0)

Fill B2 right into C2:E2. F2 would hold the entry for Function and G2
the entry for Area. Fill A2:E2 down into A3:E3. Enter the formulas

F3:
=F$2

Fill F2 right into G3. Fill A3:G3 down into A4:G6. None of these are
array formulas.

This doesn't handle the possibility that there could be fewer than 5
incidents for a particular function and area. If that's a possibility,
then the col A formulas would need to be changed to

A2:
=IF(SUMPRODUCT((X!$F$2:$F$101=$F2)*(X!$G$2:$G$101=$G2))>=ROWS(A$2:A2),
LOOKUP(2,1/(X!$D$2:$D$101-ROW(X!$D$2:$D$101)/2^20
=LARGE((X!$F$2:$F$101=$F2)*(X!$G$2:$G$101=$G2)
*(X!$D$2:$D$101-ROW(X!$D$2:$D$101)/2^20),ROWS(A$2:A2))),X!A$2:A$101),"")

And the cols B-E formulas to

B2:
=IF(N($A2),VLOOKUP($A2,X!$A$2:$E$101,COLUMNS($A2:B2),0),"")

And the cols F-G formulas to

F2:
=IF(N($A2),F$2,"")