Summarising large amt of data across sheets

[HydroXidE]

Hi,

Ok .. we deal with large amounts of data across multiple sheets. W
have data in this form

SHEET 1 a b c d e f
1 a1 b1 c1 d1 e1 f1
2 a2 b2 c2 d2 e2 f2
3 a3 b3 c3 d3 e3 f3
4 a4 b4 c4 d4 e4 f4
5 a5 b5 c5 d5 e5 f5
6 a6 b6 c6 d6 e6 f6
7 a7 b7 c7 d7 e7 f7

and

SHEET 2 a b c d e f
1 aa1 bb1 cc1 dd1 ee1 ff1
2 aa2 bb2 cc2 dd2 ee2 ff2
3 aa3 bb3 cc3 dd3 ee3 ff3
4 aa4 bb4 cc4 dd4 ee4 ff4
5 aa5 bb5 cc5 dd5 ee5 ff5
6 aa6 bb6 cc6 dd6 ee6 ff6
7 aa7 bb7 cc7 dd7 ee7 ff7
8 aa8 bb8 cc8 dd8 ee8 ff8

Now, we use this format because we have many such rows and colums (i'v
used only 8 rows and 6 columns for simplicity), and its very easy for u
to update this when it is in this format.

The problem is, we also need to compare how the values have change
across sheets, by having them one next to each other. Example: Chec
the status of b3 and bb3. As we have many such sheets, we thought thi
format could be presentable as a summary

SUMMARY

SHEET1 SHEET2
1
a a1 aa1
b b1 bb1
c
d
e
f

2
a
b
c
d
e
f

 

[Guest]

Do you have a question?

 

[DNF Karran]

Simplest way to analyse the data would be to do it in situ. ie:

for numeric info
=Sheet1!A1-Sheet2!A1

For non numeric
=Sheet1!A1&" - "&Sheet2!A1

or
=IF(Sheet1!A1=Sheet2!A1,"",Sheet1!A1&" - "&Sheet2!A1)

Or maybe look at how the source data is formated- if you really do hav
huge amounts of data access queries would be the way to go

 

[Dave Peterson]

If you're comparing address by address (and only two worksheets at a time),
you could use Myrna Larson and and Bill Manville's addin.

Here's a link to the program (kept at Chip Pearson's site):
http://www.cpearson.com/excel/whatsnew.htm
look for compare.xla

 

[HydroXidE]

*If you're comparing address by address (and only two worksheets at
time),
you could use Myrna Larson and and Bill Manville's addin.

Here's a link to the program (kept at Chip Pearson's site):
http://www.cpearson.com/excel/whatsnew.htm
look for compare.xla

Dave Peterson
(e-mail address removed) *

Thanks... but comparison is not really my issue .. I wanted to know ho
I could read the 1,2,3 and a,b,c,d and put them in rows .. such that w
have 1,a,b,c,d..., 2,a,b,c,d .. . etc ..in each row .. then on the nex
column i would have to read the value of the respective combinatio
from the first sheet , then on the next column ,, from the next shee
and so on .. I wanted to know how to automatically read all the value
and present them in this format .. Calculation is not necessary, but i
should be more like a status table .. thats all

Cheer

 

[Dave Peterson]

You're limited to 256 columns, so if you're going to create a summary for 32
worksheets, you could only look at 8 columns.

But if you know that you won't exceed that 256 column limit, you could copy
column A of each sheet, then column B, then column C....

If that works for you, how about:

Option Explicit
Sub testme()

Dim newWks As Worksheet
Dim wks As Worksheet
Dim wCtr As Long
Dim iCol As Long
Dim oCol As Long
Dim rngToCopy As Range
Dim FirstCol As Long
Dim LastCol As Long

Set newWks = Worksheets.Add

iCol = 0
oCol = 0

FirstCol = 2
LastCol = 5

For iCol = FirstCol To LastCol
For wCtr = 1 To Worksheets.Count
Set wks = Worksheets(wCtr)
With wks
If .Name = newWks.Name Then
'do nothing
Else
oCol = oCol + 1
newWks.Cells(1, oCol).Value _
= "'" & .Name & vbLf & .Columns(iCol).Address(0, 0)
Set rngToCopy = .Range(.Cells(1, iCol), _
.Cells(.Rows.Count, iCol).End(xlUp))
newWks.Cells(2, oCol) _
.Resize(rngToCopy.Rows.Count, 1).Value _
= rngToCopy.Value
End If
End With
Next wCtr
Next iCol

End Sub