Hello all,
I tried searching for a thread that addresses this question, but I was not able to locate one. I may not have worded my search correctly.
I receive a hospital's accounting trial balance that has hospital department numbers and names in row 1 and 2 and account type numbers and names in column A and B.
What I want to do is the following:
1. Combine each department number with each account type number with a dash in the middle.
2. Combine each department name with each account type name with a dash in the middle.
3. Pull the balance where the department and account type intersect.
See below for a visual example that is likely easier to understand.
What I receive:
<tbody>
</tbody>
The result I would like to get to:
<tbody>
</tbody>
I'm trying to find the least time consuming way to get to the end result above. I would be open to solutions using formulas, VBA (very little experience, but will work with this if there is a solution using VBA), or Power Query. Also please let me know if there is a current thread that addresses this question.
OS: Windows 7
Excel : Excel 2010 with Power Query Addin installed
Thank you.
Brennan<body id="cke_pastebin" style="position: absolute; top: 566px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
<tbody>
</tbody></body>
I tried searching for a thread that addresses this question, but I was not able to locate one. I may not have worded my search correctly.
I receive a hospital's accounting trial balance that has hospital department numbers and names in row 1 and 2 and account type numbers and names in column A and B.
What I want to do is the following:
1. Combine each department number with each account type number with a dash in the middle.
2. Combine each department name with each account type name with a dash in the middle.
3. Pull the balance where the department and account type intersect.
See below for a visual example that is likely easier to understand.
What I receive:
| 10 | 20 | 30 | 40 | ||
| Administration | Surgery | Laboratory | Radiology | ||
| 6000 | Salaries | 200,000 | 150,000 | 100,000 | 75,000 |
| 7000 | FICA Tax | 25,000 | 15,000 | 10,000 | 7,500 |
| 8000 | Benefits | 10,000 | 5,000 | 0 | 2,000 |
<tbody>
</tbody>
The result I would like to get to:
| Account Number | Account Name | Account Balance |
| 10-6000 | Administration - Salaries | 200,000 |
| 10-7000 | Administration - FICA Tax | 25,000 |
| 10-8000 | Administration - Benefits | 10,000 |
| 20-6000 | Surgery - Salaries | 150,000 |
| 20-7000 | Surgery - FICA Tax | 15,000 |
| 20-8000 | Surgery - Benefits | 5,000 |
| 30-6000 | Laboratory - Salaries | 100,000 |
| 30-7000 | Laboratory - FICA Tax | 10,000 |
| 30-8000 | Laboratory - Benefits | 0 |
| 40-6000 | Radiology - Salaries | 75,000 |
| 40-7000 | Radiology - FICA Tax | 7,500 |
| 40-8000 | Radiology - Benefits | 2,000 |
<tbody>
</tbody>
I'm trying to find the least time consuming way to get to the end result above. I would be open to solutions using formulas, VBA (very little experience, but will work with this if there is a solution using VBA), or Power Query. Also please let me know if there is a current thread that addresses this question.
OS: Windows 7
Excel : Excel 2010 with Power Query Addin installed
Thank you.
Brennan<body id="cke_pastebin" style="position: absolute; top: 566px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
| Surgery - Salaries |
<tbody>
</tbody>
