Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Empty Lines
Thanks for the help
On sheet one I have a list of items I inventory. The list is by vendor and I cannot use a sort because I would lose vendor order. Below is an example of the data I work with. I would like to draw out onto sheet two the data that has a count only. For example, below the line that has 44, 50, 101, 120 and a couple 0s. The only data I would like to show on sheet two are the rows that have 44, 50, 101 and 120. Vendor Name 627298 4.5ft Stainless Steel Inventory 44 $15.75 $693.00 627304 5.25ft Stainless Steel Inventory 50 $20.25 $1,012.50 627311 Copper Coated Inventory 101 $14.50 $1,464.50 627328 Silver Coated Inventory 120 $14.50 $1,740.00 21-0362 Swirl Bistro Table Inventory 0 $500.00 $0.00 21-0162 Swirl Dining Armchair Inventory 0 $500.00 $0.00 Another question I have is once the data is pulled over to sheet two, is there an easy process for removing the empty rows so the data shows without lines, or like the following: Vendor Name 627298 4.5ft Stainless Steel Inventory 44 $15.75 $693.00 627304 5.25ft Stainless Steel Inventory 50 $20.25 $1,012.50 627311 Copper Coated Inventory 101 $14.50 $1,464.50 627328 Silver Coated Inventory 120 $14.50 $1,740.00 Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Empty Lines
It's not clear how automated you need this process to be. My inclination:
1) copy the entire worksheet (right-click on the worksheet tab and select Move or Copy, then check Make a Copy and click OK) 2) on the copied sheet, select your entire table, then go to the menu bar and hit Data Filter Autofilter 3) click the drop-down arrow in the column that contains the quantity and select 0 4) click in the first visible row after the header, hit ctrl + shift + down-arrow to select all visible rows 5) Edit Delete Row (click OK when you get the confirmation prompt) 6) Data Filter Autofilter to turn the filter back off so you see the remaining rows. "Jim" wrote: Thanks for the help On sheet one I have a list of items I inventory. The list is by vendor and I cannot use a sort because I would lose vendor order. Below is an example of the data I work with. I would like to draw out onto sheet two the data that has a count only. For example, below the line that has 44, 50, 101, 120 and a couple 0s. The only data I would like to show on sheet two are the rows that have 44, 50, 101 and 120. Vendor Name 627298 4.5ft Stainless Steel Inventory 44 $15.75 $693.00 627304 5.25ft Stainless Steel Inventory 50 $20.25 $1,012.50 627311 Copper Coated Inventory 101 $14.50 $1,464.50 627328 Silver Coated Inventory 120 $14.50 $1,740.00 21-0362 Swirl Bistro Table Inventory 0 $500.00 $0.00 21-0162 Swirl Dining Armchair Inventory 0 $500.00 $0.00 Another question I have is once the data is pulled over to sheet two, is there an easy process for removing the empty rows so the data shows without lines, or like the following: Vendor Name 627298 4.5ft Stainless Steel Inventory 44 $15.75 $693.00 627304 5.25ft Stainless Steel Inventory 50 $20.25 $1,012.50 627311 Copper Coated Inventory 101 $14.50 $1,464.50 627328 Silver Coated Inventory 120 $14.50 $1,740.00 Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Empty Lines
One way ..
Assuming source data is in Sheet1, data from row2 down, and the key col is col F (with the numbers: 44, 50, 101, 120 .. ) In an empty col to the right, say, col K Put in K2: =IF(OR(F2="",F2=0),"",ROW()) Copy K2 down to say K10, to cover the max expected data range (Leave K1 empty) In Sheet2 ---------- Put in A2: =IF(ISERROR(SMALL(Sheet1!$K:$K,ROW(A1))),"", INDEX(Sheet1!A:A, MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0)) ) Copy A2 across to H2, fill down to H10 (cover the same range as was done in col K in Sheet1) Sheet2 will return only the lines w/o zeros in col F in Sheet1, with all lines neatly bunched at the top (both objectives achieved!) Adapt to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Jim" wrote in message ... Thanks for the help On sheet one I have a list of items I inventory. The list is by vendor and I cannot use a sort because I would lose vendor order. Below is an example of the data I work with. I would like to draw out onto sheet two the data that has a count only. For example, below the line that has 44, 50, 101, 120 and a couple 0's. The only data I would like to show on sheet two are the rows that have 44, 50, 101 and 120. Vendor Name 627298 4.5ft Stainless Steel Inventory 44 $15.75 $693.00 627304 5.25ft Stainless Steel Inventory 50 $20.25 $1,012.50 627311 Copper Coated Inventory 101 $14.50 $1,464.50 627328 Silver Coated Inventory 120 $14.50 $1,740.00 21-0362 Swirl Bistro Table Inventory 0 $500.00 $0.00 21-0162 Swirl Dining Armchair Inventory 0 $500.00 $0.00 Another question I have is once the data is pulled over to sheet two, is there an easy process for removing the empty rows so the data shows without lines, or like the following: Vendor Name 627298 4.5ft Stainless Steel Inventory 44 $15.75 $693.00 627304 5.25ft Stainless Steel Inventory 50 $20.25 $1,012.50 627311 Copper Coated Inventory 101 $14.50 $1,464.50 627328 Silver Coated Inventory 120 $14.50 $1,740.00 Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Lines in Excel | Excel Discussion (Misc queries) | |||
No blank lines in text files | Excel Discussion (Misc queries) | |||
Missing lines in chart w/x-axis with months 1-24... | Charts and Charting in Excel | |||
In Bar Chart, can we display both figures and their respective %a. | Excel Discussion (Misc queries) | |||
Empty Cells, Spaces, Cond Format? | Excel Discussion (Misc queries) |