LinkBack URL
About LinkBacks
want to be able to take a large quantity of data, sort all the like data together, and then quantify the number of each like data. I need the equations to do that. Any help would be great! Thanks
want to be able to take a large quantity of data, sort all the like data together, and then quantify the number of each like data. I need the equations to do that. Any help would be great! Thanks
Last edited by imcold; 01-05-2009 at 12:42 AM.
1. Use MS Access for large amounts of data (you don't say how much)
2. Grouping and summarising data is best done with pivot tables, you can create a pivot table in Excel directly from a database in Access (without the need to import the data and kill your computer)
3. Don't use equations for this
HTH
If you need to sort text, maybe this article Sorting Text in Excel using Formulas can be helpful.
Hi,
I would be sorting around 5000 entry's with about 1000 or so different pieces of(each no more than 11 characters) text. I currently don't have ms access, but I will buy it asap, if that is what I need. It wouldn't be a real issue but I need to do this on a daily basis, and it is killing my eyes.
Thanks again.
I'm not sure what you mean by "5000 entry's with about 1000 or so different pieces of(each no more than 11 characters) text". How many rows and columns are you dealing with here? An Excel 2003 sheet is about 65000 rows high and 256 columns wide; if your data exceed (or will eventually exceed) that, you would have to spread it over multiple sheets (which can get very messy, not to mention extremely burdensome from a memory standpoint), and you would want to use something like Access. If you will additionally have a large volume of cells containing formulas, you will run into problems more quickly with Excel.
So, obviously if your dataset will require 1000 columns (or 5000 * 1000 = 5,000,000 rows, for that matter), you will need different software. I believe Access has about a 2GB file size limit; beyond that, and it's time for you to hire the SAS, SQL Server, or Oracle person you've been dreaming of....
Sorry for the confusion. I would only need to sort 1 column. Roughly from A1-A5000. then in those spots the information would have around a 1000 different entries. After the sorting of column "a" I would then need to sum each group. Ex:
a1(777777)
a2(444898)
a4(777777)
a5(666666)
a6(444898)
What I need to end up with is "777777"=2, 444898=2, 666666=1
or something similar. again sorry for the confusion
OK... there are more efficient ways of doing this, but here's what I like to do:
-- there's no need to sort your data with this method, by the way, but if you like, just highlight Col A by clicking the "A" that appears at the top of the column, go to the Data menu --> Sort... --> select "Header Row" from the bottom if not already selected (assuming you have some kind of title for Col A in Row 1), click OK.
Then, as for your other problem...
insert a column to the left of Col A (your data are now in Col B).
in the new Col A in the cell to the left of your first row of data, type this formula (your data should start on row 2-- if not, you would either need to insert/delete rows or edit this formula-- in any case, do not start on Row 1).
in A2, type this:
=IF(COUNTIF(B$1:B1,B2),"",MAX(A$1:A1)+1)
Fill this formula down all 5000 or so rows (to fill down, highlight the cell with the formula in it, place the mouse pointer over the lower right hand corner of the cell until the cursor turns into a small black + sign, and then double click-- if this fails to fill all the way down, do the same thing again, but click and drag down instead of double-clicking).
Over to the right somewhere that you have space (for instance, if Column D is empty, use that), type this formula in row 2:
=IF(ROWS($1:1)>MAX(A:A),"end",VLOOKUP(ROWS($1:1),A:B,2,FALSE))
Fill this down as far as necessary-- if you expect that you have around 1000 unique records in Col B, fill down 1000 or more rows (the actual number will be the max number in Col A). This formula will list all the unique records and will fill remaining rows with the word "end" to indicate you've gone far enough.
in E2 (directly to the right of the above formula), type this:
=COUNTIF(B:B,D2)
fill down by the double-click method I described before. You now have a table of all unique records and a count of each one. Hope that's what you were looking for.
Last edited by clownfish; 01-03-2009 at 11:00 PM.
I tried that way multiple times and it says I have a "circular reference"
Post an example workbook?
Entia non sunt multiplicanda sine necessitate
here's a quick example of the solution I described earlier. Your numbers are under the green header (I just simulated some numbers with the random number function-- if you press F9, they will change, along with the counts), and the parts I added are under blue. The table with the counts is highlighted in yellow-- I *think* this is what you were after.
Take a look at the formulas and see if yours match. Note that you shouldn't have anything else in Column B besides the numbers you want to count and sort. There should also be nothing besides the counter formula I gave in Column A. Can't think of why else you'd be getting a circular reference.
If you want to just keep the table (in yellow) and lose the formulas, copy the table, then right-click --> Paste Special... --> Values --> OK
Thank a million I got it to work. Well, actually just copied your example
If you're a happy camper, would you please mark your thread as Solved?
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks