Hi,
I have 2 spreadsheets, one with all IDs in each row, and one with some IDs alone in the row while others are in the same cell but separated by a forward slash. Example:
Spreadsheet 1:
Spreasheet 2:
I need to transfer over the values from Spreadsheet 1 to Spreadsheet 2. The only problem is that the account number in Spreadsheet 1 is all separated by row, while in Spreadsheet 2, some have their own row, and some are grouped together. The ones grouped together need to be summed together.
Here's the desired final output:
Spreadsheet 2
How can I achieve that in Excel? I started doing it by IFS(number between 51000 and 51003, then 51000/51002/51003/51500/51550/51030), but my spreadsheet is considerably big, so it's just taking a lot of my time. TIA.
I have 2 spreadsheets, one with all IDs in each row, and one with some IDs alone in the row while others are in the same cell but separated by a forward slash. Example:
Spreadsheet 1:
| Acct Num | Balance |
| 51000 | $3,435,303.24 |
| 51002 | $0.00 |
| 51003 | $75.00 |
| 51004 | $0.00 |
| 51025 | $15,163.88 |
| 51026 | $0.00 |
| 51030 | $0.00 |
| 51050 | ($150,510.92) |
| 51051 | $0.00 |
| 51055 | $40,912.78 |
| 51056 | $17,054.32 |
| 51075 | $149,358.89 |
| 51076 | $0.00 |
| 51500 | $0.00 |
| 51512 | ($625.29) |
| 51550 | $5,414.74 |
| 51555 | $0.00 |
Spreasheet 2:
| Acct Num |
| 51025 |
| 51000/51002/51003/51500/51550/51030 |
| 51050/51055/51056 |
| 51075 |
I need to transfer over the values from Spreadsheet 1 to Spreadsheet 2. The only problem is that the account number in Spreadsheet 1 is all separated by row, while in Spreadsheet 2, some have their own row, and some are grouped together. The ones grouped together need to be summed together.
Here's the desired final output:
Spreadsheet 2
| Acct Num | Balance |
| 51025 | $15,163.88 |
| 51000/51002/51003/51500/51550/51030 | $3,440,717.98 |
| 51050/51055/51056 | ($92,543.82) |
| 51075 | $149,358.89 |
How can I achieve that in Excel? I started doing it by IFS(number between 51000 and 51003, then 51000/51002/51003/51500/51550/51030), but my spreadsheet is considerably big, so it's just taking a lot of my time. TIA.
