chillinsf49
New Member
- Joined
- Dec 15, 2018
- Messages
- 11
Hello,
I am have an urgent task for work and have gone through the various threads on this site, but, I could not find anything. I have to be able to quickly sum the total based on multiple criteria and remove the duplicate rows (Tab "Data". Then only certain columns are copied over to a worksheet in the same workbook (Tab "Output). There are about 100,000 rows with 40 columns, so using a SUMIF formula is not feasible since it takes up alot of memory.
For example:
There are 8 columns in Tab "Data". I would like to sum the Expense and HC columns if it meets these criteria: Date, Dept ID, Unit, and Acct Num. Then I would like to copy only a few columns from the Tab "Data" to a new sheet called "Output" in the same workbook. The new fields I need in Tab "Output" are Date, Dept ID, Unit, Acct Num, Expense, and HC. The Tab "Output" contains only 12 rows because there are 2 sets in Tab "Data" that matched the criteria; therefore, the Expense and HC values are summed and shown as one row.
Thank you advance for your help!
Tab "Data" - Columns A2 through H14
<tbody>
</tbody>
Tab "Output" - Columns A2 through F12
Date Dept ID Unit Acct Num Expense HC
Jan 31, 2018 1111 ABC 24353 $54 -
Jan 31, 2018 1111 ABC 79810 $81 -
Jan 31, 2018 1111 ABC 80545 $77 -
Jan 31, 2018 1111 XYZ 87342 ($2,991) 7.0
Jan 31, 2018 1111 EFG 80555 $0 -
Jan 31, 2018 1111 ABC 21930 $3,626 -
Jan 31, 2018 1111 EFG 21930 $1,000 -
Jan 31, 2018 1111 EFG 97897 $594 -
Feb 28, 2018 2222 XYZ 49183 $93,574 6.0
Feb 28, 2018 2222 ABC 53421 $34 -
I am have an urgent task for work and have gone through the various threads on this site, but, I could not find anything. I have to be able to quickly sum the total based on multiple criteria and remove the duplicate rows (Tab "Data". Then only certain columns are copied over to a worksheet in the same workbook (Tab "Output). There are about 100,000 rows with 40 columns, so using a SUMIF formula is not feasible since it takes up alot of memory.
For example:
There are 8 columns in Tab "Data". I would like to sum the Expense and HC columns if it meets these criteria: Date, Dept ID, Unit, and Acct Num. Then I would like to copy only a few columns from the Tab "Data" to a new sheet called "Output" in the same workbook. The new fields I need in Tab "Output" are Date, Dept ID, Unit, Acct Num, Expense, and HC. The Tab "Output" contains only 12 rows because there are 2 sets in Tab "Data" that matched the criteria; therefore, the Expense and HC values are summed and shown as one row.
Thank you advance for your help!
Tab "Data" - Columns A2 through H14
Date | Program | Dept ID | Unit | Acct Level 1 | Acct Num | Expense | HC |
Jan 31, 2018 | Technology | 1111 | ABC | Emp Related | 24353 | $54 | - |
Jan 31, 2018 | Technology | 1111 | ABC | Postage/Courier Service | 79810 | $81 | - |
Jan 31, 2018 | Technology | 1111 | ABC | Travel and Other | 80545 | $77 | - |
Jan 31, 2018 | Technology | 1111 | XYZ | Travel | 87342 | ($2,991) | 2.0 |
Jan 31, 2018 | Technology | 1111 | XYZ | Travel | 87342 | $0 | 5.0 |
Jan 31, 2018 | Technology | 1111 | EFG | Travel and Misc | 80555 | $0 | - |
Jan 31, 2018 | Technology | 1111 | ABC | Bonus | 21930 | $3,626 | - |
Jan 31, 2018 | Technology | 1111 | EFG | Bonus | 21930 | $1,000 | - |
Jan 31, 2018 | Technology | 1111 | EFG | Bonus | 97897 | $594 | - |
Feb 28, 2018 | Technology | 2222 | XYZ | Management & Supervision | 49183 | $81,688 | 5.0 |
Feb 28, 2018 | Technology | 2222 | XYZ | Management & Supervision | 49183 | $11,887 | 1.0 |
Feb 28, 2018 | Technology | 2222 | ABC | Housekeeping Supply | 53421 | $34 | - |
<tbody>
</tbody>
Tab "Output" - Columns A2 through F12
Date Dept ID Unit Acct Num Expense HC
Jan 31, 2018 1111 ABC 24353 $54 -
Jan 31, 2018 1111 ABC 79810 $81 -
Jan 31, 2018 1111 ABC 80545 $77 -
Jan 31, 2018 1111 XYZ 87342 ($2,991) 7.0
Jan 31, 2018 1111 EFG 80555 $0 -
Jan 31, 2018 1111 ABC 21930 $3,626 -
Jan 31, 2018 1111 EFG 21930 $1,000 -
Jan 31, 2018 1111 EFG 97897 $594 -
Feb 28, 2018 2222 XYZ 49183 $93,574 6.0
Feb 28, 2018 2222 ABC 53421 $34 -