How do I filter out lines in a range?

[Guest]

I have a long list of data imported to excel (around 4400 rows). The data is
financial, so there is heading row giving general supplier details, and then
subsequent rows giving specific invoice details for the supplier. Not every
supplier has invoice details so I want to remove from the list the suppliers
that don't have invoice details. As in:
Supplier Code Invoice number Description Total
ABC Limited ABC-1
Invoice 1234 Items $100
Invoice 1235 Items $100
XYZ Co. XYZ-1
PQR Co. PQR-1

so I would want to remove the lines for XYZ and PQR.

Many thanks

Aaron

 

[Guest]

Use the Auto-Filter feature - Click on the InvoiceNumber Column,
Filter - Custom, Non-Blanks...

 

[Guest]

Thanks, but this won't work because I need to keep the ABC line, and
filtering it like that will remove that line as well.

 

[Guest]

You could "replicate" the Code fields which are presently "blank" as follows,
so the Auto-filter suggested would work - Showing you the Code value versus
the Suppler value (they appear to be interchangable, )
Select or Highlight B2 down to the last record, say B110 (B2:B100)
Click on the Edit menu, Goto.. click on the Special.. button - from the
resulting window click next to Blanks; And Ok out
This will return you to the Screen where the first blank cell will be the
activecell --
at this point simply press the Up Arrow key once which should move up to
cell B2,
then press down on the CONTROL key and at the same time press the ENTER key.
This will populate all the blank rows with the value from the cell above.
Now try your Auto-filter again..

 

[Guest]

Thanks again Jim - this idea has promise however it doesn't seem to work all
the way through... after selecting the range (which is C2:C4355) and doing
the goto special, it comes back to the sheet with all the blank cells
selected and cell C2 already as the active cell. Pressing any of the arrow
keys simply deselects all the blank cells, and pressing control and enter
before, after or while pressing any of the arrow keys doesn't do anything.
Any idea what I'm missing?

 

[Guest]

My response had to do with the Column 2 (B:B) which I assumed to be your
"Code" column, not the Invoice Number column.

 

[Guest]

I'm sorry - the example spreadsheet I gave was not a exact replica of the
spreadsheet - I was trying to keep it reasonably simple in the original post.
The first three columns of the spreadsheet are like this:
Supplier Code Supplier Name Supplier Reference
ABC0000002 ABC Banyard Ltd
26/01/2006 INV 4780
26/01/2006 INV 4783
ABL0000001 Able Door Spring & Metal Window Co
ADE0000002 Adecco UK Ltd
ADM0000002 Admiral Property Maintenance Services Ltd
ADT0000001 ADT Fire & Security plc
ADT0000005 ADT Fire & Security plc
ADT0000005 ADT Fire & Security plc
ADV0000005 Boon Edam Ltd T/a Advanced Door Care Ltd
AED0000001 Aedean Ltd
AIM0000001 AIMS Group Services Ltd
AIR0000003 Airbourne Cradle Engineers
25/11/2003 INV 8058
AIR0000016 Air Liquide UK Ltd

So the reason I used column C rather than B as in the example is because
there are no blanks in the code column, I didn't realise it would make a
difference which column I used. Perhaps I was trying to be a little too
simplistic with the original example.

So, again from the part of the spreadsheet above, what I need to do is
remove all lines other then ABC Banyard and Airbourne Cradle, and the lines
with "INV" following them.

 

[Guest]

Quick response,,
Took your new info;
Inserted a new column after your column C
with new blank column cell D1 "helper"
in cell d2 I entered:
=IF(OR(B3="",C2<>"",C3<>""),"Y","")
and copied down to D3000 (or whatever..)
Then did an Auto-Filter on Column D for 'Y"'s;
got what you want.

Hide Column until you need it.

Gotta run to lunch...

 

[Guest]

Brilliant, why didn't I think of that?!?!

Thanks very much.

 

[Jim May]

Thanks Aaron;
After a few false starts I'm glad I could help you. A formula/helper column
solution is all I could "quickly" come up with at the time (although I new a
VBA solution would be better - not having to insert a cloumn and re-enter
the formula - should either get overwritten... I've been trying to break
into the Excel VBA programming and the Worksheet functions stuff now for 3
to 4 years and am just now beginning to understand the tip of the iceburg..

Looping has always baffled me - screwing my head into the ground, but once
you understand it, it is the key to performing 75%-90% of things (you need
doing).
Taking your last data info - just for practice I tried to create a VBA
solution, and it might work better for you. At present it is a bit crude at
it first goes from bottom to top (Column B) hiding all rows which do not
have the text "INV" in Column B (But it hids the Supplier Name -
BADDDDDD!!!) part 2 of the same macro returns and goes from bottom to top
(Column B) again and Unhides any previous row 1 above the first occasion of
the Text "INV".

Copy this into a Standard Module of your workbook:

Sub Foo()
Dim Lrow As Long
Dim i As Integer
Dim j As Integer
Application.ScreenUpdating = False
Lrow = Cells(Rows.Count, "B").End(xlUp).Row
For i = Lrow To 2 Step -1
If Cells(i, 2) <> "INV" Then
Cells(i, 2).EntireRow.Hidden = True
End If
Next i
For j = Lrow To 2 Step -1
If Cells(j, "b") = "INV" Then
Cells(j - 1, "b").EntireRow.Hidden = False
End If
Next j
Range("B2").Select
Application.ScreenUpdating = True
End Sub

Of course, after running the Macro - then Printing Your stuff,,, but when
you want to REVERSE the process that is Unhide
the previously hidden rows, a quick way is to while in the sheet click on
the SelectAll coordinate button (I call it) the blank
cell above the 1 (row) and to the left of the A (column) -- Doing so selects
every cell in the sheet - With it selected at the menu,
select Format, Rows, Unhide.
Besst of all No Formulas, No new Columns (hidden or otherwise)...

Your back in business;;
Just run Foo, anytime you want to view just rows with INV's...
Glad to help;
in appreciation just give me your feedback..
Tks,

Jim May
Virginia, USA

Once we