Possibly Vlookup with multiple results???

[jmthompson]

Hate to post a new thread, but I've been searching for a couple of hours and cannot find anything that quite suits my needs.

I am building a set of macros to take an ugly raw txt file and turn it into a pretty, user-friendly report. Using VBA, I want to populate each user's row with their assigned cubes. I have about 30 cubes and over 100 users.

On my report, I have a vertical list of users.
Excel 2007<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(224, 224, 240);" width="25px"><col><col><col><col><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="">UserID</td><td style="">Cube 1</td><td style="">Cube 2</td><td style="">Cube 3</td><td style="">Cube 4</td><td style="">Cube 5</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="">User1</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="color: rgb(22, 17, 32); text-align: center;">3</td><td style="">User2</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="color: rgb(22, 17, 32); text-align: center;">4</td><td style="">User3</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="color: rgb(22, 17, 32); text-align: center;">5</td><td style="">User4</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="color: rgb(22, 17, 32); text-align: center;">6</td><td style="">User5</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="color: rgb(22, 17, 32); text-align: center;">7</td><td style="">User6</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></tbody></table>

Sheet3​

On the Template sheet, I have the raw data dump. A portion of this sheet lists each cube and its users, one row per combination. Cube in A, user in B. How can I do this? I've tried combinations of loops, array formulas, can't figure it out.

Excel 2007<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(224, 224, 240);" width="25px"><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1033</td><td style="">[Groups and Users]</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1034</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1035</td><td style="">Cube 1</td><td style="">User1</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1036</td><td style="">Cube 1</td><td style="">User2</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1037</td><td style="">Cube 1</td><td style="">User3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1038</td><td style="">Cube 1</td><td style="">User4</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1039</td><td style="">Cube 1</td><td style="">User5</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1040</td><td style="">Cube 1</td><td style="">User6</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1041</td><td style="">Cube 1</td><td style="">User7</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1042</td><td style="">Cube 1</td><td style="">User8</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1043</td><td style="">Cube 1</td><td style="">User9</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1044</td><td style="">Cube 1</td><td style="">User10</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1045</td><td style="">Cube 1</td><td style="">User11</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1046</td><td style="">Cube 2</td><td style="">User1</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1047</td><td style="">Cube 2</td><td style="">User2</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1048</td><td style="">Cube 2</td><td style="">User3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1049</td><td style="">Cube 2</td><td style="">User4</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1050</td><td style="">Cube 2</td><td style="">User5</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1051</td><td style="">Cube 2</td><td style="">User6</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1052</td><td style="">Cube 2</td><td style="">User7</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1053</td><td style="">Cube 2</td><td style="">User8</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1054</td><td style="">Cube 2</td><td style="">User9</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1055</td><td style="">Cube 2</td><td style="">User10</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">1056</td><td style="">Cube 2</td><td style="">User11</td></tr></tbody></table>

Sheet4​

 

[jmthompson]

So, came up with my own solution.

Added a column to my template that assigns a cube number to each appearance of a user name.
Then added a column to concatenate the username with the cube number

On my Users sheet, I added a vlookup to match another concatenation of the username and cube number, (strt and lastrow2 are variables I define earlier in the code, they just isolate the specific portion of the data dump that concerns me)

Sheets("Template").Select
Range("C" & strt & ":C" & lastRow2).FormulaR1C1 = "=COUNTIF(R" & strt & "C[-1]:RC[-1],""=""&RC[-1])"
Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A" & strt & ":A" & lastRow2).FormulaR1C1 = "=CONCATENATE(RC[2],RC[3])"

Sheets("Users").Select
Range("E3:AH" & lastRow).FormulaR1C1 = "=VLOOKUP(CONCATENATE(RC1,R1C),Template!C1:C2,2,False)"
Range("E3:AH" & lastRow).Value = Range("E3:AH" & lastRow).Value