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

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


"Aaron" wrote:

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

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.

"Jim May" wrote:

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


"Aaron" wrote:

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

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..



"Aaron" wrote:

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.

"Jim May" wrote:

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


"Aaron" wrote:

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

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?

"Jim May" wrote:

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..



"Aaron" wrote:

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.

"Jim May" wrote:

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


"Aaron" wrote:

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

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

"Aaron" wrote:

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?

"Jim May" wrote:

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..



"Aaron" wrote:

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.

"Jim May" wrote:

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


"Aaron" wrote:

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