Displaying multiple non-adjacent cells with 1 criteria

[Roel Broos]

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]

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 A22, 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 B33, then copy A33 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

 

[Harlan Grove]

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 A22, 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$1000>=$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 B33.

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

Fill X4 down as far as needed. Fill A33 down as far as needed.

 

[Roel Broos]

Works perfect ! Thanks a lot guys

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 A22, 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$1000>=$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 B33.

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

Fill X4 down as far as needed. Fill A33 down as far as needed.