From: BDT on
Hi,

I need to consolidate some lines of data in a 19K line spreadsheet and then
change its basic format.

I have 3 columns of data: ID# | date | volume

My problem is that while the data is mostly monthly, there are some months
with two entries and I need to consolidate them into a single monthly value.
I don't care which of the dates is used as long as the month and year are
correct. That's the 'consolidate' part.

Then I need to convert the 19,000 row spreadsheet to a big grid with the
meters listed in the left row and the month/year at the top of each column
with the appropriate volumes in the body of the table. This is the 'format'
part and I suspect that I can use Access to do this, but I thought I would
ask for input here, too.

thanks
From: Don Guillett on
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"BDT" <BDT(a)discussions.microsoft.com> wrote in message
news:E22129C5-4D37-4647-BF43-114FD95E25D9(a)microsoft.com...
> Hi,
>
> I need to consolidate some lines of data in a 19K line spreadsheet and
> then
> change its basic format.
>
> I have 3 columns of data: ID# | date | volume
>
> My problem is that while the data is mostly monthly, there are some months
> with two entries and I need to consolidate them into a single monthly
> value.
> I don't care which of the dates is used as long as the month and year are
> correct. That's the 'consolidate' part.
>
> Then I need to convert the 19,000 row spreadsheet to a big grid with the
> meters listed in the left row and the month/year at the top of each column
> with the appropriate volumes in the body of the table. This is the
> 'format'
> part and I suspect that I can use Access to do this, but I thought I would
> ask for input here, too.
>
> thanks

From: Don Guillett on
Sent OP this

Option Explicit
Sub SAS_MakeTable()
Dim lr As Long
Dim c As Range
Dim mm As Double
Dim mr As Long
Dim mc As Double

Application.ScreenUpdating = False

lr = Cells(Rows.Count, "b").End(xlUp).Row
Range("f3:r" & lr).ClearContents

Range("B2:B" & lr).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("F2"), Unique:=True

For Each c In Range("b4:b" & lr)
mm = Month(c.Offset(, 1))
mr = Columns(6).Find(What:=c, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
'MsgBox mr
mc = Rows(2).Find(What:=mm, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
'MsgBox mc
Cells(mr, mc) = Cells(mr, mc) + c.Offset(, 2)
Next c
Application.ScreenUpdating = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Don Guillett" <dguillett1(a)gmail.com> wrote in message news:...
> If desired, send your file to my address below. I will only look if:
> 1. You send a copy of this message on an inserted sheet
> 2. You give me the newsgroup and the subject line
> 3. You send a clear explanation of what you want
> 4. You send before/after examples and expected results.
>
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett(a)gmail.com
> "BDT" <BDT(a)discussions.microsoft.com> wrote in message
> news:E22129C5-4D37-4647-BF43-114FD95E25D9(a)microsoft.com...
>> Hi,
>>
>> I need to consolidate some lines of data in a 19K line spreadsheet and
>> then
>> change its basic format.
>>
>> I have 3 columns of data: ID# | date | volume
>>
>> My problem is that while the data is mostly monthly, there are some
>> months
>> with two entries and I need to consolidate them into a single monthly
>> value.
>> I don't care which of the dates is used as long as the month and year are
>> correct. That's the 'consolidate' part.
>>
>> Then I need to convert the 19,000 row spreadsheet to a big grid with the
>> meters listed in the left row and the month/year at the top of each
>> column
>> with the appropriate volumes in the body of the table. This is the
>> 'format'
>> part and I suspect that I can use Access to do this, but I thought I
>> would
>> ask for input here, too.
>>
>> thanks
>