Crazy If

L

LiveUser

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
 
M

Max

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top