Another Excel Query?

[mahking51]

Hi All,
Each month I download a large csv file (approx 500-700 rows) from Ebay
I then need to process this so that I can enter the values into my stock book in the way that I need for my purposes.
There is a row for each item that contains the text 'GALLERY FEE' that they no longer charge for so its value is always empty and I just delete this row when doing my work on the sheet.
Is there any way that I can just tell Excel to delete ALL rows that contain that text in one fell swoop, it would save me quite a bit of time?

EB are very annoying in the way that they present the data, each item occupies at least 4 rows and sometimes as many as 6, more if there is a relisting with consequent refunds of fees. They are not even consistent in the order that he rows are listed each time, very annoying.

All I need to end up with is ONE row that has the combined fees, item number and description, date etc. I then transfer this to my stock workbook.
I am using Excel 2007.
Thanks,
Martin

 

[Dibs-h]

Hi All,
Each month I download a large csv file (approx 500-700 rows) from Ebay
I then need to process this so that I can enter the values into my stock book in the way that I need for my purposes.
There is a row for each item that contains the text 'GALLERY FEE' that they no longer charge for so its value is always empty and I just delete this row when doing my work on the sheet.
Is there any way that I can just tell Excel to delete ALL rows that contain that text in one fell swoop, it would save me quite a bit of time?

EB are very annoying in the way that they present the data, each item occupies at least 4 rows and sometimes as many as 6, more if there is a relisting with consequent refunds of fees. They are not even consistent in the order that he rows are listed each time, very annoying.

All I need to end up with is ONE row that has the combined fees, item number and description, date etc. I then transfer this to my stock workbook.
I am using Excel 2007.
Thanks,
Martin

Martin

What I normally do when I want to ignore certain rows is add an extra column - call it what you want - and put a formula in it along the lines of

=IF(A1="GALLERY FEE", 1, 0)

where A1 is the column that will have the text value GALLERY FEE in it. Then auto filter on the "new" column and exclude the Value 1. Probably other ways to skin this cat - but works for me.

HIH

Dibs

 

[matt]

It's definately a ROW containing the gallery fee? For some reason I'm picturing rows for items and gallery fee being amongst a series of columns for each item. If this were the case then you could choose to "Not import" the gallery fee column.

Or... You could use DATA > FILTER > AUTO FILTER to allow you to filter to see just the Gallery Fee rows and then delete them before reverting to seeing all rows.

 

[mahking51]

Dibs-h, Thanks I will try that on a copy and see how it goes
Matt, Definitely a row, a typical item might be:

date, spokeshave,itemNo,insertion fee, £0.13
date, spokeshave,itemNo,gallery fee fee, £0.00
date, spokeshave,itemNo,iinternational visibility fee, £0.04
date, spokeshave,itemNo,final value fee, £2.36

so all I would want from that is the cumulative insertion fee =£0.17
and the final value fee=£2.36 to go across into my stock book as EB fees for that item.
Just being able to lose the gallery fee row would make it that much less right click/delete work.
Could this be done using a macro that I could store and load when needed?
Cheers,
martin

 

[mahking51]

Of course the final value fee above may be weeks away on a different date or even different invoice to be added when item is sold.
Everything ties together in the stock book via the item number.
Martin

 

[Dibs-h]

Dibs-h, Thanks I will try that on a copy and see how it goes
Matt, Definitely a row, a typical item might be:

date, spokeshave,itemNo,insertion fee, £0.13
date, spokeshave,itemNo,gallery fee fee, £0.00
date, spokeshave,itemNo,iinternational visibility fee, £0.04
date, spokeshave,itemNo,final value fee, £2.36

so all I would want from that is the cumulative insertion fee =£0.17
and the final value fee=£2.36 to go across into my stock book as EB fees for that item.
Just being able to lose the gallery fee row would make it that much less right click/delete work.
Could this be done using a macro that I could store and load when needed?
Cheers,
martin

Martin

It seems you are getting back a 5 column CSV file

Date, Description, ItemNbr, FeeDescription, Fee

Then loading this in - Cols A to E then inserting the formula,

=IF(D1="GALLERY FEE", 1, 0)

into say F1 and then double clicking in the bottom RH corner of that cell - will auto copy the formula all the way down to the last cell. Then Auto Filter on F - selecting only 0's. This is how I do it - when I want to exclude rows. Haven't come across any other way so far.

Dibs

 

[mahking51]

Hi Dibs, Tried that but it seems to make a complete bollix of the sheet, unless (probably) I am being really stupid.
Any chance I could send you the file to look at?
Cheers,
Martin
[email protected]

 

[Dibs-h]

Hi Dibs, Tried that but it seems to make a complete bollix of the sheet, unless (probably) I am being really stupid.
Any chance I could send you the file to look at?
Cheers,
Martin
[email protected]

sure mate - PM sent.

Sorry - should have said - I've sent you an email!

 

[matt]

I reckon Notepad++ will allow you to remove the bits you don't want prior to import. Happy to have a go with the sample text you included.

 

[Dibs-h]

I reckon Notepad++ will allow you to remove the bits you don't want prior to import. Happy to have a go with the sample text you included.

I don't think it'll remove whole lines based on an occurrence of a single Word (or 2). If it will - I'd love to know how as it's my text editor of choice!

Thanks

Dibs

 

[matt]

Yep - does it.

Now You see it (them):

Now you don't (see them):

Open in Notepad++.
Write the word "gallery" on a new line (easiest at the top).
Highlight the word "gallery" (on the new line) and copy to clipboard.
Select Text FX > Text Viz > Hide Lines With (Clipboard) Text.

Lurve Notepad++

 

[SPinonit]

All I need to end up with is ONE row that has the combined fees, item number and description, date etc. I then transfer this to my stock workbook.

Hi Martin

I love Excel challenges. If you can send me a csv file and a blank copy of your workbook/worksheet, I'll see what I can do?

I'll pm you my email address just in case

Steve

 

[matt]

Pivottable on date, item number, item description + data fees (optionally breakdown of fees by type).

This is Open Office's Data Pilot (essentially the same as pivotable in Excel).
Less than 30 seconds = two options on the right hand side from the raw data on the left.
First with a breakdown or second with just a sum of the fees.

 

[SBJ]

Cant you just sort the data by the transaction type and then delete the gallery fee rows??

 

[Dibs-h]

Yep - does it.

Now You see it (them):

Now you don't (see them):

Open in Notepad++.
Write the word "gallery" on a new line (easiest at the top).
Highlight the word "gallery" (on the new line) and copy to clipboard.
Select Text FX > Text Viz > Hide Lines With (Clipboard) Text.

Lurve Notepad++

Ta very much!! =D> =D>