auto populate masterlist from 3 other lists

[Harold Good]

Hi, I'm working on a budget spreadsheet. Presently it has room for up to 25 expense categories, but the project reviewers would like for those 25 categories to be further consolidated into 5 subgroupings.

The only way I can think to do this is to create a Master list which will autopopulate as they construct a budget for a new project. They will need to enter their budget expense categories into one of 5 subgroup ranges, each with its own column heading, which will serve as the name of that particular subgroup.

Then the budget will populate from the Master list. I have this part figured out so it's just the above part I need help with.

So each of the 5 subgroup ranges have room for about 20 entries each, some may end up with 15, some as few as 2 or 3, but the maximum they can enter in all 5 subgroups is 25 entries because that is all the budget can handle.

How can I autopopulate the Master list as they enter their Account names into one of the five subgroup ranges that best fits its purpose?

For example, we'll keep it simple and say they only use 3 subgroupings and the Masterlist. Here is what it should look like (I'm not sure how the formatting will look when I send this):

Master Subgp1 Subgp2 Subgp3
Office Exp Office Exp Travel Print booklets
Postage Postage R & B Distrib booklts
Telephone Telephone
Travel
R & B
Print booklets
Distrib booklts

I'd prefer they appear in the same order in the Master list as they appear in their own list.

Should this be done with VBA or formulas?

I've done searching, found some Offset count combinations that seem to hold promise, but I'm not sure how to make it work with combining up to 5 subgroupings into one Masterlist.

I'd appreciate some basic vba or formula that would put me on my way.

Thank you for any help you can advise me with!
Harold

 

[JBeaucaire]

I've done this very thing, but to avoid array formulas and such, this takes a
little explaining.

First, what version of Excel are you using? If 2007, then skip the IFERROR
function I'm about to provide.
======
IFERROR: this is a function in Excel 2007 that I use in my previous versions
by adding into the sheets I need.

Alt-F11 to open the VBEditor
Insert>Module
Paste in this function code:

Function IFERROR(ToEvaluate As Variant, Default As Variant) As Variant
If IsArray(ToEvaluate) Then
IFERROR = IIf(IsError(ToEvaluate(1)), Default, ToEvaluate)
Else
IFERROR = IIf(IsError(ToEvaluate), Default, ToEvaluate)
End If
End Function

Alt-Q to close the VBEditor and then save your sheet.

Now let's build the sheet so you can see how I do it, then you can adapt it
your environment.

Column A: Master
Column B: SubGrp1
Column C: SubGrp2
Column D: SubGrp3
Column E: SubGrp4
Column F: SubGrp5

Next, let's add the INDEXing columns we'll use to make arrays unnecessary.
When done, these next 5 columns could be hidden.

Column I:
I1: Sub1Key
I2: =IF(B2="","",N(I1)+1)
Copy that cell down as far as needed, 25 rows?

Columns J:M
J1: Sub2Key
K1: Sub3Key
L1: Sub4Key
M1: Sub5Key

Formulas:
J2: =IF(C2="","",MAX($I:I)+1)
copy that ACROSS from J2:M2

J3: =IF(C3="","",N(J2)+1)
copy that down and across to the rest of the chart

You now have a set of key columns that "number" based on the text entries in
columns B:F. Pretty cool.

Now, you just need a formula back in column A to scan through the columns.
This is where the IFERROR function we installed helps. This rather LONG
formula in A2 won't work unless the IFERROR function has already been
installed in the sheet...in A2 and then copied down as far as needed:

=IFERROR(INDEX(B:B,MATCH(ROW()-1,I:I,0)),
IFERROR(INDEX(C:C,MATCH(ROW()-1,J:J,0)),
IFERROR(INDEX(D,MATCH(ROW()-1,K:K,0)),
IFERROR(INDEX(E:E,MATCH(ROW()-1,L:L,0)),
IFERROR(INDEX(F:F,MATCH(ROW()-1,M:M,0)),"")))))

That is one long formula... but it works nicely. Now start adding and
subtracting entries in columns B:F and the index keys in I:M will adjust,
causing the column A listing to reassert.