Hello everyone,

I am a bit stuck on this problem and I don't know if this is possible to do
in Excel 2007...

I have multiple worksheets in a workbook. first worksheet is raw data,
second is a statistics worksheet.
On sheet 1 I have the following:

Column D: Vendor Name
Column F: Order value
Column AA: Month number (as result of a =MONTH function)
Column AB: Year number (as a result of =YEAR function)
Column AC: shows a "1" if order value is bigger than 50.000 (as a result of
=IF function)

What I want do do on sheet 2 is display all orders bigger than 50k including
Vendor name and amount , example:

"Vendor Name" "Value" "Month" "Year"
"Vendor Name" "Value" "Month" "Year"
"Vendor Name" "Value" "Month" "Year"
"Vendor Name" "Value" "Month" "Year"
etc.

The number of orders varies over time offcourse. I do not want to use a
pivot table, as more people have to use this sheet and are not up to using
pivot tables....

Does anyone has a suggestion ?
Thanks in advance!

Roel.

Roel,

On sheet 2 in cell A1 to D1, enter 4, 6, 27, 28 (These are the column numbers of the data you want
to "extract" Then in E1, enter 50000.

In cell A2:D2, enter your headings "Vendor", "Value", "Month", "Year"
In cell A3, array enter (enter using Ctrl-Shift-Enter)

=INDIRECT("'Data'!" & ADDRESS(LARGE((Data!$F$2:$F$1000=$E$1)
*ROW(Data!$D$2:$D$1000),COUNTIF(Data!F$2:$F$1000," =" & $E$1) +ROW($A$3)-ROW()),A$1))
(Change Data to the actual sheet name of what you describe as sheet 1, in all four places.... And
change the three instances of 1000 to the actual last row number of your data sheet or, at least, to
a larger number.)

If you have entered this correctly, Excel will enclose the formula in { } - DO NOT type the { }
yourself....

Finally, copy A3 to B3:D3, then copy A3:D3 down for as many rows as you need: you will get error
values when you have extracted all the data.

HTH,
Bernie
MS Excel MVP


"Roel Broos" <Roel wrote in message
...
Hello everyone,

I am a bit stuck on this problem and I don't know if this is possible to do
in Excel 2007...

I have multiple worksheets in a workbook. first worksheet is raw data,
second is a statistics worksheet.
On sheet 1 I have the following:

Column D: Vendor Name
Column F: Order value
Column AA: Month number (as result of a =MONTH function)
Column AB: Year number (as a result of =YEAR function)
Column AC: shows a "1" if order value is bigger than 50.000 (as a result of
=IF function)

What I want do do on sheet 2 is display all orders bigger than 50k including
Vendor name and amount , example:

"Vendor Name" "Value" "Month" "Year"
"Vendor Name" "Value" "Month" "Year"
"Vendor Name" "Value" "Month" "Year"
"Vendor Name" "Value" "Month" "Year"
etc.

The number of orders varies over time offcourse. I do not want to use a
pivot table, as more people have to use this sheet and are not up to using
pivot tables....

Does anyone has a suggestion ?
Thanks in advance!

Roel.

"Bernie Deitrick" <deitbe @ consumer dot org wrote...
On sheet 2 in cell A1 to D1, enter 4, 6, 27, 28 *(These are the column numbers of the data you want
to "extract" *Then in E1, enter 50000.

In cell A2:D2, enter your headings "Vendor", "Value", "Month", "Year"
In cell A3, array enter (enter using Ctrl-Shift-Enter)

=INDIRECT("'Data'!" & ADDRESS(LARGE((Data!$F$2:$F$1000=$E$1)
*ROW(Data!$D$2:$D$1000),COUNTIF(Data!F$2:$F$1000, "=" & $E$1) +ROW($A$3)-ROW()),A$1))
....

Where to start?!

The ever popular but unnecessary and inefficient INDIRECT(ADDRESS
(...)).

The pointless use of LARGE(.,constant-ROW()) instead of SMALL(.,ROWS
(.)).


Avoid volatile functions. Strive for efficiency. Try

A3 [array formula]:
=INDEX(Data!$A$1:$AB$1000,SMALL(IF(Data!$F$2:$F$10 00=$E$1,ROW(Data!$F
$2:$F$1000)),ROWS(A$3:A3)),A$1)


More efficient still would be using the OP's column AC and an extra
supporting formula for each result record. Using column X for the
supporting calculations,

X3:
=MATCH(1,Data!$AC$2:$AC$1000,0)

A3:
=IF(COUNT($X3),INDEX(Data!$A$2:$AB$1000,$X3,A$1)," ")

Fill A3 right into B3:D3.

X4:
=MATCH(1,INDEX(Data!$AC$2:$AC$1000,X3+1):Data!$AC$ 1000,0)+X3

Fill X4 down as far as needed. Fill A3:D3 down as far as needed.

Works perfect ! Thanks a lot guys :)

"Harlan Grove" wrote:

"Bernie Deitrick" <deitbe @ consumer dot org wrote...
On sheet 2 in cell A1 to D1, enter 4, 6, 27, 28 (These are the column numbers of the data you want
to "extract" Then in E1, enter 50000.

In cell A2:D2, enter your headings "Vendor", "Value", "Month", "Year"
In cell A3, array enter (enter using Ctrl-Shift-Enter)

=INDIRECT("'Data'!" & ADDRESS(LARGE((Data!$F$2:$F$1000=$E$1)
*ROW(Data!$D$2:$D$1000),COUNTIF(Data!F$2:$F$1000, "=" & $E$1) +ROW($A$3)-ROW()),A$1))
....

Where to start?!

The ever popular but unnecessary and inefficient INDIRECT(ADDRESS
(...)).

The pointless use of LARGE(.,constant-ROW()) instead of SMALL(.,ROWS
(.)).


Avoid volatile functions. Strive for efficiency. Try

A3 [array formula]:
=INDEX(Data!$A$1:$AB$1000,SMALL(IF(Data!$F$2:$F$10 00=$E$1,ROW(Data!$F
$2:$F$1000)),ROWS(A$3:A3)),A$1)


More efficient still would be using the OP's column AC and an extra
supporting formula for each result record. Using column X for the
supporting calculations,

X3:
=MATCH(1,Data!$AC$2:$AC$1000,0)

A3:
=IF(COUNT($X3),INDEX(Data!$A$2:$AB$1000,$X3,A$1)," ")

Fill A3 right into B3:D3.

X4:
=MATCH(1,INDEX(Data!$AC$2:$AC$1000,X3+1):Data!$AC$ 1000,0)+X3

Fill X4 down as far as needed. Fill A3:D3 down as far as needed.