Crazy If
 

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

Crazy If
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"LiveUser" wrote:
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