Creating a list based off matrix contents

cafeteria_food

New Member
Joined
Jan 17, 2018
Messages
3
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

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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

New Member
Joined
Jan 17, 2018
Messages
3
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,996
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:

CBEBFBGBHBIBJBKBLBM
910170115001150111502LocationGL Amount
10502511501 $ 5,044,018.51
115018 $ - $ 279,379.57 $ - $ - 306311500 $ 5,605,159.28
125019 $ - $ - $ 664,713.26 $ (15,067.46)
135021 $ - $ - $ 536,404.36 $ (1,877.79)
145022 $ - $ - $ 122,729.65 $ -
155023 $ - $ 351,471.73 $ - $ -
165024 $ - $ 68,162.94 $ - $ -
175025 $ - $ - $ 5,044,018.51 $ (81,247.35)
185026 $ - $ - $ 591,939.71 $ (5,504.78)
195027 $ - $ 304,633.95 $ - $ -
205028 $ - $ 86,497.69 $ - $ -
213061 $ - $ - $ 456,821.09 $ -
223062 $ - $ - $ - $ -
233063 $ - $ 5,605,159.28 $ - $ -
243064 $ - $ 413,305.67 $ - $ -

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Matrix

Array Formulas
CellFormula
BK10{=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))),"")}
BL10{=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),"")}
BM10{=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),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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

New Member
Joined
Jan 17, 2018
Messages
3
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,996
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

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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.
 

Forum statistics

Threads
1,082,359
Messages
5,364,916
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top