From: BDT on 4 Jun 2010 17:13 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 4 Jun 2010 18:21 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 5 Jun 2010 10:01 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 >
|
Pages: 1 Prev: Printing in Color Next: Set row height, but allow wrap text |