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:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Administration[/TD]
[TD]Surgery[/TD]
[TD]Laboratory[/TD]
[TD]Radiology[/TD]
[/TR]
[TR]
[TD]6000[/TD]
[TD]Salaries[/TD]
[TD]200,000[/TD]
[TD]150,000[/TD]
[TD]100,000[/TD]
[TD]75,000[/TD]
[/TR]
[TR]
[TD]7000[/TD]
[TD]FICA Tax[/TD]
[TD]25,000[/TD]
[TD]15,000[/TD]
[TD]10,000[/TD]
[TD]7,500[/TD]
[/TR]
[TR]
[TD]8000[/TD]
[TD]Benefits[/TD]
[TD]10,000[/TD]
[TD]5,000[/TD]
[TD]0[/TD]
[TD]2,000[/TD]
[/TR]
</tbody>[/TABLE]
The result I would like to get to:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account Number[/TD]
[TD]Account Name[/TD]
[TD]Account Balance[/TD]
[/TR]
[TR]
[TD]10-6000[/TD]
[TD]Administration - Salaries[/TD]
[TD]200,000[/TD]
[/TR]
[TR]
[TD]10-7000[/TD]
[TD]Administration - FICA Tax[/TD]
[TD]25,000[/TD]
[/TR]
[TR]
[TD]10-8000
[/TD]
[TD]Administration - Benefits[/TD]
[TD]10,000[/TD]
[/TR]
[TR]
[TD]20-6000[/TD]
[TD]Surgery - Salaries[/TD]
[TD]150,000[/TD]
[/TR]
[TR]
[TD]20-7000[/TD]
[TD]Surgery - FICA Tax[/TD]
[TD]15,000[/TD]
[/TR]
[TR]
[TD]20-8000[/TD]
[TD]Surgery - Benefits[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]30-6000[/TD]
[TD]Laboratory - Salaries[/TD]
[TD]100,000[/TD]
[/TR]
[TR]
[TD]30-7000[/TD]
[TD]Laboratory - FICA Tax[/TD]
[TD]10,000[/TD]
[/TR]
[TR]
[TD]30-8000[/TD]
[TD]Laboratory - Benefits[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]40-6000[/TD]
[TD]Radiology - Salaries[/TD]
[TD]75,000[/TD]
[/TR]
[TR]
[TD]40-7000[/TD]
[TD]Radiology - FICA Tax[/TD]
[TD]7,500[/TD]
[/TR]
[TR]
[TD]40-8000[/TD]
[TD]Radiology - Benefits[/TD]
[TD]2,000[/TD]
[/TR]
</tbody>[/TABLE]
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;">[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Surgery - Salaries[/TD]
[/TR]
</tbody>[/TABLE]
</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:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Administration[/TD]
[TD]Surgery[/TD]
[TD]Laboratory[/TD]
[TD]Radiology[/TD]
[/TR]
[TR]
[TD]6000[/TD]
[TD]Salaries[/TD]
[TD]200,000[/TD]
[TD]150,000[/TD]
[TD]100,000[/TD]
[TD]75,000[/TD]
[/TR]
[TR]
[TD]7000[/TD]
[TD]FICA Tax[/TD]
[TD]25,000[/TD]
[TD]15,000[/TD]
[TD]10,000[/TD]
[TD]7,500[/TD]
[/TR]
[TR]
[TD]8000[/TD]
[TD]Benefits[/TD]
[TD]10,000[/TD]
[TD]5,000[/TD]
[TD]0[/TD]
[TD]2,000[/TD]
[/TR]
</tbody>[/TABLE]
The result I would like to get to:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account Number[/TD]
[TD]Account Name[/TD]
[TD]Account Balance[/TD]
[/TR]
[TR]
[TD]10-6000[/TD]
[TD]Administration - Salaries[/TD]
[TD]200,000[/TD]
[/TR]
[TR]
[TD]10-7000[/TD]
[TD]Administration - FICA Tax[/TD]
[TD]25,000[/TD]
[/TR]
[TR]
[TD]10-8000
[/TD]
[TD]Administration - Benefits[/TD]
[TD]10,000[/TD]
[/TR]
[TR]
[TD]20-6000[/TD]
[TD]Surgery - Salaries[/TD]
[TD]150,000[/TD]
[/TR]
[TR]
[TD]20-7000[/TD]
[TD]Surgery - FICA Tax[/TD]
[TD]15,000[/TD]
[/TR]
[TR]
[TD]20-8000[/TD]
[TD]Surgery - Benefits[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]30-6000[/TD]
[TD]Laboratory - Salaries[/TD]
[TD]100,000[/TD]
[/TR]
[TR]
[TD]30-7000[/TD]
[TD]Laboratory - FICA Tax[/TD]
[TD]10,000[/TD]
[/TR]
[TR]
[TD]30-8000[/TD]
[TD]Laboratory - Benefits[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]40-6000[/TD]
[TD]Radiology - Salaries[/TD]
[TD]75,000[/TD]
[/TR]
[TR]
[TD]40-7000[/TD]
[TD]Radiology - FICA Tax[/TD]
[TD]7,500[/TD]
[/TR]
[TR]
[TD]40-8000[/TD]
[TD]Radiology - Benefits[/TD]
[TD]2,000[/TD]
[/TR]
</tbody>[/TABLE]
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;">[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Surgery - Salaries[/TD]
[/TR]
</tbody>[/TABLE]
</body>
