Not sure where to start - Macro, formula?

**kicktown** · 09-19-2017, 03:48 PM

Hi all - I'm trying to create a solution for a tedious process, but I haven't made enough progress in the Excel is Fun youtube series to understand where to go. Could someone point me in the right direction? I feel that vlookups and maybe macros could be involved, but I don't really know at this point. It may be simpler than I'm thinking.

[forgive me plz, I'm not going to mess with html tables right now]
I have a file with 2 books.
Book 1 contains a list of ~100 people, and includes a column for which group they're in.

1Name - - 2Group - - - - 3Requirement string
aJohn - - Beginner StarterPack
bMark - - Intermediate StarterPack,IntermediateExtras
cDoug - - Advanced IntermediateExtras,AdvancedPack

Book 2 contains the various groups in rows, what items they need in columns, and a boolean X to denote if they need it.

1bRole - - 2bStarter Pack - - 3bIntermediate Extras - - 4bAdvanced Pack - - Requirement StringB
aB Beginner X [ ] [ ] StarterPack
bB Intermediate X X [ ] StarterPack,IntermediateExtras
cB Advanced [ ] X X IntermediateExtras,AdvancedPack

I want book 1's column 3, Requirement string, to create one string of "needed items" from book 2, referencing which group the person is in from book 1, seperated by commas without spaces. So for 3a, it would be "StarterPack", for 3b, it woul dbe "StarterPack,IntermediateExtras", and for 3c, it'd be "IntermediateExtras,AdvancedPack".

I think a simpler alternative is to create the string directly on book 2. Maybe some combination of an IF, AND, and Concatonate formulas might be what i'm looking for, but the more I think about it, the dizzier I get as I comprehend how to write it with over 100 rows and columns to parse.

Any ideas or suggested studies?

**johnnymac** · 09-19-2017, 04:06 PM

I am assuming that the requirement string in book1 is currently empty and you want to fill it from book2. This can be done with a VLOOKUP statement in the requirement string column of book1.

=VLOOKUP($Bx, 'book2'!B2:F5, 5, 0)

Then copy this through the 3rd column of book1.

This will be easier to see if you can attach a short file and I can add the command. The x here refers to the row numbers,,B2:F5 is the lookup table from book2 and 5 is the column to lookup. This column can be generated with IFs from the checked columns.