Using table row & column heading for data extract summary

[eduardo1966]

Here is a problem that is driving me nuts. I have two tables. The first is generated from a trial balance with department-level detail. In this report I have department numbers going across and account numbers going down the page. The second table is a list of account and department combinations. I need to use this list to "cherry pick" the values in the trial balance report.

The following is a simplified example of the data I am working with:


Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="FONT-WEIGHT: bold">Department</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="FONT-WEIGHT: bold">Account</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">100</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">200</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">300</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">400</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">500</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1947</TD><TD style="TEXT-ALIGN: right">443.77 </TD><TD style="TEXT-ALIGN: right">969.51 </TD><TD style="TEXT-ALIGN: right">652.91 </TD><TD style="TEXT-ALIGN: right">914.01 </TD><TD style="TEXT-ALIGN: right">448.99 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2240</TD><TD style="TEXT-ALIGN: right">645.41 </TD><TD style="TEXT-ALIGN: right">413.39 </TD><TD style="TEXT-ALIGN: right">686.03 </TD><TD style="TEXT-ALIGN: right">341.49 </TD><TD style="TEXT-ALIGN: right">799.64 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2242</TD><TD style="TEXT-ALIGN: right">708.18 </TD><TD style="TEXT-ALIGN: right">95.08 </TD><TD style="TEXT-ALIGN: right">225.11 </TD><TD style="TEXT-ALIGN: right">272.31 </TD><TD style="TEXT-ALIGN: right">554.33 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2639</TD><TD style="TEXT-ALIGN: right">849.45 </TD><TD style="TEXT-ALIGN: right">52.08 </TD><TD style="TEXT-ALIGN: right">759.91 </TD><TD style="TEXT-ALIGN: right">739.21 </TD><TD style="TEXT-ALIGN: right">812.24 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">6400</TD><TD style="TEXT-ALIGN: right">698.19 </TD><TD style="TEXT-ALIGN: right">86.64 </TD><TD style="TEXT-ALIGN: right">412.01 </TD><TD style="TEXT-ALIGN: right">90.86 </TD><TD style="TEXT-ALIGN: right">505.13 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>Data Needed for</TD><TD style="TEXT-ALIGN: right"></TD><TD style="FONT-WEIGHT: bold">Account</TD><TD style="FONT-WEIGHT: bold">Department</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1947</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">200</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2240</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">100</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2240</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">200</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2240</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">300</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2639</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">500</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">6400</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">300</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>Function would return</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">3,938.59 </TD><TD>sum of cells C3, B4, C4, D4, F6, & D7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet1​

I can get an answer the old-fashioned way with index and match formulas running down the second list, but I've got to believe there is a tighter alternative. Ideally, I would like the formula to be elegant and in just one cell. I thought using "countif" would do the trick, but there is a limitation in that function that I just can't get past. Any ideas out there?

 

[circledchicken]

Mike,

Thanks for that insight. Very interesting video on counting unique items, (including the point on wildcards)!

Thanks again - extremely helpful as always,

 

[Arul.rajesh]

Arul,

Yes, I'm sure this forum is full of information about array formula's. Other resources:

• Mike's array formula series - http://www.youtube.com/watch?v=vqfikSMx7mE
• Chip Pearson's website - http://www.cpearson.com/excel/ArrayFormulas.aspx
• Microsoft website - http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx
• + I'm sure there's loads more all over the place

Thank You..

 

[mgirvin]

In honor of circledchicken's cool formula:

http://www.youtube.com/watch?v=VE4AKPuJiis&feature=channel_video_title

 

[Arul.rajesh]

In honor of circledchicken's cool formula:

http://www.youtube.com/watch?v=VE4AKPuJiis&feature=channel_video_title

Wow Explanation..

 

[Arul.rajesh]

Excel Array Formulas Beg - Advanced <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
http://www.youtube.com/view_play_list?p=007E7E9CA63304D3<o></o>
<o></o>
I will definitely make a video about the cool formula you posted (hopefully within the next week)!!

I did not know you could use F9 to do magic inside a formula.
Now I see array formulas like the way Neo saw the Matrix.
Really Amazing videos. They makes excel enjoyable even to the absolute novice.

Do you teach any programming language which would help me get more out of my Computer?

 

[mgirvin]

"Now I see array formulas like the way Neo saw the Matrix"

Now that is cool!!!!

I am glad that the videos help!

 

[circledchicken]

In honor of circledchicken's cool formula:

http://www.youtube.com/watch?v=VE4AKPuJiis&feature=channel_video_title

Woah just watched it - I'm with Arul - another awesome explanation in your magic collection - sooooo cool !!

 

[mgirvin]

No, no, what is so cool is two things:

1) circledchicken's formula and 2) the coolest name at the Mr Excel Message Board: circledchicken!!!!