Free Microsoft Excel 2013 Quick Reference

Sorting a large spreadsheet

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.


Post your answer or comment

comments powered by Disqus
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 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 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

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

Afternoon everyone,

I have been racking my brain all day, and just don't know what to do with this situation. Here's the low-down:

I have a large spreadsheet (1000-plus entries) that all have about 10 or 15 columns of information (product title, product number, etc).

I also made a form that I want to use as a master form, where all the titles will not change.

What I am wanting to do is, by going off the product number, when you click it, this pre-made master form will pop up, and all the information that was located on the large spreadsheet for this product number will be inserted where it is designated to be on the master form.

While I could probably do something like this through Access, we need to keep it on Excel, and in the same workbook so that it will automatically update things once I put a new number or input onto the 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.

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?

I get the following message when attempting to edit a formular in a large
excell spreadsheet "Excel cannot complete this task with available resources"
I am using window 2000 and Office XP

I have a large .CSV spreadsheet that shows all of sales history for the
past 12 months. I would like to separate that data into bottom-tabbed
worksheets. I will end up with about 100 worksheets. Our 10-digit customer
code is the first column in the spreadsheet.

When this report was run once a month, I just cut and pasted the data
into separate worksheets manually. However, now the bosses want to be able
to run this report on demand.

If it would make the problem easier, I could break the data down into
separate spreadsheets wneh I export it from the filePro DBMS system. IE,
acmeco.csv, jonesinc.csv, smith.csv, etc.

Thanks!

I get the following message when attempting to edit a formular in a large
excell spreadsheet "Excel cannot complete this task with available resources"
I am using window 2000 and Office XP

I have a large spreadsheet with conditional formatting in some of the cells
within the rows. When I select a range and sort it, the conditional
fomatting does not stay with the cell. My spreadsheet then has cells with
the wrong conditional formatting in them. I have made sure that I have no
hidden rows or columns, no merged cells. Why won't the conditional
formatting move with the cell it was formatted for?
--
gfoley

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

I have a large spreadsheet (9500 lines) with data organized in the following
way:

name blank cell hire date starting salary
title blank cell blank cell current salary

I need to reorganize this information into a single, more efficient format
with the following headings:

name title hire date starting salary current salary

Because of the length of the document cut and paste is out of the question.
Can I use a macro or is there another way to reorganize the data? Thanks in
advance for any help you can provide.

I need to update a monthly spreadsheet on a daily basis. How do I get to the
bottom of the data without paging or scrolling down every time?

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 and need to just look at one invoice what is the
shortcut for this?

I am trying to sort a large spreadsheet using more than 3 criteria in excel. When you go to Data --> Sort, it only gives you 3 boxes to use. I want to do this in VBA anyway, and I think that is the only possible way to make this work - but I don't know what it would look like. Any ideas? Thanks

Each month I receive a large spreadsheet that must be cross referenced with a large sheet I created. I would like to find the overlap between these two sheets and create a third spreadsheet containing only the overlap. I have done this before manually, but it is tedious and extremely time consuming. Also, as the monthly spreadsheet grows, the task will only become more difficult.

To further complicate matters, there is not a single field that identifies an entry. I am using maps, so I have a north/south reference, east/west reference and a third zoning reference.

Summary: Is there anything I can do to find the overlap between the entries of two spreadsheets (using my three coordinates) to create a third spreadsheet of that overlap?

Thanks

I have typed a large list of staff without headings, but want to list them by
their type of job - worksheet includes person and the type of job they do


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