|
Need formula or report to condense table, not by manual sort
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 |
Need formula or report to condense table, not by manual sort
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 |
Need formula or report to condense table, not by manual sort
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 |
Need formula or report to condense table, not by manual sort
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 - |
Need formula or report to condense table, not by manual sort
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 - |
| All times are GMT +1. The time now is 04:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com