Compare records in multiple sheet -> report

Hi!

I have a workbook consisting of ten sheets. To simplify my
question letīs say that the three first columns of every
sheet denotes the spatial coordinates x,y,z and the fourth
column is a scalar value. Some x,y,z-triplets exist in all
10 sheets, some exists in only a few sheets, if the triplet
exists, then also the scalar value of the 4th column exists.
What I would like to do is to find all unique x,y,z-triplets
and show them in the first column of a new sheet. In columns
2-11, I would like to show the scalar value(from the
corresponding x,y,z-triplet of course) in column 4 in
sheet 1-10 (if it exists). Is there an easy way to achieve
something like this?

/PB

--
A: Because it messes up the order in which people normally
read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

Thus spake <PB>:

> Hi!
>
> I have a workbook consisting of ten sheets. To simplify my
> question letīs say that the three first columns of every
> sheet denotes the spatial coordinates x,y,z and the fourth
> column is a scalar value. Some x,y,z-triplets exist in all
> 10 sheets, some exists in only a few sheets, if the triplet
> exists, then also the scalar value of the 4th column exists.
> What I would like to do is to find all unique x,y,z-triplets
> and show them in the first column of a new sheet. In columns
> 2-11, I would like to show the scalar value(from the
> corresponding x,y,z-triplet of course) in column 4 in
> sheet 1-10 (if it exists). Is there an easy way to achieve
> something like this?
>
> /PB

Oh, and I forgot to mention that I use Excel 2002 on Win XP

--
A: Because it messes up the order in which people normally
read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

if you ony need to do it once.

Select data in the first four columns of sheet 1
copy and paste into Sheet 11
select the data for the first three columns of sheet 2 and copy and paste
under the data in shheet 11.
Select the data in column 4 on sheet 2 and paste in the appropriate rows in
the fifth column in sheet 11
in sequence for the remaining sheets select all the data and paste under the
data in sheet 11. then cut the new information in column 4 and move it one
column past what was done for the previous sheet.
Note( depending on whether you have equations in dfferent places), You may
have to do the pasting as paste special Values

if you really want the triplet in one column, insert a new column 4 and
enter
=A1&"-"&B1&"-"&C1 or whatever format you want for the combined triplet
Copy column 4 and paste special on top of itself
Delete columns A B and C
Select All and sort by column A
Make sure you have a header row.
In M2 enter
=if(A2<>A1,Countif(A:A,A2,"")
copy down to the end of the data
in N2 enter
=if($M2<>"",sum(offset(n2,0,-12,$M2,1),"")
copy accross to W2 and down to the bottom of your data.
copy columns N:W and paste special values on top of Columns B:K
Use auto filter on column M and select blanks delete all visible rows below
the header row. Get out of autofilter and delete column M

If you have to do it a bunch, there are different levels of macros that you
could use.
The simplest would be to select a number of rows greater than you would
expect to see in any one sheet, and record a macro doing something like done
in this posting but copying and pasting the number of rows selected instead
of just the data I would change the equation for the M column to
=if(A2="","",if(A2<>A1,Countif(A:A,A2,""))

need less to say there are many ways to do this.

"PB" wrote:

> Thus spake <PB>:
>
> > Hi!
> >
> > I have a workbook consisting of ten sheets. To simplify my
> > question letÂīs say that the three first columns of every
> > sheet denotes the spatial coordinates x,y,z and the fourth
> > column is a scalar value. Some x,y,z-triplets exist in all
> > 10 sheets, some exists in only a few sheets, if the triplet
> > exists, then also the scalar value of the 4th column exists.
> > What I would like to do is to find all unique x,y,z-triplets
> > and show them in the first column of a new sheet. In columns
> > 2-11, I would like to show the scalar value(from the
> > corresponding x,y,z-triplet of course) in column 4 in
> > sheet 1-10 (if it exists). Is there an easy way to achieve
> > something like this?
> >
> > /PB
>
> Oh, and I forgot to mention that I use Excel 2002 on Win XP
>
> --
> A: Because it messes up the order in which people normally
> read text.
> Q: Why is top-posting such a bad thing?
> A: Top-posting.
> Q: What is the most annoying thing on usenet and in e-mail?
>

Thus spake <bj>:

> if you ony need to do it once.
>
> Select data in the first four columns of sheet 1
> copy and paste into Sheet 11
> select the data for the first three columns of sheet 2 and copy and paste
> under the data in shheet 11.
> Select the data in column 4 on sheet 2 and paste in the appropriate rows in
> the fifth column in sheet 11
> in sequence for the remaining sheets select all the data and paste under the
> data in sheet 11. then cut the new information in column 4 and move it one
> column past what was done for the previous sheet.
> Note( depending on whether you have equations in dfferent places), You may
> have to do the pasting as paste special Values
>
> if you really want the triplet in one column, insert a new column 4 and
> enter
> =A1&"-"&B1&"-"&C1 or whatever format you want for the combined triplet
> Copy column 4 and paste special on top of itself
> Delete columns A B and C
> Select All and sort by column A
> Make sure you have a header row.
> In M2 enter
> =if(A2<>A1,Countif(A:A,A2,"")
> copy down to the end of the data
> in N2 enter
> =if($M2<>"",sum(offset(n2,0,-12,$M2,1),"")
> copy accross to W2 and down to the bottom of your data.
> copy columns N:W and paste special values on top of Columns B:K
> Use auto filter on column M and select blanks delete all visible rows below
> the header row. Get out of autofilter and delete column M
>
> If you have to do it a bunch, there are different levels of macros that you
> could use.
> The simplest would be to select a number of rows greater than you would
> expect to see in any one sheet, and record a macro doing something like done
> in this posting but copying and pasting the number of rows selected instead
> of just the data I would change the equation for the M column to
> =if(A2="","",if(A2<>A1,Countif(A:A,A2,""))
>
> need less to say there are many ways to do this.
>
> "PB" wrote:
>
>> Thus spake <PB>:
>>
>>> Hi!
>>>
>>> I have a workbook consisting of ten sheets. To simplify my
>>> question letīs say that the three first columns of every
>>> sheet denotes the spatial coordinates x,y,z and the fourth
>>> column is a scalar value. Some x,y,z-triplets exist in all
>>> 10 sheets, some exists in only a few sheets, if the triplet
>>> exists, then also the scalar value of the 4th column exists.
>>> What I would like to do is to find all unique x,y,z-triplets
>>> and show them in the first column of a new sheet. In columns
>>> 2-11, I would like to show the scalar value(from the
>>> corresponding x,y,z-triplet of course) in column 4 in
>>> sheet 1-10 (if it exists). Is there an easy way to achieve
>>> something like this?
>>>
>>> /PB
>>
>> Oh, and I forgot to mention that I use Excel 2002 on Win XP
>>
>> --
>> A: Because it messes up the order in which people normally
>> read text.
>> Q: Why is top-posting such a bad thing?
>> A: Top-posting.
>> Q: What is the most annoying thing on usenet and in e-mail?
>>

Thanks bj!

Iīll give it a go tomorrow. I do need a more general
approach so Iīll use your ideas and write a macro for the
task.

Cheers

PB
--
A: Because it messes up the order in which people normally
read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?