VBA help to create summary table from a range of data.

[CorpsMan]

Hello, and thanks in advance for the help. I have been brooding over the code for this problem for a while now and am stuck. The problem:

I have data that comes in a 2 column table. "Date" and "Data Source" .



Date

Date Source



6/22/1922

Factored



6/23/1922

Factored



6/24/1922

Factored



6/25/1922

Observed



6/26/1922

Observed



6/27/1922

Observed



6/28/1922

Daily



6/29/1922

Daily



6/30/1922

Daily




I want to write a macro that will scroll down the list and for each unique data source give me it's range of dates. The example for the above table would be:



Data Source

Range of Dates



Factored
6/22/1922 to 6/24/1922



Observed

6/25/1922 to 6/27/1922



Daily

6/28/1922 to 6/30/1922




I am a novice at writing VBA code so I ran into a bunch of problems just thinking about where to start with this code. Where would people more experienced in VBA start with this? Are there any macros or functions that you know about that would help me write this script? Any and all help is very much appreciated. Thank you.

[Bob Phillips]

Just create a pivot table from the data, copy the date to the values pane twice, one with a min setting, one with a max.

[CorpsMan]

Just create a pivot table from the data, copy the date to the values pane twice, one with a min setting, one with a max.

Thank you for the quick response. I will definitely look into pivot tables, haven't heard of those before. I should've added one extra note though. the "data source" column can go from "observed" to "daily" and then back to "observed" any number of times and for different lengths throughout the whole period of record.

I could doctor the data so the pivot table will work for chunks of it. That's already infinitely better than what I have been doing. Thank you again.

[Bob Phillips]

I created a pivot table from your data and got the results you wanted without any doctoring.

[CorpsMan]

I created a pivot table from your data and got the results you wanted without any doctoring.

I can't seem to find the right options that will make it work. Here's an example from my spreadsheet:

1506315064

My pivot table gives me the absolute min and max for a specific data source. Is there a combination of options that would allow me to capture each separate min/max?

For example the first 3 lines for this spreadsheet would be:



Grand Gallatin MOVE.1
6/30/1921

8/5/1921



Marmaton MOVE.1

8/6/1921

6/6/1925



Grand Gallatin MOVE.1

6/7/1925

4/15/1929




Thank you once more. The help has been very helpful!

[Bob Phillips]

Is there a combination of options that would allow me to capture each separate min/max?

For example the first 3 lines for this spreadsheet would be:



Grand Gallatin MOVE.1
6/30/1921

8/5/1921



Marmaton MOVE.1

8/6/1921

6/6/1925



Grand Gallatin MOVE.1

6/7/1925

4/15/1929




The only way that I could think of doing it was to add a helper column to the original data that grouped the data sources, an incrementing id per group, and then include this group id in the pivot. This is the formula I came up with


=IF(B2=B1,C1,IFERROR(VLOOKUP($B2,$B$1:$C1,2,FALSE),0)+1)

[CorpsMan]

The only way that I could think of doing it was to add a helper column to the original data that grouped the data sources, an incrementing id per group, and then include this group id in the pivot. This is the formula I came up with


=IF(B2=B1,C1,IFERROR(VLOOKUP($B2,$B$1:$C1,2,FALSE),0)+1)

Excellent, excellent solution. I couldn't get the formula you posted above to work out because I wasn't sure how to update the references. But it reminded me of my first attempt at solving this problem. Early on I made a column with this formula:

=IF(C4=C3,D3,D3+1)

If the data source is the same, the helper column will be the same number. When the data source changes the helper column increases by one. Adding this column into the pivot table worked exactly as you described. Thank you very much for your input on this problem. It's a good feeling when a solution is found. Thank you again xld.

[Bob Phillips]

Good! I think in hindsight that mine was unnecessarily complex, I re-seed the id for each new data source as well as incrementing it for each group. Your KISS approach is perfectly sufficient here :)

If you want to try mine, just to see how it differs, I think that it would be as follows, adjusted by what I glean from your formula


=IF(C4=C3,D3,IFERROR(VLOOKUP($C4,$C$3:$D3,2,FALSE),0)+1)