Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / January 2008

Tip: Looking for answers? Try searching our database.

Crazy If

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
LiveUser - 29 Jan 2008 20:51 GMT
Looking through multiple worksheets:

I multiple worksheets (at least . Each worksheets has the same data and
different data.

Example:
(Sheet 1)

    A               B              C
1   Car             1              49
2   Truck                          54
3   Bear           6               5
4   Ralph          9              4

(Sheet 2)
   
    A              B             C
1  Car             2              5
2  Truck         55             4
3  Train          42             5
4   Ralph          3             8

I need to copy information into the new summary sheet from Column A. But, I
can't have duplicates. Something like If in A of all worksheets then copy
into summary A and If duplicate don't copy.

I then need to transfer the row data.

If data in column A of worksheets matches Column A of summary worksheet then
what is the sum of the information that matches in that row and column and
placed in the matching row and column of the summary worksheet.

So this (detail):

(Sheet 1)

    A               B              C
1   Car             1              49
2   Truck                          54
3   Bear           6               5
4   Ralph          9              4

(Sheet 2)
   
    A              B             C
1  Car             2              5
2  Truck         55             4
3  Train          42             5
4   Ralph          3             8

Should look like this (summary):

     A                B               C
1   Car               3             54
2   Truck          55             58
3   Train           42             5
4   Ralph          12             12
5   Bear             6              5
Max - 30 Jan 2008 14:44 GMT
Don't think it's possible via formulas

This approach might work:
1. Use a sub by Ron to merge data from all source sheets
2. Create a pivot table to get the unique listing and corresponding sums

For (1), go to Ron's page at:
http://www.rondebruin.nl/copy2.htm
Look for: Copy from row 2 till the last row with data
Copy n paste into a module
a. Sub Test2()
b. Function LastRow(sh As Worksheet)

Before running the sub, group all source sheets and insert a top row for col
labels and put in the labels. Run the Sub Test2().

Then in the MergeSheet, insert a top row, put in the 3 col labels. Create a
pivot on the data in the merged sheet, drop col A label in ROW area, cols B &
C labels in the DATA area (SUM). Then go to the pivot sheet, drag DATA and
drop over "Total", and you'd get the desired results.  
Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Looking through multiple worksheets:
>
[quoted text clipped - 54 lines]
> 4   Ralph          12             12
> 5   Bear             6              5
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.