Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you add an Excel spread sheet to a Microsoft Word report? | Excel Worksheet Functions | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) | |||
How do I use multiple worksheets (List) in a Pivot Table Report? | Excel Worksheet Functions | |||
compare unique identifiers in multiple ranges | Charts and Charting in Excel | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |