If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Ye olde blank cell question.. maybe
I assume this question has been asked a dozen times but *I* haven't
quite seen it asked yet. I have seen similar questions but they are not the same.. at least I don't think they're the same as mine.. but anyways... I have a couple columns (say A and B) which contain data Lot# in A and Footage in B. Lot# are alphanumeric i.e. 4C02304. Now these columns in this example have 20 rows, but not every row has an entry, many are blank inbetween the Lot#. On the same sheet or another I need to make a new list with the Lot# and Footage order intact but excluding all blank cells. Our IT can't/won't "enable" VB macros or something to that extent (???) So I came up with this so far, and though it works beautifully, it doesn't 'feel' professional. It feels like I am trying to kill a gnat with a sledge hammer or an 18-wheeler ;^) Again column A has Lot#'s. B has the lengths. So for this example: A B ---------+------ 4S03801 12000 4S03703 48000 4S03912 39000 4S03110 58350 3S33001 58350 3S00101 58350 3S00606 37000 4S00707 49000 --------------------- now the sledge hammer solution bit goes like this: I create a numbered list 1-20 in column F where F1=01, F2=02.... F20=20. G1=COUNTA($A$1:A1) (Copied and pasted to G20) H1=MATCH(F1,$G$1:$G$20,0) I1=INDEX($A$1:$A$20,H1,1) (or these two combined to make) J1=INDEX($A$1:$A$20,MATCH(F1,$G$1:$G$20,0),1) Again these are copied and pasted from row 1 to row 20 this result in the following output: F G H I J --+-----+----+----------+---------- 1 1 1 4S03801 4S03801 2 2 2 4S03703 4S03703 3 2 5 4S03912 4S03912 4 2 8 4S03110 4S03110 5 3 10 3S33001 3S33001 6 3 13 3S00101 3S00101 7 3 17 3S00606 3S00606 8 4 20 4S00707 4S00707 9 4 #N/A #N/A #N/A 10 5 #N/A #N/A #N/A 11 5 #N/A #N/A #N/A 12 5 #N/A #N/A #N/A 13 6 #N/A #N/A #N/A 14 6 #N/A #N/A #N/A 15 6 #N/A #N/A #N/A 16 6 #N/A #N/A #N/A 17 7 #N/A #N/A #N/A 18 7 #N/A #N/A #N/A 19 7 #N/A #N/A #N/A 20 8 #N/A #N/A #N/A So it works and all, great. But is there a better way? Question 2: Still using the above, how could I eliminate column F and the function in H use these index's without me having to type each one. (Yes I know I still typed them in F but that is besides the point) Question 3: H and I can be combined to form J Is there a similar way to combine them all? I just started messing with Excel this past January so please excuse my inexperience here. |
#2
|
|||
|
|||
Ye olde blank cell question.. maybe
Hi
Starting on another sheet, you could choose Data=Filter=Advanced Filter Select Copy to another location List Range use the icon to point at the range of your Source Data on original sheet Leave Critera Range Blank Make Destination Range equal to Say A1 on your destination sheet Select Unique records only Since all the blank cells will not be unique rows, then only the completed rows will come across. Alternatively, if the order of the rows does not matter, copy all the data to another sheet, then sort on Column A and all the blanks rows will go to the bottom of the list. -- Regards Roger Govier "XLguy" wrote in message ... I assume this question has been asked a dozen times but *I* haven't quite seen it asked yet. I have seen similar questions but they are not the same.. at least I don't think they're the same as mine.. but anyways... I have a couple columns (say A and B) which contain data Lot# in A and Footage in B. Lot# are alphanumeric i.e. 4C02304. Now these columns in this example have 20 rows, but not every row has an entry, many are blank inbetween the Lot#. On the same sheet or another I need to make a new list with the Lot# and Footage order intact but excluding all blank cells. Our IT can't/won't "enable" VB macros or something to that extent (???) So I came up with this so far, and though it works beautifully, it doesn't 'feel' professional. It feels like I am trying to kill a gnat with a sledge hammer or an 18-wheeler ;^) Again column A has Lot#'s. B has the lengths. So for this example: A B ---------+------ 4S03801 12000 4S03703 48000 4S03912 39000 4S03110 58350 3S33001 58350 3S00101 58350 3S00606 37000 4S00707 49000 --------------------- now the sledge hammer solution bit goes like this: I create a numbered list 1-20 in column F where F1=01, F2=02.... F20=20. G1=COUNTA($A$1:A1) (Copied and pasted to G20) H1=MATCH(F1,$G$1:$G$20,0) I1=INDEX($A$1:$A$20,H1,1) (or these two combined to make) J1=INDEX($A$1:$A$20,MATCH(F1,$G$1:$G$20,0),1) Again these are copied and pasted from row 1 to row 20 this result in the following output: F G H I J --+-----+----+----------+---------- 1 1 1 4S03801 4S03801 2 2 2 4S03703 4S03703 3 2 5 4S03912 4S03912 4 2 8 4S03110 4S03110 5 3 10 3S33001 3S33001 6 3 13 3S00101 3S00101 7 3 17 3S00606 3S00606 8 4 20 4S00707 4S00707 9 4 #N/A #N/A #N/A 10 5 #N/A #N/A #N/A 11 5 #N/A #N/A #N/A 12 5 #N/A #N/A #N/A 13 6 #N/A #N/A #N/A 14 6 #N/A #N/A #N/A 15 6 #N/A #N/A #N/A 16 6 #N/A #N/A #N/A 17 7 #N/A #N/A #N/A 18 7 #N/A #N/A #N/A 19 7 #N/A #N/A #N/A 20 8 #N/A #N/A #N/A So it works and all, great. But is there a better way? Question 2: Still using the above, how could I eliminate column F and the function in H use these index's without me having to type each one. (Yes I know I still typed them in F but that is besides the point) Question 3: H and I can be combined to form J Is there a similar way to combine them all? I just started messing with Excel this past January so please excuse my inexperience here. |
#3
|
|||
|
|||
Ye olde blank cell question.. maybe
XLguy
One method Select your range. Hide the rows with no data by EditGo ToSpecialBlanksOK FormatRowHide. Select the range of visible cells. EditGo ToSpecialVisible cellsOK Copy these cells and paste at C1 or on a new sheet. Unhide the blank rows on original sheet. Gord Dibben Excel MVP On Tue, 10 Feb 2004 16:12:37 GMT, XLguy wrote: I assume this question has been asked a dozen times but *I* haven't quite seen it asked yet. I have seen similar questions but they are not the same.. at least I don't think they're the same as mine.. but anyways... I have a couple columns (say A and B) which contain data Lot# in A and Footage in B. Lot# are alphanumeric i.e. 4C02304. Now these columns in this example have 20 rows, but not every row has an entry, many are blank inbetween the Lot#. On the same sheet or another I need to make a new list with the Lot# and Footage order intact but excluding all blank cells. Our IT can't/won't "enable" VB macros or something to that extent (???) So I came up with this so far, and though it works beautifully, it doesn't 'feel' professional. It feels like I am trying to kill a gnat with a sledge hammer or an 18-wheeler ;^) Again column A has Lot#'s. B has the lengths. So for this example: A B ---------+------ 4S03801 12000 4S03703 48000 4S03912 39000 4S03110 58350 3S33001 58350 3S00101 58350 3S00606 37000 4S00707 49000 --------------------- now the sledge hammer solution bit goes like this: I create a numbered list 1-20 in column F where F1=01, F2=02.... F20=20. G1=COUNTA($A$1:A1) (Copied and pasted to G20) H1=MATCH(F1,$G$1:$G$20,0) I1=INDEX($A$1:$A$20,H1,1) (or these two combined to make) J1=INDEX($A$1:$A$20,MATCH(F1,$G$1:$G$20,0),1) Again these are copied and pasted from row 1 to row 20 this result in the following output: F G H I J --+-----+----+----------+---------- 1 1 1 4S03801 4S03801 2 2 2 4S03703 4S03703 3 2 5 4S03912 4S03912 4 2 8 4S03110 4S03110 5 3 10 3S33001 3S33001 6 3 13 3S00101 3S00101 7 3 17 3S00606 3S00606 8 4 20 4S00707 4S00707 9 4 #N/A #N/A #N/A 10 5 #N/A #N/A #N/A 11 5 #N/A #N/A #N/A 12 5 #N/A #N/A #N/A 13 6 #N/A #N/A #N/A 14 6 #N/A #N/A #N/A 15 6 #N/A #N/A #N/A 16 6 #N/A #N/A #N/A 17 7 #N/A #N/A #N/A 18 7 #N/A #N/A #N/A 19 7 #N/A #N/A #N/A 20 8 #N/A #N/A #N/A So it works and all, great. But is there a better way? Question 2: Still using the above, how could I eliminate column F and the function in H use these index's without me having to type each one. (Yes I know I still typed them in F but that is besides the point) Question 3: H and I can be combined to form J Is there a similar way to combine them all? I just started messing with Excel this past January so please excuse my inexperience here. |
#4
|
|||
|
|||
Ye olde blank cell question.. maybe
"Roger Govier" wrote in
: Hi Starting on another sheet, you could choose Data=Filter=Advanced Filter Select Copy to another location List Range use the icon to point at the range of your Source Data on original sheet Leave Critera Range Blank Make Destination Range equal to Say A1 on your destination sheet Select Unique records only Since all the blank cells will not be unique rows, then only the completed rows will come across. Alternatively, if the order of the rows does not matter, copy all the data to another sheet, then sort on Column A and all the blanks rows will go to the bottom of the list. First off: Thanks for your response. Second, I tried it out, but for some reason excel does include the first blank it comes across but skips the rest therefore the first blank it comes across is considered a unique entry :/ Third: Supposing I could get the above problem with Advance Filter 'fixed' is there away to have Excel update this list automatically everytime the other sheet is updated with a new entry? |
Thread Tools | |
Display Modes | |
|
|