table grouping

[Mark]

Hi all,

I have a 2-dimensional table with 234 columns and 604 rows. Column headings
are A011100, A011200, ... E425100, E425900; row headings are 11111, 11112,
.... 91513, 91514. (In case you're interested, column headings represent
industries, row headings represent occupations, and table data is of
employment).

I want to be able to condense this table on the basis of the leading
characters of the row and column headings. In other words, I'd like to end
up with a table with, for example, column headings A, B,...E and row
headings 11, 12, ...84, 91; in this aggregated table, cell 11A would
contain the sum of all values in cells from the original table with column
headings A011100 - A011990 and row headings 11111 - 11411 (and so on for
cells B11-E91).

Can anybody point me in the right direction?

Thanks in advance,
Mark

 

[Carim]

Hi Mark,

Why don't you go and try a pivot table and play around with the sorting
....?

HTH
Carim

 

[Mark]

Thanks Carim.

I have since hit upon one solution using successive SUMIF calculations
(one to aggregate columns followed by one to aggregate rows). I was
looking for something a little more elegant, but since it's the result
that matters the most, I'll stick with it.

Why don't you go and try a pivot table and play around with the sorting
...?

Re pivot tables: I could be wrong, but I thought they were useful only
for data organised in columns? So in my case I could use a pivot table if
my data were:

Industry Occupation Value
A011100 11111 45
A011100 11112 780
A011200 11111 477
A011200 11112 63
etc

Instead I have:

A011100 A011200 ...
11111 45 477 ...
11112 780 63 ...
.... ... ...

which I'm not sure that a pivot table can cope with (though I'd happily
be proved wrong).


Mark

 

[Rowan]

I'm pretty sure this won't qualify as being elegant but it seems to give
the require result:

Sub Smrse()
Dim eRow As Long
Dim eCol As Integer
Dim cell As Range
Dim usdRange As Range
Dim dataSht As Worksheet
Dim newSht As Worksheet
Dim fRng As Range
Dim colH As String
Dim rowH As String
Dim tRow As Long
Dim tCol As Integer
Dim fRow As Long
Dim fCol As Integer
Dim tempCol As Integer
Dim tempRow As Integer

Set dataSht = ActiveSheet
Set newSht = Sheets.Add
newSht.Name = "Report"
tRow = 2
tCol = 2

With dataSht
eRow = .Cells(Rows.Count, 1).End(xlUp).Row
eCol = .Cells(1, Columns.Count).End(xlToLeft).Column
Set usdRange = .Range(.Cells(2, 2), .Cells(eRow, eCol))
For Each cell In usdRange
colH = Left(.Cells(1, cell.Column).Value, 1)
rowH = Left(.Cells(cell.Row, 1).Value, 2)
With newSht.Rows(1)
Set fRng = .Find(colH)
End With
If fRng Is Nothing Then
newSht.Cells(1, tCol).Value = colH
tempCol = tCol
tCol = tCol + 1
Else
tempCol = fRng.Column
Set fRng = Nothing
End If
With newSht.Columns(1)
Set fRng = .Find(rowH)
End With
If fRng Is Nothing Then
newSht.Cells(tRow, 1).Value = rowH
tempRow = tRow
tRow = tRow + 1
Else
tempRow = fRng.Row
Set fRng = Nothing
End If
newSht.Cells(tempRow, tempCol).Value = _
newSht.Cells(tempRow, tempCol).Value + cell.Value
Next cell
End With

End Sub

Regards
Rowan

 

[Mark]

Thanks Rowan. Just what I was looking for - and way nicer than my code.

Mark