Free Microsoft Excel 2013 Quick Reference

Sorting Help:large spreadsheet

hey there I am having some sorting issues. I have a very large spreadsheet and i need to make a moving average of it by date. so i have to sort it by date but it looks like this:

___A___________________B_____________C
1 Need to sort by this___Some Value___=avg(b1:b2)
2 Need to sort by this___Some Value
3 Repeat this 2 row pattern many times.
I need to make a moving average of C by date. But it is an average of the b columns, so when i sort it the value changes.

any ideas? long story short i cannot rewrite all the b columns. I think i might have to rewrite all the c columns, as numbers not averages. As in retype the average as a number.


Post your answer or comment

comments powered by Disqus
hi!

I have a large spreadsheet with entries such as this:

TownA | CodeA1
TownB | CodeB1 | CodeB2
TownC | Code C1
TownD | CodeD1 | CodeD2 | CodeD3 | CodeD4
... etc

which I would like to turn into this:

CodeA1 | TownA
CodeB1 | TownB
CodeB2 | TownB
CodeC1 | TownA
CodeD1 | TownD
CodeD2 | TownD
CodeD3 | TownD
CodeD4 | TownD

Note that the names used above are not indicative of the actual content of the cells.

Does anybody know how I can do this?

Thanks very much for any help you can give.

Maff.

confused,

i know your project is probably over by now but i came across your post
today and had to reply. i too suffer from "data slip" and you are the first
person to talk about. i am wondering if you have had any luck resolving the
problem. i understand select all needs to be done before sorting and i am
assuming you do this as well.

i have a similar spreadsheet in terms of size but mine deals with projects
and milestones so there are lots of dates on a single row. i began to find
dates that were way out of place. soon i began testing. i would enter a date
in a cell, save the ss, close, reopen, and the data was in a different cell.
with mine it didn't just slip to empty cells. i suspect your data copied over
other data also but it was harder to spot. my ss is shared by multiple users.
i have also noticed that the change history will not record changes correctly
(i.e. a cell which has not been changed will show a change made to it while
the cell whose data was changed does not show a change). the only way i can
resolve the problem is to unshare the ss, fix the cells and then reshare it.
this has really become a problem.

none of the techs i have spoken with have been able to recreate the problem.
the latest suggesting i have been given is to make sure all versions of excel
which edit the ss have been fully updated. like you i sort and filter the ss
all the time.

as a side note, i too am a novice compared to the average person answering
posts on this site but i had a few thoughts about your project if it could
still help. first, don't sort while the ss is filtered, this will hang your
computer forever - sort first then filter. second, this is a low tech
solution for comparing a ss without having a unique key column - what about
creating one ss which at least has all the last names filled in and make
another which has at least the zip and so on. compare each to another ss
which has been prepared the same way. when finished merge them back together.
i'm not sure it that makes any sense but maybe it will help.

anyway, anything you can share about "data slip" would be appreciated. thanks,

scott

"ConfusedNovice" wrote:

> Thanks, Dave. This thread is getting unwieldy, but I
> *think* I understand what you're advising.
>
> Sticking with the issues at hand, yes, the records are all
> one per row. Last Name is probably the best key, but there
> are some records with only a Company Name, & a few with
> only an email name. I have been using Last Name & Zip
> Codes to try & match things up.
>
> I discovered that when sorting some of the intermediate db
> versions by "new" entries, those newest entries were
> mostly correct. I am thinking of isolating these records &
> copy/pasting them on to the oldest db version. The idea is
> to try for a cleaner sheet to compare to the current one.
> Maybe it would be better not to merge these? It's crunch
> time - I will try your formulae on a test file today.
>
> And moving ahead: If I can execute the comparisons you
> suggest, and if I can achieve a cleaner list, then what's
> the best way to prevent data from slipping into the empty
> cells in the future? Should I make a new index column,
> number each record, and append & number all new records?
> Can people safely sort & filter the data? What if they
> update a record in a filtered list & then re-sort? Or
> Find/Replace & then re-sort? I still don't understand how
> some of the data slipped out of one record & into another.
> Until I do understand, I'm afraid it will continue to
> happen & all this work will be for nothing.
>
> Thanks again! It's an ugly way to learn Excel.
>
> Jane
>
> Subject: How to sort/update large excel db
> From: "Dave Peterson" >
> Sent: 9/30/2004 1:05:46 PM
>
> Just to clarify, is your data laid out one record per
> row? You don't use
> multiple rows to represent one "logical" record, do you?
>
> If you do, then this will make it even more difficult.
>
> But to match/merge two different worksheets, I would think
> that you'd need to
> have a unique key/index into the data.
>
> If you don't have this key, how do you match up your data
> to look for
> differences?
>
> I wouldn't put too much faith in having that unique key in
> the last name
> column--but maybe you can use a couple of columns that
> could serve as that key.
>
> For instance, if you had the lastname, firstname, address
> in 3 separate columns,
> you could use a helper cell and do something like:
> =A2&char(10)&b2&char(10)&c2
> (The char(10) probably won't appear in your data and will
> serve as a field
> delimiter--just in case there's records that might look
> identical when
> combined.)
>
> But even if you did this, you'd have to make sure that
> these fields weren't
> changed. If any were changed, then you couldn't use them
> for a match between
> worksheets.
>
> To find out if the records in one worksheet are in the
> other (based on any
> single column you can use--in your data or derived from
> your data):
>
> =isnumber(match(a2,sheet1!a:a,0))
> and drag down.
> You'll see True if it appears on that other worksheet.
>
> And use the equivalent to check the other worksheet.
>
> Once you've found your Falses--you'll want to inspect them
> to see if they should
> be cleansed--cleaned up to make them match.
>
> You could end up fixing the data on either worksheet--just
> to find the matches.
>
> Once you get rid of all the Falses, you can do the =vlookup
> () stuff to match
> merge.
>
> I'm not sure if all this work can be done on a quick
> schedule. This kind of
> stuff usually means fix, check, fix, check....until you
> can't find any more
> differences. Then you let someone else review it and it
> starts again.
>
> Good luck,
>
>
>
> ConfusedNovice wrote:
> >
> > Still trying to understand what I've got. There is no
> > column with all unique entries. Every column has blank
> > cells someplace. On 2 of the earlier database versions,
> > the leftmost column (Column A) is set up as "Index
> > Column," but not all the records have an "Index" number
> in
> > this column - some of them are blank, & some have zeros
> in
> > Column A. The most recent master does not have Index
> > column, it starts with a Last Name field.
> >
> > This brings me back to the question of how to prevent
> > names/values from slipping into the blank cells when
> > sorting & updating records. I have been reading a lot of
> > Excel info, but this is *not* my area at all, & I want to
> > fix these problems, not compound them. Should I be able
> to
> > identify key column(s)? If yes, how? And if no, is there
> > any safe way to sort when blanks exist in every column?
> > (Or can it be a Key & contain multiple blank cells?)
> >
> > There are ~17,000 records, & when the wrong values move
> > into a blank cell, that record doesn't indicate that it
> > has been changed. (Probably because it was not opened?)
> >
> > I'm seriously running out of time. I've got a pretty big
> > list of changes ready to enter directly on db records. I
> > also have isolated a few hundred recent entries by date,
> > verified them, & placed them in a separate worksheet.
> > (Hoping to append to a clean main list & sort into
> place.)
> > I don't think it's possible to verify every row.
> >
> > Yes, no matter how you look at this, it's a pain! I'm not
> > really dumb, just a total rookie, & your help is
> > invaluable.
> > To summarize:
> > Whatsup with "Index Column"?
> > How to identify/designate Key columns?
> > Implications of having blank cells throughout sheet?
> > Fastest way to add/edit records if I go back to an older,
> > cleaner version of the database?
> >
> > Thanks again!
> >
> > Jane
> >
> > >-----Original Message-----
> > >I'm saying that if you have two "live" copies of the
> > workbook, it'll be a pain
> > >to merge one of them back into the other.
> > >
> > >There's nothing built into excel that will force you to
> > make sure your key
> > >column entries are unique.
> > >
> > >When I do this, I spend some time verifying that the
> data
> > is at least
> > >consistent--no additional entries with the same key were
> > added. If you can
> > >believe/verify this, then, yeah, you can do =vlookup()'s
> > to merge the updated
> > >values into the one real workbook.
> > >
> > >It's just in my experience, avoiding this is usually
> > better. (Just give the
> > >file to someone else and tell them to return it when
> > they're done--and put a
> > >freeze on any other changes.)
> > >
> > >
>
>
>

I have a large spreadsheet that I want to sort, but I do not want the row
heights to change from what they currently are. Right now, row 3 will remain
at the height I have it set at rather than at the height of the information
that is being put in row 3 after the sort. Any help on this would be greatly
appreciated.

Hey everyone,

I've had quite alot of experience with Excel in the past, and recently started in my third year of Uni only to be given a large spreadsheet, needing macro's in order for me to find certain pieces of information. I've got the entire spreadsheet layed out with the information I need, and have attempted a few times to write some simple IF based macros in Excel to allow me to fill the column the whole way down getting all information, but I keep bumping into problems. As far as I can tell, the only way for me to do this might be to use VBA, something with which I have no experience... The assessment and my course have absolutely nothing to do with computers (It's an Energy and Environmental Engineering course), or VBA for that matter. Which is why i'm panicking a bit as I don't want this to hold me back for so long that I don't have time to focus on the actual assessment criteria!

I tried to attach the excel document, but it wouldn't let me, so I've uploaded it at Mediafire. The link: http://www.mediafire.com/?8asw69ru3px11a9, with S21-V26 showing the information I need (every row with P having a value above 1 should contain data), but there are going to be alot of 0 values in-between. This is what is causing me the most problems. I could simply copy and paste those cells to the next group which will contain values, but since there are over 4000 rows, this could take a bit of time.

There must be a simpler way of getting around this, if anyone could spare a bit of time to give some advice regarding what I should do, it would be greatly appreciated... you'd really be helping me out!

Thanks in advance,

Ben

Hi there,

I have recently been sent a large spreadsheet with close to ten thousand lines on it. I have been given the job of finding a quick and easy way of amending each line.

Whats needs done:
A cell in every record (line) has a 12 digit number but this needs to be reduce to a 9 digit number . i.e. the last 3 digits of every every number needs to be removed.

Can anyone advise of an easier way than going down the sheet one by one and deleting the last three digits of each number for the next week.

Let me know if this does not make sense to anyone. Any help will be very much appreciated.

Many Thanks peeps!

I have a large spreadsheet that is a report generated by a program that I routinely split by hand into many smaller spreadsheets which are saved under a filename that comes from data on the spreadsheet. The format of the large spreadsheet is not mutable as it is generated by another program.

I want to be able to have a macro that will take a spreadsheet like "large spreadsheeet Example.xlsx" and create many smaller spreadsheets like:

"Variable Name1.xlsx"
"Variable Name2.xlsx"
"Variable Name4.xlsx"
"variable Name5.xlsx"
"variable Name6.xlsx"
"variable Name7.xlsx"

I'm attaching an example large spreadsheet and an example from the middle of the large spreadsheet of what the smaller sheets end up looking like once I have cut and pasted and saved them. The name of the file is important and is the value of "Variable Namexxx" in the spreadsheet (it's just a persons name in reality), so the name of a file above would look something like "John Smith.xlsx" or "Sam Jones.xlsx". Whatever is on the spreadsheet.

The acutal spreadsheet is many thousands of lines long and contains 100's of "variable namexxx", so once done, you will have a directory full of 100's of smaller files that the macro creates. The amount is variable depending on the run date of the report.

The large spreadsheet, while it will be considerable longer in real life, will follow the example given. It will ALWAYS begin as you see it and ALWAYS end as you see it with a variable number of lines in between with many different "Variable Heading1" type fields on it. The number of rows per "Variable Heading" is not set and can be 100's of rows long (I have shortened them down for sanity in the examples). The "spacing" that comes before and after the smaller spreadsheets follows the pattern you will find. If the "Variable Heading1" does not change, then there are two blank rows and the next "Variable Heading1" row. If this changes to "Variable Heading2" (or whatever the next one is), then there will be 6 blank rows between them. I'm not sure if this is even helpful information as I think the best source to pick the beginning row of the smaller spreadsheet is that this row contains the data "Static1:". And the last row that you would select in the copy fuction would contain the data "Static8". I'm not sure if this is a good delimiting factor, but it is basically how I do it manually (actually, I'm looking at both this and the color cues as I scroll around cutting and pasting).

Also, as you can see in the smaller spreadsheet examples, the formatting of the original large spreadsheet is kept intact on the smaller ones (same column widths, row heights, background colors, etc...etc...). This formatting is very important as I have another macro that is used on the smaller spreadsheets to further change them into their final form which is emailed out the the individuals for them to enter other data on that spreadsheet which is then collected, further worked on by macros, and finally put back into another large spreadsheet with all the newly collected data shown from the emailed individuals. I've managed to do all the other macros, but this one eludes me.

Thanks for taking a look in advance as well as many thanks later...

DKH

I have a large spreadsheet, 32,000 rows X 13 columns and I need to create graphs of the data but 32000 data points is too many. I need to select every fifth or Nth row and copy it to another sheet. I think a macro would work, but i don't know how to write them. Can anyone please help me???

I am enjoying learning VBA, but I don't want to create VBA code unnessesarily (over complicate things), if there is a practical built in method that might be getting overlooked.

Question 1
Is there a built in way in Excel 2003 to sort a large number rows and columns in date order based on the date on one of the columns without repetitively having to manually select the range of cells.
Or would I need to record a macro to do this?

For example, when a new row of data is entered, can the row / column range be auto sorted in date order automatically when the date is entered?

Question 2
Any suggestions relating to sorting data that would be worth investigating?

Hi, I have an extremely large spreadsheet which needs to be split into multiple documents depending upon categories.

For instance, maybe 10 rows is one category, specified by a number, in the first column, and I need all of those to be copied into it's own document. This must be done for many different categories. Everything is sorted in category batches, so the first 10 rows is one category, the second 15 another, the third 34 yet another, etc.

There wouldn't happen to be a way to automate this splitting by using a macro or Excel feature? All it would have to do is check the first column, take all the rows with the same number (each cell in the first column has the category number), and split it into a separate document.

It would also be convenient to have it paste the category batch in question starting on the 2nd row, with a predefined first row as standard column headers that I'd like to have in each separated document. If there was a way to have it format the filename of each file to have "predefined text - category number.xls" that would be good too.

Can this be done?

hello, this is my first time posting so excuse me if i do anythign wrong

i would like some help on a script for sorting a large selection of numerical data in ascending order

the range of the selection would be the whole column (i.e. i would click the A if i wanted to sort the A column, but the script should also work for sorting out other columns)

for example

i have data that is

10/12312/123312/231/1232
24/12312/123312/231/1230
46/12312/123312/231/1233
57/12312/123312/231/1235
99/12312/123312/231/1234
13210/12312/123312/231/121223

appreciate the help

Hello.
I need a little help with a problem.

I need to sum up certain line items with the same name from a large spreadsheet.

Any help would be appreciated. I have attached a spreadsheet

Thanks.

I have a very large spreadsheet with text entries and would like to set
it up so that I could click on a letter, for example, "d" and I'd be
taken to the first cell with an entry beginning with "d". I also need
to be able to continue to add data to the spreadsheet.

I've read a lot of posts and the best solution seems to be setting up a
macro. My problem is that I've never been able to record a macro and my
attempts to try these in both Word and Excel have failed miserably.
I've basically been self-taught in the Microsoft office programs for
the past few years because I work in a 4-person office with no advanced
users. I've read several books and searched the web for hours but most
instructions are over my head and I've never been able to locate a
good, simple tutorial.

Is there a way to get a ready-made macro to accomplish this? And is
there anywhere I could get the most basic, simple detailed,
step-by-step instructions on how to get it on my pc and use it? I've
seen posts that contain responses with code in them, but I have no idea
what to do with code. finally, if I can do this, is there somewhere to
purchase, copy, etc. other macros that would make my job easier? Any
help would be appreciated.

Jeanne P.

I have a large spreadsheet that another program "downloaded" information into; but without formatting.

I have created all the formulas that are necessary in the first section, but am wondering if there is a way to create a macro or VBA to do this automatically (there are more than 600 wells to do manually!) As you can see in the attachment, not all wells have the same information, which is creating my problem.

Thanks in advance for any help!!

Hi
I must be doing something wrong.

I have a large spreadsheet which I have use a conditional format...some line
work ok but other do NOT

For example

In Col B I have 58.05
In Col C I have 8
In col D I divide Col B by Col c and get 7.26

The same occurs in Col F I have 9 and in Col G I have 1:Then I divide Col F
by Col G and get 9 in col H
I have formatted COL H as follows:
Cell value greater than or equal to col D...........make it GREEN
Cell value less than COL D.............make it RED
In this case the color is GREEN and It should be red, Most other lines are
OK but not all
I need help...please
and Thanks
Norton Professional 2004 says this email is clean...believe it

Hello,

I have to sort a large number of records- more than 100k to be more specific. The problem is most of the records result from different functions (vlookups, if or isna). Each time I try sorting them excel 2007 gets stuck, not responding. I sorted them quite easily after I copy/pasted all with Paste Special/Values.

Is there a way I could "trick" the Sort functionality in excel so that It won't calculate the formulas again when sorting...but still keep the formulas there:D?

Thanks in advance!

I am using vb6 and I need to Sort records in spreadsheet based on items in column 7.
Actually, I want to sort the spreadsheet before executing the count process below.
Note: the code below works perfectly, I just need to sort the spreadsheet before counting.
Any assistance will be greatly appreciated.


	VB:
	
Selection.Subtotal groupBy:=7, Function:=xlCount, TotalList:=Array(7), _ 
PageBreak:=False, SummaryBelowData:=True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
In otherwords, I want to sort the records based on the 7 column before executing the above routine.

Thank you.
tgifgemini

I have a large spreadsheet with over 50 columns but usually only need to view a small selection of them. Many colleagues use the sheet and often use the format, hide, column function to hide columns they do not need but then the next user has to unhide them and re-hide others until they are left with the different columns they require.

Is there anyway of creating a menu which allows users to select from a list of all the columns those they wish to display, all the others therefore being hidden.

Many thanks

Nick

I have a large spreadsheet I dumped from access DB and am wondering if I can check a cell located in column C to the data appears to be a valid email address. The people who enter the data in access tend to put the info in and it is not alway perfect if you get my drift. I am getting ready to use this SS for a large email list and don't rilly feel like sifting it by hand.. Thankd for the advise if it is possible

I have a large spreadsheet that I want to print onto ONE page (onto legal size or tabloid size paper). Columns A-K. I can do this from a PC without a problem but when I try to do it from my MAC it shrinks it to 10%. All margins are fine, landscape orientation is fine, and all other print options look normal why does the MAC have a problem with this and the PC works fine?

I use a large spreadsheet with over 60,000 rows and 30 columns with formulas
such as Vlookup to track and update inventory for my business.

The spreadsheet is taking a long time to calculate, eventually it will
complete the task but it just seems to take an exceedingly amount of time
every time I make a change. I have 2GB memory and a P4 processor 4.3 Gh or
so. What is the correct hardware to use with such big spreadsheets? should
I get a workstation with a bunch of memory and dual core processor? Would
that solve the issue or will the spreadsheet still be sticky ?
--
DJ

we have to use arrow keys to manuever spreadsheet. very large spreadsheet,
last year got error message of "too many cell formats." questions:
1. would it make a difference to move up to v. 2003 (on 2000 now)
2. what is causing problems
3. what else can be done to resolve this problem
thanks

I need to sort a large spreadsheet - by the column of account numbers - (over
50K) with duplications. I need to remove the duplications and keep only 1
entry of each account

Office XP Help File: Spreadsheet Component Help Update

More Tips for Finding Records in Large Spreadsheets


No luck finding an answer? You could always try Google.