Paul,
I played with your suggestion and now have a working/MacGyver'ed
together solution that will allow me to do my report. Thanks. I have
used that method before, I just had not thought to use it here. I
appreciate your offer to write the macro, but I was stubborn and worked it
out myself.
On Sunday, October 11, 2015 at 12:34:04 AM UTC-4, Paul Schreiner wrote:
>
> 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,Mindate
> use vlookup to look up the mindate
> copy/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_del...@sil.org <javascript:>>
> *To:* MS EXCEL AND VBA MACROS <excel-...@googlegroups.com <javascript:>>
> *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 TotalItems
> A 1 10/1/2010 4/2/2011 12
> B 4 03/1/2009 09/10/2014 52
> C 24 01/2/2015 4/17/2015 08
> B 4 02/15/2008 06/03/2012 10
>
> I need to summarize this down to:
> Key1 Key2 MinDate MaxDate TotalItems
> A 1 10/1/2010 4/2/2011 12
> B 4 02/15/208 09/10/2014 62
> C 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...@googlegroups.com <javascript:>.
> To post to this group, send email to excel-...@googlegroups.com
> <javascript:>.
> 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.
