Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Too much data for Excel?

I have a source txt file that contains data for one column, however more than 65,536 rows that is Excel's 2003 limit.
Is there any way how to make Excel to accept more than the limit?

Thank you,
Ludo.


Post your answer or comment

comments powered by Disqus
I have a data dump that I'm trying to put into Excel, but unfortunately there are just over 68k rows. The data is a list of employees with their previous pay numbers on and is set out as follows:

Name Current Pay No NI Number Prev Pay No
Cand A 100/12345 AB123456C 150/55555
Cand A 100/12345 AB123456C 200/55555
Cand A 100/12345 AB123456C 250/55555
Cand B 100/54321 DE123456F 150/22222
Cand B 100/54321 DE123456F 250/22222

Due to these duplications, the data exceeds Excels row limit, so I was hoping for some advice on whether the following is viable and how easy it is to do?

I want to split the data dump into two sections, then get some VB code to put all previous pay numbers onto one line for each candidate, so the above example would look like:

Name Current Pay No NI Number Prev Pay No1 Prev Pay No 2 etc
Cand A 100/12345 AB123456C 150/55555 200/55555 etc
Cand B 100/54321 DE123456F 150/22222 250/22222 etc

Can anyone provide me with advice or the code required to do this?

Hi,

I need to search within a text, number and character string (?/cid=1) to find a specific code (eg cid=1) however because sometime the code within the text is longer (eg ?/cid=111) I am returning to much data for the shorter code. Is there a way for me to limit the way a vlookup returns a value so the duplicated data will not occur?

The formula I’m using is: =IF(ISNA(VLOOKUP("*"&$A4&"*",Sheet5!$A:$D,2,FALSE)),0,(VLOOKUP("*"&$A4&"*",Sheet5!$A:$D,2,FALSE)))

Where $A4 is the code and sheet!5A:D is the array.

Thanks.

I need an advice on how group data. The best way to explain this is to make an example:
On one sheet i have two columns with following data:
A B
1st China
1st Japan
2nd China
2nd Japan
2nd Singapore

I need to group data on second sheet followingly by using a formula:
A B
1st China; Japan
2nd China; Japan; Singapore

If i use VLOOKUP i only get the first value of column B on the first sheet and not the others. So all i could think of was a complicated IF formula but i have too much data for that and i even don't want to start writing it.

Any help is welcome.
Thankyou!

Hey guys, I need help making a simple macro for a solving a problem with data exported from Quickbooks. When I do an export the date data is in text format. When I do not have a lot of data I can simply go through the process of highlighting the first cell and pressing F2 and then enter until I get to the bottom of the data. As I do this each cell seems to take on the date format (Don't know why, i just know it works). This, however, is way too time consuming for some of the data files I have been working with lately.

Can anyone help me build a macro to solve this problem? I am familiar with recording macros, but when I try this one it simply takes whatever I had in the first cell and replaces the next cell with that, although I am only using the keyboard when recording to make sure my movement between cells is relative (I at least know that much).

Any help would be greatly appreciated. Thanks.

Sub DeleteHiddenRows()
    Dim iRow    As Long

    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False

        With ActiveSheet.UsedRange
            For iRow = .Row + .Rows.Count - 1 To .Row Step -1
                If Rows(iRow).Hidden Then Rows(iRow).Delete
            Next iRow
        End With

        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub
Hello-i;m new around here-no clue whatsoever of how this forum works-but i'm a bit desperate. I've been trying to filter and copy visible cells in a report containing 36000 lines (half empty,half filled with numbers). I copied the above code in VBA-posted by SGH some time ago...it worked on one file once,but when i tried again,no luck.

Am I doing something wrong here? I used excel 2003,my macro security level is set to Low. I've copied the code in VBA,sheet 1,saved the sheet and then reopened and copied the data i needed in there. After filtering the data by non blanks,i have the error with too complex data over and over again.

It's pretty urgent to have this issue solved and i can't think of anything now. Can anyone advise on the stuff with VBA code?Am I doing it right?Is there any other code that can perform the similar action in excel-deleting all hidden rows and eliminating the extra cells?

Please please please!

Greetings !

I have a spreadsheet, from which I have to abstract
(from two different WorkSheets) two tables
each extending to about 2,700 rows long by 18 columns
wide,
and include them into a WORD document.

Each will therefore run to some 45 pages,
so I want the top three rows to appear
as a heading on every page.

One will be Appendix A and the other Appendix B.

What I would like to do is to have a VBA routine
such that I can simply place the cursor in the WORD
document,
then Alt-Tab into the Excel WorkBook,
select the entire area that I want to copy,
and then click on a ToolBar Button.

I will be asked how many rows I want as Header,
and the data will be pasted automatically at the
cursor position in the Word Document
in the right number (45) of different bits, one per page.

When I tried an ordinary cut 'n' paste, WORD crashed....
I guess I was trying to paste too much data.

I've got a database of information that contains multiple levels of data.
For example, a school awards a certificate, associate's, bachelor's and
master's degree in nursing. The data is set up with a column for the school
name, another column for the award level, and a third column for the program
name. I have about 7000 schools, each with multiple award levels within each
program.

Is there any way to set up a worksheet to provide the information for each
school on ONE line?

I have tried pivot tables, but there is too much data and Excel cannot
display it all.

Anyone got any ideas about creating multiple levels of this information, to
eventually get to the one line of data?

Thanks in advance for your ideas!

I have created an AddIn for a project I'm working on using the 'Open' command in Excel Vb. While this worked perfectly for the file I was testing it on (1,740,754 bytes) it doesn't work on a new file (121,445,125 bytes).
On testing the code, one line at a time, it gets stuck on

	VB:
	
 #1, DataLine 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The files only contain one line so I suspect there is too much data for the command to handle.

Is there an alternative?

Hi,
I have a workbook with data on sheet1 and 3 charts on sheet2.
3 Charts because there is too much data for 1 chart.
The Y-scale values of the charts are automatically set.
This works by entering the following code in sheet1.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim intIndex As Integer
Dim r As Range

For intIndex = 1 To 3
Set r = Range("metingen" & CStr(intIndex))
If Not Intersect(r, Target) Is Nothing Then
With Worksheets("Grafiek").ChartObjects("Chart" & CStr(intIndex)).Chart.Axes(xlValue)
.MinimumScale = Int(Application.Min(r)) - 0.5 'Int returns integer portion of number.
.MaximumScale = Int(Application.Max(r)) + 0.5
End With
Exit For
End If
Next

End Sub

Here is the problem:
A user enters data by using a userform.
The chart will be adjusted when excel detect any changes on the worksheet.
But because of entering data by a userform it doesn’t detect any changes. I have to select the changed cells my self before the chart will adjust.
How can I solve the problem so that excel detects changes after pressing OK on the userform?

Thanx,
Fluppe

hi
i hv an excel sheet with more than 60,000 rows of data (abt 8 columns)
i need to find one particular number in all that data. i use ctrl-f to
search for that number. excel says data can't be found. i do a filter on
that column with the numbers and do not see that number in the scroll down
list.

BUT, that number IS in the sheet. after i found it (manually), the ctrl-f
works... excel magically finds what it could not find earlier. yet, the
filter scroll down list still does not display that number.

question: what is happening? is it because there is just too much data that
excel does not look in each and every cell?

hope someone can help. i just cannot keep manually searching through
thousands of rows... thanks in advance!

--
Message posted via http://www.officekb.com

hi
i hv an excel sheet with more than 60,000 rows of data (abt 8 columns)
i need to find one particular number in all that data. i use ctrl-f to
search for that number. excel says data can't be found. i do a filter on
that column with the numbers and do not see that number in the scroll down
list.

BUT, that number IS in the sheet. after i found it (manually), the ctrl-f
works... excel magically finds what it could not find earlier. yet, the
filter scroll down list still does not display that number.

question: what is happening? is it because there is just too much data that
excel does not look in each and every cell?

hope someone can help. i just cannot keep manually searching through
thousands of rows... thanks in advance!

--
Message posted via http://www.officekb.com

Hi,
I have a workbook containing a sheet for each letter of the alphabet. Each row relates to an item distributed. I have the distribution date in column A. In column B I have the 'Today' function. Column C is days lapsed using e.g. C2=B2-A2, formatted to a single number. I have a 27th page called 'Alarm, in which all the data from Column C of all pages is fed. Each cell is coded using the code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [A2] >7 Then MsgBox "A2"	, vbOKOnly + vbInformation, "Lapse Warning"
If [A3] >7 Then MsgBox	"A3"	, vbOKOnly + vbInformation, "Lapse Warning"
If [A4] >7 Then MsgBox "A4"	, vbOKOnly + vbInformation, "Lapse Warning"
If [A5] >7 Then MsgBox "A5"	, vbOKOnly + vbInformation, "Lapse Warning"
End Sub
And so on, from A2 to A200 all the way to Z2 to Z200. Thus by selecting the alarm page, I can see which items have gone over 7 days.

The trouble is;
a) This is a massive load of spaghetti code
b) It is too much data for the 64k limit for the VB editor in Excel 2003.
In the worse case scenario, I do want all those message boxes, as once the time has lapsed, I would chase it up and change the date sent to the date chased (thanks for the prompt, TMShucks).
If anyone can give me any pointers as to how to chop this code down to size, I would be really grateful.
Many thanks,
Mattbro

My goal is to have a workbook containing data entry sheets for 40 managers and one consolidation sheet to display and analyze the data.

My problem is that there are 40 managers. Will 40 sheets be too much data for the workbook to be effective? If not how can I pull data from one workbook into another automatically...without a macro?

I am trying to create pivot table with multiple ranges because ther is too
much data for 1 spreadshhet and I don't want to consolidate. I am selecting
datas from 3 different tabs and all are in the same formate with 7 columns
but have differenet numbers of rows.

when I select the ranges and create the pivot table the only options I have
to put into the Page header and body are Row, column and Value. This isn't
flexible enough I wanted to be able to slect from all the fields in the data
ranges.

Is there a solution? Have I done something wrong?

Thanks
Rick

Here is my data (in actuality, it's about 35,000 rows long and will be
growing at the rate of 1,000 rows per month) and it's in a sheet I've labeled
as "Format 1".

TYPE (column A) PERIOD (column B) VERSION (column C)
DS 02/2005 1
BS 02/2005 2
WS 03/2005 2
DS 02/2005 1

I want to be able to count all of the different combinations that occur
(i.e., count all of the "DS and 02/2005 and 1") and return them in a sheet
I've labeled "Results"

There is too much data for a pivot table and I know there's a solution using
sumproduct (or something like that), but I haven't been able to figure it
out.

Any ideas?

Thanks,

Jim

Thanks for the reply but i have one more question,
I need a cell that will combine the two functions. That means that i need a
cell that will give me lets say number 200 everytime that i have in another
cell "Orange2". My problem is that i have too much data for one formula of
lookup.

"Bob Phillips" wrote:

> Create a table in say M1:R100 to look like
>
> Tree Year1 Year2 Year3 Year4 Year5
> Orange 100 200 300 400 500
> Banana 300 400 450 500 700
>
> or whatvere values, and then just use
>
> =VLOOKUP("Orange",M1:R100, year+1,False)
>
> year+1 is 2 for one year old tress etc.
>
> --
> HTH
>
> Bob Phillips
>
> "Andycleos" <Andycleos@discussions.microsoft.com> wrote in message
> news:39A1D2B2-B38B-4348-8785-A9A07393376D@microsoft.com...
> > I have some trees and i want to get some prices for them in different
> years.
> > How can i do it?
> >
> > For example: Orange trees (OT) and bananas (BA) that are 1,2,3,4,5 years
> > old. The prices for every type of tree is different. OT-1year - $100,
> > OT-2Years - $200 and BA-1year - $300, BA-2years - $400.etc.
> > How can i make a commant that will recognize the type of tree, its age and
> > give me the price that i want.
> > Please i really need it.
>
>
>

Hi i have alot of values that i would like to display in a graph over time.

The problem is, is that there are many different times (irregular intervals) that the value is recorded during each day.

whenever i create a graph and choose the x-axis format as the date i get a spike at each day, which is no good!

value Date Date Time
0 22/12/2008 12:33:35 22/12/2008 12:33:35
0 22/12/2008 20:53:25 20:53:25
9 23/12/2008 00:48:48 23/12/2008 00:48:48
0 23/12/2008 06:58:28 06:58:28
8 23/12/2008 07:46:28 07:46:28
0 23/12/2008 07:48:28 07:48:28
0 23/12/2008 10:14:49 10:14:49
0 24/12/2008 02:05:50 24/12/2008 02:05:50
7 24/12/2008 02:22:26 02:22:26
1 24/12/2008 04:28:46 04:28:46
0 24/12/2008 04:30:46 04:30:46
0 24/12/2008 20:53:21 20:53:21
0 26/12/2008 20:57:15 26/12/2008 20:57:15
2 29/12/2008 03:15:02 29/12/2008 03:15:02
0 29/12/2008 03:17:02 03:17:02
6 29/12/2008 05:23:02 05:23:02

The best option i have so far is using columns C and D from above for the x-axis... this works exactly how i want, the only problem is i have too much data for the xaxis to be readable.

So what i really want is maybe dates along the x-axis and when i hold the mouse over the graph it will say the time??

Any Ideas??

Cheers

Here is my data (in actuality, it's about 35,000 rows long and will be
growing at the rate of 1,000 rows per month) and it's in a sheet I've labeled
as "Format 1".

TYPE (column A) PERIOD (column B) VERSION (column C)
DS 02/2005 1
BS 02/2005 2
WS 03/2005 2
DS 02/2005 1

I want to be able to count all of the different combinations that occur
(i.e., count all of the "DS and 02/2005 and 1") and return them in a sheet
I've labeled "Results"

There is too much data for a pivot table and I know there's a solution using
sumproduct (or something like that), but I haven't been able to figure it
out.

Any ideas?

Thanks,

Jim

Hi,
This is the first time that I post a question in this forum. I need to copy and paste a formula into a certain range of cells in column B (Number depends on how many non blank cells are there in column A). Simply copy and paste the formula into the whole column B will take too much time for Excel to work in the back end. How to do this in a macro? Thanks!!

Hi All,

Potentially a tricky one (not least because I might be going about
doing it in a bit of a backwards way)...

I've got a large amount of data in a MS Project file, which is too much
to be able to be able to use as a summary document. To this end, I've
created a series of macros which use the project data to prodcue
summary charts and graphs as required - this is done and dusted with...

The bit that remains, and that I'm struggling with, is how to simplify
the process. I want a single button, in excel, that when pushed does
the following:

1. Opens up hte relevant Project file
2. extracts the required fields (for example, name and start date) from
that project file and saves them elsewhere in the workbook.
3. closes back up the project file - and lets my other macros do their
magic on the resultant data...

To be honest, I can' t do any of these - but I'm particularly stumped
by 2. I've thought about trying to save the data in project as an .xls
(using a template as required), or getting excel to run some macro in
Project which could do the extract.... Any help with these or examples
of bits of code that might help would be fabulous!

Thanks in advance....

Chris

I'm a fairly experienced Excel-user but I've been pondering this problem for
days now so I hope anyone out there can help me. I'm from Holland so forgive
me for my English.

I work at the headquarters of a company that owns over 20 bookstores. Every
week I receive a large CSV-file with the weekly revenues(?). First thing I
do is to organize these into 10 productgroups, so my "Source-file" looks
like this:
Column A: Storename
Column B: Productgroup
Column C: Year
Column D: Weeknumber
Column E: Amount

In the weekly reports I make for the management, they want to see the weekly
revenues, compared with those of last year. No problem there; I've made a
pivot-table and graphs which do the job.

Here comes the problem:
Management has determined the budgets for this year for every store and
every productgroup. They also want to see in my reports the comparison
between the real revenue and the one budgetted. The weekly budget for this
year is calculated as follows:

For instanstance: the weekly budget for Store 1, Productgroup1, week 1 2005
is
(Week 1 2004 / Total 2004) * Budget 2005

It all seems very easy and in smaller files I've solved this problem with
Vertical Sum and VLOOKUP but when I try this on the real data, Excel keeps
getting stuck. I have over 200 rows a week, starting in 2003 and I guess
that's just too much to handle for my processor.

Does any know a "smarter" way to handle this?
Any help is much appreciated!

Bart Snel

Hi,

i have about 13000 rows in my excel spreadsheet. To sort data i have auto filter..however, whenever I use autofilter the spreadsheet takes too much time to filter out the information.

Also, this worksheet has formulas that take alot of time to update.

Does anyone know the reason for this and how to solve it?

Thanks,
Meg

Hi,

I'm currently using ASAP Utilities for Excel. What I use most
often is the "Advanced Character Removal" Tool under the Text Menu of
ASAP. It works great, However this Advanced Character Removal tool
completely FAILS once the individual field/cell sizes become too
large.
Some of the cells in my Excel have a LOT of Data (mostly
memos,words,email transcript(s),etc) within each of the indivudal
cells.

I have a bunch of 010 and 013 empty box characters and use ASAP to help
me get rid of them, however ASAP fails to erase anything from cells that
contain too much data. (Note: I have used MACROS to accomplish the same
thing, and again here once the cells get too large the same thing
happens, which makes me thing this is some kind of limitation/bug in MS
Excel itself!)

IS there anyway around this????

Here's a link to my previos post for clarification:

Lots of ideas from people have helped a lot, but now I'm stuck with
having too large fields, and I can't compromise the size, so what to
do?

Thanks,
Bo

--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=523549

Excel 2000 SP 1
When every record does not have content in the last column of the excel
spreadsheet - it produces the "too few data fields in record 16" error
message.

This is a recurring error in Excel upgrades -- some upgrades anticipate the
fact that there may/may not be data in the last field. Others, like Excel
2000 SP1 do not.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc


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