I am trying to find a formula or other way (other than manually sort) to
select from the table below, just the list of items that have a quantity.
Basically create a table in another sheet using the data below but
€œcondensed€� without adding the items that have no quantity values. The table
is lengthy and I want to create a formula or report. I know sorting would
give me the view I need want a formula to do automatically and not show any
items that are €œzero€�.
Thanks.

A B
1 Item description Item Qty
2 AirConditioner Large
3 AirConditioner Small
4 Baby Carriage 1
5 Baby Crib
6 Base Buffet 2
7 Bathinette
8 BBQ Grill Large
9 BBQ Grill Small 1
10 Bed, Bunk (Set of 2)
11 Bed, King
12 Bed, Queen 1
13 Bed, Rollaway
14 Bed, Single/Hollywood
15 Bed, Standard Double
16 Bed, Waterbed Base
17 Bed, Youth
18 Bicycle


Desired result:
1 Baby Carriage
2 Base Buffet
1 BBQ Grill Small
1 Bed, Queen

Look at Data - Filter - Auto filter.
--
HTH...

Jim Thomlinson


"Copart" wrote:

I am trying to find a formula or other way (other than manually sort) to
select from the table below, just the list of items that have a quantity.
Basically create a table in another sheet using the data below but
€œcondensed€� without adding the items that have no quantity values. The table
is lengthy and I want to create a formula or report. I know sorting would
give me the view I need want a formula to do automatically and not show any
items that are €œzero€�.
Thanks.

A B
1 Item description Item Qty
2 AirConditioner Large
3 AirConditioner Small
4 Baby Carriage 1
5 Baby Crib
6 Base Buffet 2
7 Bathinette
8 BBQ Grill Large
9 BBQ Grill Small 1
10 Bed, Bunk (Set of 2)
11 Bed, King
12 Bed, Queen 1
13 Bed, Rollaway
14 Bed, Single/Hollywood
15 Bed, Standard Double
16 Bed, Waterbed Base
17 Bed, Youth
18 Bicycle


Desired result:
1 Baby Carriage
2 Base Buffet
1 BBQ Grill Small
1 Bed, Queen

Thanks for the reply but a filter will not work for me on this.
Since the data is in a separate sheet I need a way to pull just the data
with quantities into a different sheet without filtering or sorting.

"Jim Thomlinson" wrote:

Look at Data - Filter - Auto filter.
--
HTH...

Jim Thomlinson


"Copart" wrote:

I am trying to find a formula or other way (other than manually sort) to
select from the table below, just the list of items that have a quantity.
Basically create a table in another sheet using the data below but
€œcondensed€� without adding the items that have no quantity values. The table
is lengthy and I want to create a formula or report. I know sorting would
give me the view I need want a formula to do automatically and not show any
items that are €œzero€�.
Thanks.

A B
1 Item description Item Qty
2 AirConditioner Large
3 AirConditioner Small
4 Baby Carriage 1
5 Baby Crib
6 Base Buffet 2
7 Bathinette
8 BBQ Grill Large
9 BBQ Grill Small 1
10 Bed, Bunk (Set of 2)
11 Bed, King
12 Bed, Queen 1
13 Bed, Rollaway
14 Bed, Single/Hollywood
15 Bed, Standard Double
16 Bed, Waterbed Base
17 Bed, Youth
18 Bicycle


Desired result:
1 Baby Carriage
2 Base Buffet
1 BBQ Grill Small
1 Bed, Queen

Put this formula in C2 of your data sheet (assumed to be sheet1):

=IF(B2="","",COUNT(B$2:B2))

then copy it down as far as your data extends.

Then put these two formulae in sheet2:

A1: =IF(ISNA(MATCH(ROW($A1),Sheet1!$C:$C,0)),"",INDEX( Sheet1!
B:B,MATCH(ROW($A1),Sheet1!$C:$C,0)))

B1: =IF(A1="","",INDEX(Sheet1!A:A,MATCH(ROW($A1),Sheet 1!$C:$C,0)))

Then copy these down as far as you need to.

Hope this helps.

Pete

On Aug 12, 8:34*pm, Copart wrote:
Thanks for the reply but a filter will not work for me on this.
Since the data is in a separate sheet I need a way to pull just the data
with quantities into a different sheet without filtering or sorting.



"Jim Thomlinson" wrote:
Look at Data - Filter - Auto filter.
--
HTH...

Jim Thomlinson

"Copart" wrote:

I am trying to find a formula or other way (other than manually sort) to
select from the table below, just the list of items that have a quantity.
Basically create a table in another sheet using the data below but
“condensed” without adding the items that have no quantity values.. The table
is lengthy and I want to create a formula or report. I know sorting would
give me the view I need want a formula to do automatically and not show any
items that are “zero”. *
Thanks.

* * * * * * * *A * * * * * * * * * * * * * B
*1 * *Item description * * * *Item Qty
*2 * *AirConditioner Large * *
*3 * *AirConditioner Small * *
*4 * *Baby Carriage * * * *1
*5 * *Baby Crib * * *
*6 * *Base Buffet * * * * *2
*7 * *Bathinette * * *
*8 * *BBQ Grill Large
*9 * *BBQ Grill Small * * *1
*10 * Bed, Bunk (Set of 2) * *
*11 * Bed, King * * *
*12 * Bed, Queen * * * * * 1
*13 * Bed, Rollaway *
*14 * Bed, Single/Hollywood *
*15 * Bed, Standard Double * *
*16 * Bed, Waterbed Base * * *
*17 * Bed, Youth * * *
*18 * Bicycle

* * * Desired result:
* * * 1 * * * Baby Carriage
* * * 2 * * * Base Buffet
* * * 1 * * * BBQ Grill Small
* * * 1 * * * Bed, Queen- Hide quoted text -

- Show quoted text -

Worked like a charm!
Thank you!

"Pete_UK" wrote:

Put this formula in C2 of your data sheet (assumed to be sheet1):

=IF(B2="","",COUNT(B$2:B2))

then copy it down as far as your data extends.

Then put these two formulae in sheet2:

A1: =IF(ISNA(MATCH(ROW($A1),Sheet1!$C:$C,0)),"",INDEX( Sheet1!
B:B,MATCH(ROW($A1),Sheet1!$C:$C,0)))

B1: =IF(A1="","",INDEX(Sheet1!A:A,MATCH(ROW($A1),Sheet 1!$C:$C,0)))

Then copy these down as far as you need to.

Hope this helps.

Pete

On Aug 12, 8:34 pm, Copart wrote:
Thanks for the reply but a filter will not work for me on this.
Since the data is in a separate sheet I need a way to pull just the data
with quantities into a different sheet without filtering or sorting.



"Jim Thomlinson" wrote:
Look at Data - Filter - Auto filter.
--
HTH...

Jim Thomlinson

"Copart" wrote:

I am trying to find a formula or other way (other than manually sort) to
select from the table below, just the list of items that have a quantity.
Basically create a table in another sheet using the data below but
€œcondensed€� without adding the items that have no quantity values.. The table
is lengthy and I want to create a formula or report. I know sorting would
give me the view I need want a formula to do automatically and not show any
items that are €œzero€�.
Thanks.

A B
1 Item description Item Qty
2 AirConditioner Large
3 AirConditioner Small
4 Baby Carriage 1
5 Baby Crib
6 Base Buffet 2
7 Bathinette
8 BBQ Grill Large
9 BBQ Grill Small 1
10 Bed, Bunk (Set of 2)
11 Bed, King
12 Bed, Queen 1
13 Bed, Rollaway
14 Bed, Single/Hollywood
15 Bed, Standard Double
16 Bed, Waterbed Base
17 Bed, Youth
18 Bicycle

Desired result:
1 Baby Carriage
2 Base Buffet
1 BBQ Grill Small
1 Bed, Queen- Hide quoted text -

- Show quoted text -