Ye olde blank cell question.. maybe

[XLguy]

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.

 

[Roger Govier]

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.

 

[Gord Dibben]

XLguy

One method

Select your range.

Hide the rows with no data by Edit>Go To>Special>Blanks>OK

Format>Row>Hide.

Select the range of visible cells.

Edit>Go To>Special>Visible cells>OK

Copy these cells and paste at C1 or on a new sheet.

Unhide the blank rows on original sheet.

Gord Dibben Excel MVP

 

[XLguy]

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?