There's several ways to manage this.It LOOKS like you are identifying all
unique Key1/Key2 combinations.then, recording the minimum mindate and maximum
maxdate and totaling the totalitems.
copy Key1,Key2 to another set of columns and remove duplicates.combine key1 "."
key2 to create a "key" field.
sort the data by Key1,key2,Mindateuse vlookup to look up the mindatecopy/paste
special values to save mindate.
sort by Key1, Key2, Maxdate (descending)use vlookup to obtain the maxdate.
use sumif (or sumifs) to get totalitems.
-Now, 50,000 rows is a lot of rows and copying that many formulas is VERY slow.
I could easily write a short macro that would produce the summary.
Can you send me a sample file? or I could try to create a bunch of data to test.
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: Greg Della-Croce <greg_della-cr...@sil.org>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Saturday, October 10, 2015 8:33 AM
Subject: $$Excel-Macros$$ How to do a summary of a large table
Since I do no see a way to do this with just functions, I think I am going to
have to write some code to do this myself, but I would appreciate a word from
the experts (aka you people)
I have a table with just shy of 50,000 rows that looks something like this:
Key1 Key2 MinDate MaxDate TotalItemsA 1
10/1/2010 4/2/2011 12B 4 03/1/2009 09/10/2014
52C 24 01/2/2015 4/17/2015 08B 4
02/15/2008 06/03/2012 10
I need to summarize this down to:Key1 Key2 MinDate MaxDate
TotalItemsA 1 10/1/2010 4/2/2011 12B 4
02/15/208 09/10/2014 62C 24 01/2/2015
4/17/2015 08
I just can't see how to do this without reading all 50,000 rows 50,000 times
since VLookup will not return multiple records using a compound key. Add to
that I don't remember the process for reading each record in a table and
stopping at the end of the table. (Can you say it has been a looong time since
I wrote VBA code like this?)
Is there a kind soul out there that can push me in the correct direction with
some suggestions?
Thanks to everyone who takes the time to reply ahead of time! I do appreciate
it.
GregDC --
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
