Creating a list based off matrix contents

[cafeteria_food]

Hello - I have been racking my head trying to figure this one out... Lots of google searches and searching MrExcel have left me still wondering.

I have a matrix in excel where the top header is general ledger accounts and the left hand column is locations. When the two are combined you have a general ledger string that searches our accounting database and returns the end of month account balances. For each GL account there is a dollar threshold which requires us physically review the balance and sign off on it. The matrix is set up with conditional formatting so if the balance in any cell is higher than the threshold it conditionally formats its red. If the account balance is less than the threshold nothing happens to the cell. I need a way to summarize all the accounts in the matrix with conditionally formatted red backgrounds into a list for review. The matrix is big (18,000 cells) so manually going through it looking for accounts and district numbers is not possible.

The link below is a screenshot of the matrix. As an example, the list would return the following info (district, account, and balance):
3063, 11500, $5,605,159.28
6090, 11500, $3,098,027.54
5025, 15501, $5,044,018.51
3067, 11501, $3,129,789.33
etc. etc. etc.

Matrix Screenshot Link:
https://imgur.com/a/vlHG8

 

[Roderick_E]

I'd suggest adding a helper column that instead of simply turning red, it places an X in that column for every row that meets the review criteria. This way you can simply filter the review column by X. Or even better, just filter the red conditional column you have by "filter by color"

 

[cafeteria_food]

Thanks for the reply, Roderick_E, but due to the size of the matrix both helper columns and filtering won’t work. There are 280+ columns and 90 rows, so manually filtering isn’t an option

Creating a list that automatically populates is what I’m searching for.

 

[Eric W]

Welcome to the forum.

Is the threshold the same for all GL accounts? If the threshold is the same for all of them (I used 3000000 in this example), then you could try:

	C	BE	BF	BG	BH	BI	BJ	BK	BL	BM
Location	GL	Amount

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10170[/TD]
[TD="align: right"]11500[/TD]
[TD="align: right"]11501[/TD]
[TD="align: right"]11502[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5025[/TD]
[TD="align: right"]11501[/TD]
[TD="align: right"] $ 5,044,018.51 [/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]5018[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 279,379.57 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3063[/TD]
[TD="align: right"]11500[/TD]
[TD="align: right"] $ 5,605,159.28 [/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]5019[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 664,713.26 [/TD]
[TD="align: right"] $ (15,067.46)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]5021[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 536,404.36 [/TD]
[TD="align: right"] $ (1,877.79)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]5022[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 122,729.65 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]5023[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 351,471.73 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]5024[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 68,162.94 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]5025[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 5,044,018.51 [/TD]
[TD="align: right"] $ (81,247.35)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]5026[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 591,939.71 [/TD]
[TD="align: right"] $ (5,504.78)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]5027[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 304,633.95 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]5028[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 86,497.69 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]3061[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 456,821.09 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]3062[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]3063[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 5,605,159.28 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]3064[/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 413,305.67 [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Matrix​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]BK10[/TH]
[TD="align: left"]{=IFERROR(INDEX($C$11:$C$24,SMALL(IF($BE$11:$BH$24>3000000,ROW($C$11:$C$24)-ROW($C$11)+1),ROWS($BK$10:$BK10))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]BL10[/TH]
[TD="align: left"]{=IFERROR(INDEX($BE$9:$BH$9,MOD(SMALL(IF($BE$11:$BH$24>3000000,ROW($BE$11:$BH$24)*1000+COLUMN($BE$11:$BH$24)),ROWS($BK$10:$BK10)),1000)-COLUMN($BE$11)+1),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]BM10[/TH]
[TD="align: left"]{=IFERROR(INDIRECT(TEXT(SMALL(IF($BE$11:$BH$24>3000000,ROW($BE$11:$BH$24)*1000+COLUMN($BE$11:$BH$24)),ROWS($BK$10:$BK10)),"R000C000"),0),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



This works on a small range, but I don't know how it would work for your sheet. These are fairly intensive array formulas, and one of them uses the volatile INDIRECT function. I think you'd be better off with a macro that can generate the list on-demand.

 

[cafeteria_food]

Eric W, thanks for your help! The thresholds change based on account - the thresholds are grouped in a collapsed column. I think you're right about the size of the matrix and the intensive INDIRECT function. Recalc'ing 18k+ cells would put quite the strain on my computer Looks like it's time to dive into VBA

 

[Eric W]

Let us know if you need any help. This actually seems like a pretty good task for a beginner, pretty well-defined and not too big. But I'm sure that some of the old hands here with VBA could offer some tips.

 

[Roderick_E]

VBA solution is the way to go. For projects like this, I've created a help/config tab that contains the trigger/criteria/thresholds and loop through the data set and compare to the help/config tab. You could then either tag the ones in question or have the vba put them on a separate sheet for easy viewing; no filtering required.