Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CONCATENATE CRAZY! | Excel Worksheet Functions | |||
Loop gone crazy | Excel Discussion (Misc queries) | |||
I'm going crazy here | Excel Discussion (Misc queries) | |||
crazy mouse? | Excel Discussion (Misc queries) | |||
This is crazy!!! | Excel Discussion (Misc queries) |