How do I extract specific information from a VERY long list

[goldenrose]

Hi there,

I work with huge data sheets that are sometimes more than a million lines long and 80 columns across.
Already in my current excel version I have run out of space and cannot even total the amounts, there is not one line left, so I usually use a pivot table to capture totals.

However this month I have 3 months (january, february and March) dates per transaction, with multiple transactions per day for the full term of each month 1-31 days.

I need to extract just the january and february information startin from the 1st Janaury and ending on the 28th February...I cant manually tick the drop down filter list as the sheet is too big to display all three months and it would take me a whole day.. how can i isolate january and february transaction and the information each line contains per hour per day.. is there a pivot command? or a filter that i just dont know about ? desperate!

 

[etaf]

how is your data structured

 

[ChrisR]

Hi,

If you click on the dropdown on your actual Pivot table you will be able to set filters etc, so you can create a custom filter to choose only data between the dates you have chosen.

This should be really quick and easy to do if you already have the data pivoted.

Regards,
Chris

 

[goldenrose]

Its completely unformatted raw data....

the transaction date column data has the day/month/year and time of purchase listed (per customer transaction) , so it will look like this...

01/01/2013 1:15 am
01.01/2013 4:15 pm
01/02/2013 3:15pm
01/03/2013 8:00am
01/03/2013 11:00am
02/01/2013 5m
02/03/2013 6:10pm
02/03/2013 17:00pm


it goes like this all the way up to 31 (january or march) the adjacent columns hold the country that the product comes from, and then what product it is, then the amount that the product is worth split into vat , discount , basic price and total.. then codes , the amount of time on the phone taken.. and a lot more stuff but thats the basics....

I need to split the january and february costs from the march and still have a fairly detailed breakdown of each day.. not necessarily every transaction of the day...just a per day view of january and february

Is that what you meant by "data structured"

Thanks

 

[goldenrose]

Barbara, it actually just freezes my whole excel when i try to do this.. .however do you have an example of how to do this, as I have not used filter on a pivot before... certainly not for filtering on this scale...?

 

[etaf]

so this is not a clever way to do it - and i'm sure there are much better ways to extract the rows
but i would

save with a different name - so you dont muck up the main spreadsheet
sort by date so the oldest date is at the top

then

click on the column with date - then use find control F and put the first day of the oldest month you want

then stepup to the previous row - and goto column A on that row
now use
control +Shift + uparrow to select all the row to the top of the sheet
which will select them all
right click - delete rows

that should have deleted all the rows from the oldest date to the first day on the first month you want

then for the first day of the next month of data you do not want - use find
so if you want all March data - then use 1/april

then control + shift +down arrow
selects to the bottom of the sheet
right click delete rows

now delete all dates after the months you are interested in

now you are left with just the dates you want

just make sure the order of the dates is correct for which up or down arrow you use to delete the rows

anyway - not smart - but thats how i would extract just the months I wanted

if it has to stay in the same workbook
I would - first make a copy of the sheet and then do the above

but the PC may not cope with even doing that -

 

[ChrisR]

Hi (It's Chris by the way,not Barbara )

It's a bit difficult for me to show you this on this forum......But I'll try and explain

If you have your pivot table and one of the row labels is 'Date'

If you look to the right of Date you will see a drop down menu button.......

Click that button and a list will drop down........One of the options is 'Date Filter'

The bottom two options are 'Date in the period'

By using this, you could select the month that you need to summarise the date for and show as much or as little data as you need by your other selections in your pivot table......

Hopefully that makes sense but very difficult to explain on a forum without seeing the data etc.........

P.s. when you are working on a sheet which populates every single row in Excel then I should imagine you are always going to experience performance issues/crashes etc. whenever you try and manipulate this volume of data, so always going to be tricky!